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;
這是為什么呢？
uj5u.com熱心網友回復：
用聚合函式時，如果欄位不是統計列，則必須放到group by后邊uj5u.com熱心網友回復：
聚合函式必須要group，如果寫到子查詢中，再加一層select就不需要了。
uj5u.com熱心網友回復：
-- 聚合函式除了和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
uj5u.com熱心網友回復：
那如果是你用第一種的方法該怎么寫呢（不用子查詢）？謝謝uj5u.com熱心網友回復：
沒太懂前半句的意思，要是您用第一種方法寫該怎么寫呢？謝謝uj5u.com熱心網友回復：
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都無法去重，導致報錯
go
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;
//優點，先在子語中做group合集，再以此子集做為from表，再和原表關聯，就可以不需要再次group，因為子集已經去重。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/101995.html
標籤：基礎類