一道数据库面试题
id name month salary
----------- ---------- --------------------- -----------
1 a 1 3500.00
2 a 2 3600.0
3 b 1 6600.00
4 b 2 6800.00
要把上面的记录按name分组统计并且从高到低排序,如果只要求输出name和统计后的字段可以使用下面的SQL语句:
select name,sum(salary) as salary from t
group by name order by salary desc
但是,如果还要求输出id或者month:
select id,month,name,sum(salary) as salary from t
group by name,id,month order by salary desc
上面的语句则不能得到预想的结果,正确的SQL语句应该这样:
select min(id) as id,name,min(month) as month,sum(salary) as salary
from t group by name order by salary desc
为了让id和month不在Group by列表里出现,只能加上聚合函数,得到的记录集为:
id name month salary
----------- ---------- --------------------- -----------
3 b 1 13400.00
1 a 1 7100.00