您可以在匯總摘要中創建新欄位還是需要嵌套查詢？

結果將是一個表格，其中原始表格內容是德克薩斯州每個全州種族的 450,000 行（或記錄）選區選舉結果。

這是我的查詢方式：

select 
UPPER(county) as TXCounty, Name as Presidential_Candidate, 
sum(votes) as Votes
from ['2020_General_Election_Returns$']
where name in ('Candidate 1', 'Candidate 2')
group by county, Office, Name
order by County, Office, name

查詢結果顯示該縣重複了兩次，一次用於候選人 1，另一次用於候選人 2。這意味著，在德克薩斯州的 254 個縣中，它將返回 508 行而不是 254 行，因為它複製了同一個縣以獲取候選人 2 的值。有沒有辦法創建一個查詢，其中有兩個額外的候選人 2 列和他們收到的選票？我認為嵌套查詢可以，但我還不精通該領域。這比為候選人 2 重複縣名要乾淨得多。

如果你總是有兩個候選人，你可以使用條件聚合

select 
 UPPER(county) as TXCounty,
 min(case when name = 'Candidate 1' then Name end) as Presidential_Candidate1,
 sum(case when name = 'Candidate 1' then votes end) as Votes1,
 min(case when name = 'Candidate 2' then Name end) as Presidential_Candidate2,
 sum(case when name = 'Candidate 2' then votes end) as Votes2
from ['2020_General_Election_Returns$']
where name in ('Candidate 1', 'Candidate 2')
group by
 county
order by
 county;

誠然min(case when name = 'Candidate 1' then Name end)也可以，'Candidate 1'但它也允許您使用行編號

select 
 UPPER(county) as TXCounty,
 min(case when rn = 1 then Name end) as Presidential_Candidate1,
 sum(case when rn = 1 then votes end) as Votes1,
 min(case when rn = 2 then Name end) as Presidential_Candidate2,
 sum(case when rn = 2 then votes end) as Votes2
from (
   select *,
     row_number() over (partition by county order by Name) as rn
   from ['2020_General_Election_Returns$']
   where name in ('Candidate 1', 'Candidate 2')
) t
group by
 county
order by
 county;

引用自：https://dba.stackexchange.com/questions/314704