select s.sid,sname,count(cid),sum(score) from Student as s left join SC on s.sid=SC.sid group by SC.sid;会出现
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'shop.s.sid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
但是换成下面这种就可以
select sname,r.* from (select sid,count(cid),sum(score) from SC group by SC.sid)as r left join Student as s on r.sid=s.sid;
这是为什么呢?
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'shop.s.sid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
但是换成下面这种就可以
select sname,r.* from (select sid,count(cid),sum(score) from SC group by SC.sid)as r left join Student as s on r.sid=s.sid;
这是为什么呢?
如果写到子查询中,再加一层select就不需要了。
-- 聚合函数除了和Group By一起使用,也可以与Over“开窗函数”一起使用
--例子select s.sid,sname,
count(cid) over(partition by SC.sid order by s.sid),
sum(score) over(partition by SC.sid order by s.sid)
from Student as s left join SC on s.sid=SC.sid
select s.sid
,sname
,count(cid)
,sum(score)
from Student as s
left join SC on s.sid = SC.sid group by SC.sid
//sid和sname都无法去重,导致报错
goselect sname
,r.*
from (select sid,count(cid),sum(score) from SC group by SC.sid)as r
left join Student as s on r.sid=s.sid;
//优点,先在子语中做group合集,再以此子集做为from表,再和原表关联,就可以不需要再次group,因为子集已经去重。