在SQLite内连接查询中添加DISTINCT COUNT场次统计列的问题

我正在查询一个篮球数据集，想用INNER JOIN获取休斯顿火箭队每位球员的赛季数据，同时新增一列统计每位球员的赛季出场次数。目前还在学习SQL，组合查询时遇到了嵌套查询的问题，特此求助。

我写的错误SQL代码 #

-- Houston Rockets team_id = 1610612745
-- Jalen Green player_id = 1630224
SELECT player.player_name, player_season.*
FROM player_season
(
SELECT 
DISTINCT COUNT (player_id)
FROM player_game_log
AS games_played
)
INNER JOIN
    player
    ON player_season.player_id = player.player_id
WHERE player_season.team_id = 1610612745

问题出在哪 #

1. 语法错误：FROM player_season后面直接跟子查询的写法不合法，缺少JOIN关键字或分隔逗号，数据库无法识别该结构。
2. 子查询未关联主表：当前子查询是全局统计player_game_log里的player_id总数，没有和主表的单个球员关联，无法得到每位球员的个人出场次数。
3. COUNT用法错误：DISTINCT COUNT(player_id)是错误的组合逻辑，统计单个球员出场次数，应该按player_id分组后计数，而非用DISTINCT包裹COUNT。

正确的两种写法 #

写法一：关联子查询（新手友好） #

-- Houston Rockets team_id = 1610612745
SELECT 
    p.player_name,
    ps.*,
    -- 子查询关联当前行的player_id，统计该球员的出场数
    (SELECT COUNT(*) 
     FROM player_game_log pgl
     WHERE pgl.player_id = ps.player_id) AS games_played
FROM player_season ps
INNER JOIN player p ON ps.player_id = p.player_id
WHERE ps.team_id = 1610612745;

写法二：JOIN + GROUP BY（效率更高） #

-- Houston Rockets team_id = 1610612745
SELECT 
    p.player_name,
    ps.*,
    COUNT(pgl.game_id) AS games_played
FROM player_season ps
INNER JOIN player p ON ps.player_id = p.player_id
-- 左连接确保无出场记录的球员也能被保留，出场数显示0
LEFT JOIN player_game_log pgl ON ps.player_id = pgl.player_id
WHERE ps.team_id = 1610612745
-- GROUP BY需包含player_season中所有非聚合列，根据实际表结构补充剩余列
GROUP BY ps.player_season_id, p.player_name, ps.player_id, ps.team_id,
         ps.season_year, ps.points_per_game, ps.rebounds_per_game, ps.assists_per_game;

补充说明 #

• 关联子查询写法直观易懂，每一行数据会单独查询对应球员的出场次数，适合刚接触SQL的学习者。
• JOIN + GROUP BY写法在数据量较大时效率更优，注意不同SQL数据库对GROUP BY的要求有差异，比如MySQL开启ONLY_FULL_GROUP_BY时，必须将所有非聚合列纳入GROUP BY子句。

内容的提问来源于stack exchange，提问作者itscrewston