单表sum和双表join后sum区别，复杂查询，子查询中再join结合

3张表

mysql原文

-- 男款，女款，男女通用商品的总销售额

-- 两张表的sum，结合了销售数量
select items.gender,sum(items.price) as "売上額" from items join sales_records on sales_records.item_id = items.id group by gender;
-- 一张表的sum，只是单表的总额，没有结合销售量
select items.gender,sum(items.price) as "売上額" from items group by gender;

-- 销售额在前五的商品的商品ID，商品名，销售额，由多到少排序

-- group by 里面不加入price
select items.id,items.name,sum(items.price) as "売上額" from items join sales_records on sales_records.item_id = items.id group by items.id,items.name order by sum(items.price) desc limit 5;
-- group by 里面不加入price，sum_price代替sum(items.price)用于order by
select items.id,items.name,sum(items.price) as sum_price from items join sales_records on sales_records.item_id = items.id group by items.id,items.name order by sum_price desc limit 5;
-- group by 里面加入price,结果一样的
select items.id,items.name,sum(items.price) as sum_price from items join sales_records on sales_records.item_id = items.id group by items.id,items.name,price order by sum_price desc limit 5;

-- 求比商品グレーパーカー销售额高的商品的商品ID，商品名，销售额

-- 注意group by后用的是having不是where！子查询里再来个join
select items.id,items.name,sum(items.price) as "売上額" from items join sales_records on sales_records.item_id = items.id group by items.id,items.name having sum(items.price)>(select sum(items.price) from items join sales_records on sales_records.item_id = items.id where items.name = "グレーパーカー");

-- 版本太低，无法执行
with A as (select sum(items.price) from items join sales_records on sales_records.item_id = items.id where items.name = "グレーパーカー")
select items.id,items.name,sum(items.price) as "売上額" from items join sales_records on sales_records.item_id = items.id group by items.id,items.name having sum(items.price)>A;

男款，女款，男女通用商品的总销售额
2张表结合sum()查询结果

一张表sum()查询结果

销售额在前五的商品的商品ID，商品名，销售额，由多到少排序
group by 里面不加入price(结果一样)

group by 里面加入price，sum_price代替sum(items.price)用于order by(结果一样)

求比商品グレーパーカー销售额高的商品的商品ID，商品名，销售额

注意group by后用的是having不是where！子查询里再来个join

mysql版本低于5.8无法使用with as