團隊
| ID | 隊名 |
|---|---|
| 1 | 兵工廠 |
比賽
| ID | 日期 | HOME_TEAM_ID | AWAY_TEAM_ID | HOME_SCORE | AWAY_SCORE |
|---|---|---|---|---|---|
| 1 | 2018-08-10 | 10 | 7 | 2 | 1 |
國家
| ID | NATION_NAME |
|---|---|
| 1 | 西班牙 |
播放器
| ID | 姓名 | NATION_ID | 團隊 ID |
|---|---|---|---|
| 100 | 大衛·德赫亞 | 1 | 10 |
目標
| 目標 ID | 匹配 ID | PLAYER_ID | GOAL_ORDER | 自己的目標 |
|---|---|---|---|---|
| 1 | 1 | 106 | 1 | 錯誤的 |
我試圖從上面的資料庫（SQL Server）中計算出在主場進球最多的那支球隊的英格蘭球員人數。
我的查詢：
SELECT TOP 1 COUNT(PLAYER.PLAYER_ID), MAX(HOME_SCORE)
FROM PLAYER
JOIN NATION ON PLAYER.NATION_ID = NATION.NATION_ID
JOIN MATCH ON MATCH.HOME_TEAM_ID = PLAYER.TEAM_ID
WHERE NATION.NATION_NAME = 'England'
GROUP BY PLAYER.PLAYER_ID
ORDER BY MAX(MATCH.HOME_SCORE) DESC
它給出了 的正確答案max(home_score)，但該隊中的英國球員人數不正確。
uj5u.com熱心網友回復：
您需要先獲得得分最高的球隊，然后左加入NATION到PLAYER，然后計算非空的數量NATION_ID。
您也按錯誤的值分組
SELECT TOP (1)
p.TEAM_ID,
NumPlayers = COUNT(*),
NumEnglishPlayers = COUNT(n.NATION_ID),
HighestScore = m.HOME_SCORE
FROM (
SELECT TOP (1) *
FROM MATCH m
ORDER BY
m.HOME_SCORE DESC
) m
JOIN PLAYER p ON m.HOME_TEAM_ID = p.TEAM_ID
LEFT JOIN NATION n ON p.NATION_ID = n.NATION_ID
AND n.NATION_NAME = 'England'
GROUP BY
m.HOME_TEAM_ID,
m.HOME_SCORE;
資料庫<>小提琴
uj5u.com熱心網友回復：
只是一個未經測驗的記事本涂鴉。
因為沒有樣本資料我無法驗證它。
SELECT TOP 1
match.match_id
, match.home_score
, COUNT(CASE
WHEN player_nation.nation_name = 'England'
THEN player.player_id
END) AS EnglishPlayers
, COUNT(goal.goal_id) AS TotalGoals
FROM GOAL AS goal
JOIN PLAYER AS player
ON player.player_id = goal.player_id
JOIN MATCH AS match
ON match.match_id = goal.match_id
AND match.home_team_id = player.team_id
LEFT JOIN NATION AS player_nation
ON player_nation.nation_id = player.nation_id
GROUP BY match.match_id
ORDER BY TotalGoals DESC
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/374189.html
標籤：sql sql-server 查询语句