商品表goods:
id name
1 苹果
2 橘子
3 西瓜
商品属性表sku:
id name price goods_id
1 一级 10 1
2 二级 5 1
3 三级 2 1
4 一级 10 2
5 二级 5 2
6 价格 2 3
订单表order_info:
id total_price
1 15
订单详情表order_detail:
id order_id goods_id sku_id
1 1 1 1
2 1 1 2
现在是根据订单id,查询商品列表详情
假如order的id是1,查询结果是:
order_id goods_id sku_id name price
1 1 1 一级 10
1 1 2 二级 5
id name
1 苹果
2 橘子
3 西瓜
商品属性表sku:
id name price goods_id
1 一级 10 1
2 二级 5 1
3 三级 2 1
4 一级 10 2
5 二级 5 2
6 价格 2 3
订单表order_info:
id total_price
1 15
订单详情表order_detail:
id order_id goods_id sku_id
1 1 1 1
2 1 1 2
现在是根据订单id,查询商品列表详情
假如order的id是1,查询结果是:
order_id goods_id sku_id name price
1 1 1 一级 10
1 1 2 二级 5
create table goods(id int , name varchar(10)); insert into goods values(1, "apple"); insert into goods values(2, "orange"); insert into goods values(3, "xigua"); create table sku(id int , name varchar(10), price int, goods_id int ); insert into sku values (1, "level 1", 10 , 1);\ insert into sku values (2, "level 2", 5 , 1); insert into sku values (3, "level 3", 2 , 1); insert into sku values (4, "level 1", 10 , 2); insert into sku values (5, "level 2", 5 , 2); insert into sku values (6, "price", 2 , 3); create table order_info(id int , total_price int ); insert into order_info values(1, 15); create table order_detail(id int, order_id int , goods_id int , sku_id int); insert into order_detail values(1, 1, 1, 1); insert into order_detail values(1, 1, 1, 2);
解决方案
50
select a.order_id,a.goods_id,a.sku_id,b.name,b.price from order_detail a,sku b where a.sku_id = b.id and a.order_id = 1
50
select order_id ,goods_id, sku_id ,name ,price
from order_detail d inner join sku s on d.sku_id=s.id
where order_id=1
from order_detail d inner join sku s on d.sku_id=s.id
where order_id=1