我有一個任務，我需要在查詢中使用聚合函式。我一直遇到一個問題，即同一個 ID 有多個條目，我寧愿將它們組合成一個運行（為同一個 ID 加在一起）。
-- Aggregate #3 - Show the total amount of pledges for each Team
-- for particular year. Order the results by amount of pledges highest
-- to lowest. This query should result in 1 row per Team.
-- It should include the Team ID, Team name (or a combination of Sport,
-- Level, and Gender), the total pledge amount and the Event date/year.
SELECT DISTINCT SUM(TEGS.monPledgeAmount) AS TotalPledge
,TE.intEventID
,TTC.intTeamandClubID
,TE.dtmEventDate
,TGS.strGenderDesc
,TLT.strLevelDesc
FROM TEventGolfers AS TEG JOIN TEvents TE
ON TEG.intEventID = TE.intEventID
JOIN TGolfers AS TG
ON TG.intGenderID = TEG.intGolferID
JOIN TEventGolferSponsors AS TEGS
ON TEGS.intEventGolferID = TEG.intEventGolferID
JOIN TEventGolferTeamandClubs AS TEGTC
ON TEGTC.intEventGolferID = TEG.intEventGolferID
JOIN TTeamandClubs AS TTC
ON TTC.intTeamandClubID = TEGTC.intTeamandClubID
JOIN TLevelofTeams AS TLT
ON TLT.intLevelofTeamID = TTC.intLevelofTeamID
JOIN TGenders AS TGS
ON TGS.intGenderID = TTC.intGenderID
GROUP BY
TEGS.monPledgeAmount
,TE.intEventID
,TTC.intTeamandClubID
,TE.dtmEventDate
,TGS.strGenderDesc
,TLT.strLevelDesc
下面的輸出（要在第 3 列“intTeamandClubID”中組合的 ID）：
0.80 2 3 2016-01-01 Female Varsity Football
0.80 2 4 2016-01-01 Male Varsity Golf
4.00 2 3 2016-01-01 Female Varsity Football
4.00 2 4 2016-01-01 Male Varsity Golf
10.00 2 3 2016-01-01 Female Varsity Football
50.00 2 3 2016-01-01 Female Varsity Football
我希望“intTeamandClubID”為同一 ID 的 1 行，并將“TotalPledge”加在一起。
uj5u.com熱心網友回復：
您可以洗掉SELECT子句中的 DISTINCT 和GROUP BY子句中的TEGS.monPledgeAmount列。
SELECT SUM(TEGS.monPledgeAmount) AS TotalPledge
,TE.intEventID
,TTC.intTeamandClubID
,TE.dtmEventDate
,TGS.strGenderDesc
,TLT.strLevelDesc
FROM TEventGolfers AS TEG JOIN TEvents TE
ON TEG.intEventID = TE.intEventID
JOIN TGolfers AS TG
ON TG.intGenderID = TEG.intGolferID
JOIN TEventGolferSponsors AS TEGS
ON TEGS.intEventGolferID = TEG.intEventGolferID
JOIN TEventGolferTeamandClubs AS TEGTC
ON TEGTC.intEventGolferID = TEG.intEventGolferID
JOIN TTeamandClubs AS TTC
ON TTC.intTeamandClubID = TEGTC.intTeamandClubID
JOIN TLevelofTeams AS TLT
ON TLT.intLevelofTeamID = TTC.intLevelofTeamID
JOIN TGenders AS TGS
ON TGS.intGenderID = TTC.intGenderID
GROUP BY
TE.intEventID
,TTC.intTeamandClubID
,TE.dtmEventDate
,TGS.strGenderDesc
,TLT.strLevelDesc
uj5u.com熱心網友回復：
您想TotalPledge對每個 的值求和intTeamandClubID。為此，您需要為每個intTeamandClubID. 在您的情況下，由于您要選擇多個列，因此您將為這些列的每個唯一組合創建一個組，這是由GROUP BY陳述句完成的。
您的查詢中已經有了它，但您也按 分組TotalPledge，這是您不想要的。您想對該值求和，因此您應該將其從GROUP BY：
GROUP BY
TE.intEventID
,TTC.intTeamandClubID
,TE.dtmEventDate
,TGS.strGenderDesc
,TLT.strLevelDesc
請注意，即使它在 GROUP BY 旁邊有一些用例DISTINCT，這里也沒有必要，因為每個組（列值的組合）都是唯一的。但是，將其留在查詢中不會影響結果
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/457830.html
上一篇：如何將多列提取為單列