多条件多表单查询求和：统计用户查看指定类型球员次数

需求说明 #

获取每位用户的Firstname、LastName，以及该用户查看过的、在Users表中UserType为'teamplayer'的球员数量。规则如下：

• 用户ID对应ViewedPlayers表的CreatedByCharacterID字段
• 统计ViewedPlayers表中Viewed_PlayerID列符合条件的记录数（即该Viewed_PlayerID对应的用户在Users表中UserType是teamplayer）

表结构与示例数据 #

Users表（字段：ID、UserType、Firstname、LastName） #

ViewedPlayers表（字段：Viewed_PlayerID、CreatedByCharacterID） #

预期结果 #

尝试的查询语句 #

Query #1 #

SELECT      
    c.Firstname, c.LastName,
    SUM(CASE WHEN p.CreatedByCharacterID THEN 1 ELSE 0 END) AS Count
FROM 
    Users c
JOIN    
    ViewedPlayers p ON p.CreatedByCharacterID = c.ID
WHERE   
    c.UserType = 'Teamplayer'
GROUP BY 
    p.CreatedByCharacterID
ORDER BY 
    SUM(CASE WHEN p.CreatedByCharacterID THEN 1 ELSE 0 END) DESC

Query #2 #

SELECT      
    c.Firstname, c.LastName,
    SUM(CASE WHEN c.UserType = 'Teamplayer' AND p.CreatedByCharacterID THEN 1 ELSE 0 END) AS Count
FROM    
    Users c
JOIN
    ViewedPlayers p ON p.CreatedByCharacterID = c.ID
GROUP BY 
    p.CreatedByCharacterID
ORDER BY 
    SUM(CASE WHEN p.CreatedByCharacterID THEN 1 ELSE 0 END) DESC

正确SQL语句及解释 #

你的两个查询都没抓住核心需求：需要关联Users表两次——一次是获取查看球员的用户信息，另一次是判断被查看的球员是否为Teamplayer类型。

正确的SQL语句如下：

SELECT
    u.Firstname,
    u.LastName,
    COUNT(vp.Viewed_PlayerID) AS Count
FROM
    Users u
LEFT JOIN
    ViewedPlayers vp ON u.ID = vp.CreatedByCharacterID
LEFT JOIN
    Users p ON vp.Viewed_PlayerID = p.ID AND p.UserType = 'teamplayer'
GROUP BY
    u.ID, u.Firstname, u.LastName
ORDER BY
    Count DESC;

关键说明： #

1. 两次关联Users表：
   • Users u：代表查看球员的用户，用于获取其姓名信息
   • Users p：代表被查看的球员，通过vp.Viewed_PlayerID = p.ID关联，并筛选p.UserType = 'teamplayer'来判断是否符合统计条件
2. LEFT JOIN确保全量用户统计：即使某个用户没有查看过任何Teamplayer类型的球员，也会出现在结果中，Count值为0
3. 规范分组逻辑：按用户的ID、Firstname、LastName分组，避免因重名导致的分组错误
4. 精准统计有效记录：只有当被查看的球员是Teamplayer时，关联的p.ID才不为空，COUNT会自动统计这些符合条件的记录数

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