我想建立一個基于一個表的查詢。從這個表中，我想基于計數聚合查詢創建兩個虛擬表。在我的示例中，它會將原始表格拆分為一個表格，其中計算每位玩家的 Xbox 游戲，以及計算每位玩家的 Playstation 游戲。然后根據 PlayerID 連接查詢的結果。
create table data (PlayerID text, game text, platform text);
insert into data (PlayerID, game, platform) values
('Player1', 'Fifa', 'Playstation'),
('Player1', 'Tekken', 'Playstation'),
('Player1', 'Gears of War', 'Xbox'),
('Player1', 'Ninja Gaiden', 'Playstation'),
('Player2', 'Gears of War', 'Xbox'),
('Player1', 'Metal Slug Anthology', 'Playstation'),
('Player1', 'Metal Gear V', 'Playstation'),
('Player2', 'Halo', 'Xbox'),
('Player3', 'Street Fighter', 'Playstation'),
('Player3', 'Madden NFL', 'Playstation'),
('Player1', 'Final Fantasy', 'Xbox'),
('Player2', 'Ratchet & Clank', 'Playstation');
結果應如下所示：
| PlayerID | playedPlaystationGames | playedXBoxGames |
|----------|------------------------|-----------------|
| Player1 | 5 | 2 |
| Player2 | 1 | 2 |
| Player3 | 2 | 0 |
這 3 個步驟應該在 1 個查詢中完成：
Select PlayerID, Count(platform)as playedPlaystationGames, platform
From Data AS TablePlaystation
Group By PlayerID, platform
Having platform='Playstation';
Select PlayerID, Count(platform)as playedXBoxGames, platform
From Data AS TableXBox
Group By PlayerID, platform
Having platform='Xbox';
SELECT data.PlayerID, TableXBox.PlayedXBoxGames, TablePlaystation.playedPlaystationGames
FROM data
RIGHT JOIN (TablePlaystation
RIGHT JOIN TableXBox
ON TablePlaystation.PlayerID = TableXBox.PlayerID)
ON TablePlaystation.PlayerID = data.PlayerID;
uj5u.com熱心網友回復：
利用 Group By
SELECT PlayerID,
SUM(CASE WHEN platform = 'Playstation' THEN 1 ELSE 0 END) AS playedPlaystationGames,
SUM(CASE WHEN platform = 'Xbox' THEN 1 ELSE 0 END) AS playedXBoxGames
FROM data
GROUP BY PlayerID
sqlfiddle 中的演示
或者我不推薦的另一種方法
SELECT T1.PlayerID,playedPlaystationGames,playedXBoxGames
FROM
(Select PlayerID, Count(platform)as playedPlaystationGames, platform
From Data AS TablePlaystation
Group By PlayerID, platform
Having platform='Playstation') t1
LEFT JOIN
(Select PlayerID, Count(platform)as playedXBoxGames, platform
From Data AS TableXBox
Group By PlayerID, platform
Having platform='Xbox') t2 ON T1.PlayerID = T2.PlayerID
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/394459.html
上一篇：自定義加入到任意欄位上的Many2Many關系，包括“OR”？
下一篇：在最近的日期和按磁區加入