一道数据库面试题

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

转载于:https://www.cnblogs.com/chenhong/archive/2008/07/28/1254918.html