mysql什么牌子_mysql 电商数据查询案例(畅销品牌/城市省份...) in

ER图

1-- 求出最畅销的十个商品所属品牌中所有商品的销售额 ***

(先找出最畅销的十个商品的 typeid , 然后再用 in 操作符去匹配对应品牌的goodsid )

select g.goodsid, sum(goodsprice*amount) 销售额

from orderdetail o right join goodsinfo g on o.GoodsID = g.goodsid

where typeid in (select distinct typeid from(

select sum(amount) 销量, o.goodsid, typeid

from orderdetail o left join goodsinfo g on o.goodsid = g.goodsid

group by o.goodsid

order by 销量 desc

limit 10) a)

group by goodsid;

##注意: 第二行用 goodsinfo 表去进行左连接, 而没有用orderdetail表,原因是goodsinfo表的信息更全.

2 -- 求出购买力最强的前十个城市以及他们所在的省份 **

方法1 ( 首先查出购买力最强的前十个城市 , 将结果存为虚拟表c , 再将regioninfo 表与c表进行连接)

select 购买金额 , 城市,regionname from

(select sum(orderamount) as 购买金额 , regionname as 城市 , province from

orderinfo as o left join regioninfo as r on o.city = r.regionid

group by city

order by sum(goodsamount) desc

limit 10) as c

left join regioninfo as re on c.province = re.regionid;

方法2 (将orderinfo 表 与regioninfo 表进行两次连接, 但两次连接时,给regioninfo 表的命名要不一样)

select sum(orderamount) as 购买金额 , r1.regionname as 城市 , r2.regionname as 省份 from

orderinfo as o left join regioninfo as r1 on o.city = r1.regionid

left join regioninfo as r2 on o.province = r2.regionid

group by city

order by sum(goodsamount) desc

limit 10;