CASE或IF IN SQL语句

Question

使用course_article、gr_article和user_score的表。

课程中可以有很多文章:7篇可以有1,2,5,6,7篇文章。 只有少数文章被分级: 1,6和2,5,7没有分级。 用户尝试所有文章:只能从gr_article获得分级文章1,6篇，从course_article获得2,5,7篇。

我想要所有的课程，文章和用户得分在一个表中，如输出所示。

SELECT u.userid ,
       c.cid ,
       a.id ,
       u.score ,
       ( CASE WHEN c.cid = a.cid THEN u.score
              ELSE 0
         END ) AS "score"
FROM   course_article c
       JOIN gr_article a ON c.cid = a.cid
       JOIN user_score u ON u.a_id = a.id
WHERE  u.userid = 8
       AND c.cid = 7;

当我执行查询时，它只显示用户尝试的分级文章，但我需要显示所有的课程文章。

表格

course_article
+--------------+
|   id  cid    |
+--------------+
|     1    7   |
|     2    7   |
|     3    5   |
|     4    5   |
|     5    7   |
|     6    7   |
|     7    7   |
+--------------+

gr_article
+------------------------+
| id  cid  article_name  |
+------------------------+
| 1    7       A1        |
| 2    7       A4        |
| 3    7       A5        |
| 4    7       A3        |
| 5    7       A2        |
| 6    7       A44       |
| 7    7       A6        |
+------------------------+

user_score
+------------------------------+
|   id  a_id  userid   score   |
+------------------------------+
|     1    2      8       3.4  |
|     2    3      3       2.0  |
|     3    4      8       6.7  |
|     4    5      3       4.5  |
|     5    1      8       5.6  |
+------------------------------+

输出

+-----------------------------+
|  userid cid  a_id   score   |
+-----------------------------+
|       8     7    2      3.4 |
|       8     7    4      6.7 |
|       8     7    1      5.6 |
|       8     7    5       0  |
|       8     7    6       0  |
|       8     7    7       0  |
+-----------------------------+

Answer 1

您需要首先确定用户应该评分的课程，然后执行OUTER JOIN，以便为尚未评分的文章显示0.0。

假设没有将user与course相关的表，也许您可以通过假设一个用户与课程相关，如果该用户在该课程中至少获得了一篇文章的1级，就可以推断出这种关系。

然后，您将得到一个类似于以下所示的查询：

SELECT
    uc.userid,
    a.cid,
    a.id AS a_id,
    COALESCE(us.score, 0) AS score -- If no user score, assume 0
FROM (
    SELECT DISTINCT
        us.userid,
        a.cid
    FROM user_score us
        INNER JOIN gr_article a
            ON us.a_id = a.id
    ) uc -- Get user/course relationship
    INNER JOIN gr_article a
        ON uc.cid = a.cid -- Get all possible articles for user courses
    INNER JOIN course_article ca
        ON uc.cid = ca.cid -- Join back to course article as requested (not sure exactly of conditions you may need)
    LEFT OUTER JOIN user_score us -- Get available scores by article for user
        ON us.userid = uc.userid
            AND us.a_id = a.id
 WHERE uc.userid = 8 -- Here is sample filter by user id

更新以显示如何通过用户id进行筛选

如果您要按用户id进行筛选，那么它需要是uc.userid而不是us.userid -否则它会将OUTER JOIN变成INNER JOIN。我更新了查询以显示用户id筛选示例。

下面是带有userid筛选器的查询的SQL：http://sqlfiddle.com/#!18/a1b8d/12/0