補充說明
'rogeryao'大大的解答才是對的，下午腦袋沒醒還選錯答案，新手又沒辦法回應...
有誰知道怎麼改解答嗎，新手竟然連回覆都不能.....Orz ......
註：一開始的原始資料給的有點問題，重新編輯了一次
我有一份資料如下面的格式 ， 我想要GROUP BY AA BB 而且CC欄位不同才加總DD，請問語法該怎麼下才對
原始資料：
| AA | BB | CC | DD | EE |
|---|---|---|---|---|
| 201902 | X | 1000 | 1 | 1 |
| 201902 | X | 1000 | 2 | 2 |
| 201902 | X | 1001 | 4 | 3 |
| 201902 | Y | 2000 | 6 | 4 |
| 201902 | Y | 2001 | 8 | 5 |
| 201903 | Y | 2002 | 10 | 6 |
| 201904 | Y | 2002 | 12 | 7 |
補充說明：第一筆資料跟第二筆資料CC欄位一樣的時候，我只取EE欄位比較大的第二筆，所以會變成 2+4=6
如果用以下語法出來的結果沒辦法剔除CC重複的
SELECT AA,BB,SUM(DD) FROM table GROUP BY `AA`,`BB`;
SQL GROUP BY 的結果：
| AA | BB | DD |
|---|---|---|
| 201902 | X | 7 |
| 201902 | Y | 14 |
| 201903 | Y | 10 |
| 201904 | Y | 12 |
想要的結果：
| AA | BB | DD |
|---|---|---|
| 201902 | X | 6 |
| 201902 | Y | 14 |
| 201903 | Y | 10 |
| 201904 | Y | 12 |
求解惑QQ
已邀請的邦友 {{ invite_list.length }}/5
目前看起來，你會需要做多重處理才有辦法。
先將ee反向排序後。再做group by aa bb cc
然後再去做sum
出來的sql碼是如下
SELECT aa,bb,sum(dd) FROM
(
SELECT * FROM
(
SELECT * FROM `db` ORDER BY ee DESC
) AS oo GROUP BY aa,bb,cc
) AS kk GROUP BY aa,bb
select X.AA,X.BB,SUM(DD) AS DD
from (
select AA,BB,CC,max(EE) as EE
from Test
where 1=1
group by AA,BB,CC) as X
left join (
select AA,BB,CC,DD,EE from Test) as Y on X.AA=Y.AA and X.BB=Y.BB
and X.CC=Y.CC
and X.EE=Y.EE
where 1=1
group by X.AA,X.BB
order by X.AA,X.BB;
select AA,BB,SUM(DD) AS DD
From (
select AA,BB,CC,DD,ROW_NUMBER() OVER (PARTITION BY AA,BB,CC ORDER BY EE desc) AS KK
from Test) AS SS
Where KK = 1
Group By AA,BB
就2次查詢..
SELECT AA,BB,SUM(DD) FROM (
SELECT AA,BB,CC,DD
from table
GROUP BY `AA`,`BB`,`CC`,`DD`
) as k GROUP BY `AA`,`BB`;
可以使用子查詢取得最大的DD 在使用SUM
SELECT AA,BB,SUM(DD)
FROM (
SELECT AA,BB,MAX(DD) DD
FROM table
GROUP BY AA,BB,CC
) t1
GROUP BY AA,BB
iThome鐵人賽
看影片追技術