题解sql必知必会36 | #从 Products 表中检索所有的产品名称以及对应的销售总数#
从 Products 表中检索所有的产品名称以及对应的销售总数
http://www.nowcoder.com/practice/2b289b78de1546f38fd24e17e56f1bec
首先是正确答案
select prod_name , quant_sold from Products as b inner join ( select prod_id, sum(quantity) as quant_sold from OrderItems group by prod_id ) as a on b.prod_id = a.prod_id
好像大家都习惯性先连接子查询表然后join其他表，比如
select prod_name , quant_sold from ( select prod_id, sum(quantity) as quant_sold from OrderItems group by prod_id ) as a inner join Products as b on a.prod_id = b.prod_id
然后是自己的错误答案,这里错在在子查询里面，使用了别名a.prod_id,画蛇添足了，要想大概是因为这个子查询是最先运行的，然后才赋予这个表别名，在运行子查询的时候是从OrderItems里选择而不是a表,然后这里的Products别名b，也不可省略，否则会出现ambiguous错误
select prod_name , quant_sold from Products as b inner join ( select a.prod_id, sum(quantity) as quant_sold from OrderItems group by a.prod_id ) as a on b.prod_id = a.prod_id其他答案，根据常识可以得出，Products表是产品名称表，所以是一一对应的，可以直接连接完，再group by聚合,像这种直接不别名可能其实更优雅，可读性跟强，虽然麻烦点，但是体现逻辑性
select Products.prod_name, sum(OrderItems.quantity) as quant_sold from Products left join OrderItems on OrderItems.prod_id = Products.prod_id group by Products.prod_name补充探索，虽然这里t1是零食表，但是字段不会提示模糊不清哦，好好体会
select prod_name,t1.quant_sold （这里t1省略是ok的） from (select prod_id,sum(quantity) as quant_sold from OrderItems group by prod_id) t1 right join Products t2 on t1.prod_id=t2.prod_id;
