php - sql count 连接表中精确或相似记录的数量

Question

我有一个包含表的数据库:用户、用户类(class)、类(class)和类(class)类别

结构类似于下图:

USERS------------------------------------------------------------id      username------------------------------------------------------------1       john2       amy3       sarah4       james5       nickUSER_COURSES------------------------------------------------------------user_id     course_id------------------------------------------------------------1            21            31            42            23            14            35            45            5COURSES------------------------------------------------------------id,     course_category_id  course_name------------------------------------------------------------1       1                   english language2       1                   english literature3       2                   algebra4       3                   physics5       3                   biologyCOURSE_CATEGORIES------------------------------------------------------------id      category_name------------------------------------------------------------1       language2       mathematics3       science4       computing

我正在尝试编写一个采用 user_id 的查询，例如John 的 id 1 并返回结果显示确切的共同类(class)数量(course_id 匹配的类(class))和共同类(class)的数量(category_id 相同的类(class))

因此基于上面的示例数据库，查询应返回以下内容:

------------------------------------------------------------user_id | username | num_exact_courses | num_common_courses------------------------------------------------------------2         amy        1                   03         sarah      0                   14         james      1                   05         nick       1                   1

我将如何做到这一点？非常感谢这里的一些帮助。谢谢

Answer 1

所以基本上在这里你有一个从 user_courses 获取行的主选择..然后你必须左连接其他行以便没有过滤......按 id 分组并按用户 id 过滤..所以在这种情况下，John 的 1 .. 我使用 COALESCE 将 null 值更改为 0 值，这给出了最终结果集:)

SELECT 
    uc.user_id, 
    u.username, 
    COALESCE(t.num_exact, 0) as num_exact_courses, 
    COALESCE(t1.num_common, 0) as num_common_courses 
FROM user_courses uc
JOIN users u ON u.id = uc.user_id
LEFT JOIN 
(   SELECT COUNT(course_id) AS num_exact, uc.user_id
    FROM users u
    LEFT JOIN user_courses uc ON u.id = uc.user_id 
    WHERE uc.course_id IN
    (   SELECT course_id  -- # -- get courses where john is in
        FROM user_courses 
        WHERE user_id = 1
    ) AND uc.user_id <> 1 -- # -- but make sure its not john that has the course
    GROUP BY uc.user_id
) t ON t.user_id = uc.user_id
LEFT JOIN
(   SELECT COUNT(*) AS num_common, uc.user_id 
    FROM courses c
    JOIN user_courses uc ON uc.course_id = c.id
    WHERE course_category_id IN
    (   SELECT c.course_category_id  -- # -- get course categories that john has
        FROM courses c
        JOIN user_courses uc ON uc.course_id = c.id
        WHERE uc.user_id = 1
    )
    AND course_id NOT IN -- # -- and make sure that the other users dont have the same course as john but are in the category
    (   SELECT c.id 
        FROM courses c
        JOIN user_courses uc ON uc.course_id = c.id
        WHERE uc.user_id = 1
    )
    GROUP BY c.id
) t1 ON t1.user_id = uc.user_id
WHERE uc.user_id <> 1
GROUP BY uc.user_id;

DEMO