MySQL练习题

sql文件：

链接：https://pan.baidu.com/s/1q3mT9KUZt162XtoIMXspXg 
提取码：0lym

sql：

-- 1.请查询2020年全年总销售额前5的门店id，门店名称，总销售额。以降序形式排列（20分）查询结果字段表示为：id，门店名称，总金额。
SELECT a.门店id,a.`门店名称`,SUM(a.`销售额`) 销售额 FROM 
(SELECT shop.id 门店id,shop.`name` 门店名称,SUM(h.totalprice) 销售额 
FROM goods 
LEFT JOIN booth ON goods.booth_id = booth.id 
LEFT JOIN historytrade h ON h.goods_id = goods.id 
LEFT JOIN shop ON shop.id = booth.shop_id
WHERE YEAR(DATE_FORMAT(h.tradetime,"%Y%m%d"))=2020 GROUP BY h.goods_id) a
GROUP BY a.`门店id`
ORDER BY 销售额 DESC
LIMIT 5;
-- 2.查询店名为“清河店”的门店，2019年销售商品的总重量
SELECT SUM(total.countGood) 总量 FROM
(SELECT goods.id,COUNT(goods_id) countGood FROM shop LEFT JOIN booth ON
shop.id = booth.shop_id LEFT JOIN goods ON
goods.booth_id = booth.id LEFT JOIN historytrade h ON
h.goods_id = goods.id 
WHERE YEAR(DATE_FORMAT(h.tradetime,"%Y%m%d"))=2019
AND
shop.`name` LIKE "清河店%"
GROUP BY goods.id) total;
-- 3.查询店名为“南苑店”在2020年的农残检查的合格率，（1为合格）合格率的定义为检测合格的次数除以检测的总次数
SELECT 
SUM((CASE WHEN r.checking = 1 THEN 1 ELSE 0 END))/COUNT(r.id)*100 合格率
FROM shop LEFT JOIN booth ON
shop.id = booth.shop_id LEFT JOIN remain r ON
r.booth_id = booth.id
WHERE YEAR(DATE_FORMAT(r.check_time,"%Y%m%d"))=2020
AND
shop.name LIKE "南苑店%";
-- 4.查询“南苑店”中销售额比“洋洋水果”高的商户名称和销售额，并以销售额升序进行排列。查询结果字段展示为：商户名称，销售额
SELECT * FROM (SELECT booth.booth_name 商户名称,SUM(h.totalprice) 销售额 FROM shop LEFT JOIN booth ON
shop.id = booth.shop_id LEFT JOIN goods ON
goods.booth_id = booth.id LEFT JOIN historytrade h ON
h.goods_id = goods.id
WHERE 
shop.`name` LIKE "南苑店%"
GROUP BY 商户名称) a WHERE
a.`销售额`>
(SELECT SUM(h.totalprice) FROM shop LEFT JOIN booth ON
shop.id = booth.shop_id LEFT JOIN goods ON
goods.booth_id = booth.id LEFT JOIN historytrade h ON
h.goods_id = goods.id
WHERE 
shop.`name` LIKE "南苑店%" AND booth.booth_name = "洋洋水果")
ORDER BY a.`销售额`
;
-- 5.查询所有名字中含有“白菜”两字的商品在2019年销售额的总合，以门店名称进行归类。查询结果字段表示为：门店名称，商品名称，销售额
SELECT shop.`name` 门店名称,goods.goods_name 商品名称,SUM(h.totalprice) 销售额 FROM goods LEFT JOIN booth ON
goods.booth_id = booth.id LEFT JOIN historytrade h ON
h.goods_id = goods.id LEFT JOIN shop ON
shop.id = booth.shop_id
WHERE YEAR(DATE_FORMAT(h.tradetime,"%Y%m%d"))=2019
AND
goods.goods_name LIKE "%白%菜%" 
GROUP BY h.goods_id;
-- 6.查询“南苑店”中所有商品的名称及当前价格。（10分）查询字段结果表示为：商品名，价格。
SELECT goods.goods_name 商品名称,goods.unitprice 价格 FROM shop LEFT JOIN booth ON
shop.id = booth.shop_id LEFT JOIN goods ON
goods.booth_id = booth.id
WHERE 
shop.`name` LIKE "南苑店%";
-- 7.查询“南苑店”中，在售课别为“肉类”商品的对应商户名称及其商品id和商品名（10分）查询结果字段表示为：商户名称，商品id，商品名称。
SELECT booth.id 商户id,goods.id 商品id,goods.goods_name 商品名称 FROM shop LEFT JOIN booth ON
shop.id = booth.shop_id LEFT JOIN goods ON
goods.booth_id = booth.id LEFT JOIN category ON
goods.id = category.goods_id LEFT JOIN categoryplus ON 
category.categoryplus_id = categoryplus.id
WHERE
categoryplus.`name` = "肉类" 
AND
shop.name LIKE "南苑店%";

优化后的sql：

-- 1.请查询2020年全年总销售额前5的门店id，门店名称，总销售额。以降序形式排列（20分）查询结果字段表示为：id，门店名称，总金额。
SELECT a.门店id,a.`门店名称`,SUM(a.`销售额`) 销售额 FROM 
(SELECT shop.id 门店id,shop.`name` 门店名称,SUM(h.totalprice) 销售额 
FROM shop 
JOIN booth on shop.id = booth.shop_id
JOIN goods ON goods.booth_id = booth.id 
JOIN historytrade h ON
h.goods_id = goods.id
WHERE h.tradetime BETWEEN '2020-01-01 00:00:00' and '2020-12-31 23:59:59' GROUP BY h.goods_id) a
GROUP BY a.`门店id`
ORDER BY 销售额 DESC
LIMIT 5;

优化内容：left join驱动表排在前面或者改成join、
改写where后面的语句，不能首跟表达式、
创建索引CREATE INDEX index_tradetime ON historytrade (tradetime)


-- 2.查询店名为“清河店”的门店，2020年销售商品的总重量
SELECT goods.id,COUNT(goods_id) countGood FROM shop 
JOIN booth ON shop.id = booth.shop_id  
JOIN goods ON goods.booth_id = booth.id  
JOIN historytrade h ON h.goods_id = goods.id 
WHERE h.tradetime BETWEEN '2020-01-01 00:00:00' and '2020-12-31 23:59:59'
AND
shop.`name` LIKE "清河店%"
GROUP BY goods.id;

优化内容：left join驱动表排在前面或者改成join、
改写where后面的语句，不能首跟表达式


-- 3.查询店名为“南苑店”在2020年的农残检查的合格率，（1为合格）合格率的定义为检测合格的次数除以检测的总次数
SELECT 
SUM((CASE WHEN r.checking = 0 THEN 1 ELSE 0 END))/COUNT(r.id)*100 合格率
FROM shop LEFT JOIN booth ON
shop.id = booth.shop_id LEFT JOIN remain r ON
r.booth_id = booth.id
WHERE r.check_time BETWEEN '2020-01-01 00:00:00' and '2020-12-31 23:59:59'
AND
shop.name LIKE "南苑店%";

优化内容：left join驱动表排在前面或者改成join、
改写where后面的语句，不能首跟表达式

-- 4.查询“南苑店”中销售额比“洋洋水果”高的商户名称和销售额，并以销售额升序进行排列。查询结果字段展示为：商户名称，销售额
SELECT a.商户名称,a.销售额 FROM (SELECT booth.booth_name 商户名称,SUM(h.totalprice) 销售额 FROM shop  JOIN booth ON
shop.id = booth.shop_id  JOIN goods ON
goods.booth_id = booth.id  JOIN historytrade h ON
h.goods_id = goods.id
WHERE 
shop.`name` LIKE "南苑店%"
GROUP BY 商户名称) a WHERE
a.`销售额`>
(SELECT SUM(h.totalprice) FROM shop  JOIN booth ON
shop.id = booth.shop_id  JOIN goods ON
goods.booth_id = booth.id  JOIN historytrade h ON
h.goods_id = goods.id
WHERE 
shop.`name` LIKE "南苑店%" AND booth.booth_name = "洋洋水果")
ORDER BY a.`销售额`;

优化内容：left join驱动表排在前面或者改成join、
创建索引CREATE INDEX index_goods_id ON historytrade (goods_id);
CREATE INDEX index_shop_id ON booth (shop_id);