mysql - 使用 SQL Union 执行依赖于日益宽松的条件的复杂报表查询

Question

我有以下架构(其中与问题相关的部分):

# schema
- table
* table field

# schema
    - mods
        * Id
        * Title
        * Affiliation
        * Attended
    - modusergrades
        * ModId 
        * UserId
        * QuestionValues
        * Grade
        * Accepted
        * Comments
    - users
        * Id
        * Projects
        * Location
        * Grade 
        * TermId 
    - all
        * Email
        * FirstName
        * LastName
        * UserId
        * ModId
        * TermId

一次mods已注册事件，即可参加事件(如果在特定时间内登录，Attended将被设置为1)。在那里，他们能够对一定数量的 users 进行评分。 ，比如说maxUsers 。如果他们的评分 ( QuestionValues != null && Grade != null ) 低于 maxUsers ，如果他们已准确评分 maxUsers，我们就说他们已开始评分我们说他们已经完成评分。

现在，我希望能够区分以下类别 mods :

• mods已注册但未参加的人(即 Attended = 0)
• mods谁有Attended已参加事件但未开始评分( Attended = 1 但 QuestionValues == null && Grade == null )
• mods已开始评分但未完成的人(因此，他们的上述两个字段的非空值小于 maxUsers )
• mods已完成评分的人 (== maxUsers )。

我希望能够获得一个 SQL 查询，将所有这些信息放在一个位置。

首先，UNION 运算符是否适合此任务？如果没有，我该如何解决这个问题？

其次，如果上一个问题的答案是是，那么这就是我迄今为止获得的mods谁已经成功graded 。这是正确的方法吗？

SELECT  m.Id, m.Title, m.Attended, a.FirstName, a.LastName, a.Email, a.TermId
FROM all a 
INNER JOIN mods m ON m.Id = a.ModId
WHERE m.Id = a.ModId 
    AND Attended = 1 
    AND (SELECT COUNT(*) FROM modusergrade mug
         WHERE m.Id = mug.ModId 
            AND QuestionValues IS NOT NULL 
            AND Grade IS NOT NULL 
            AND Accepted = 1) > 5

UNION

SELECT m.Id, m.Title, 'Graded' AS Attended, a.FirstName, a.LastName, a.Email, a.TermId
FROM all a
INNER JOIN mods m ON m.Id = a.ModId
WHERE m.Id = a.ModId 
    AND Attended = 1 

编辑:为了使问题更加清晰，这是我的目标:

Graded将是 Started Grading 的子集，它是 Attended 的子集，它是 Registered 的子集。使用设置符号:

Graded ⊆ Started Grading ⊆ Attended ⊆ Registered .

是否可以创建一个 SQL UNION 查询来捕获此行为？我还需要其他东西吗？

Answer 1

您不需要为此使用 union 查询。您只需要一个包含您正在寻找的分层逻辑的 case 语句。这需要来自 all 和 mug 表的信息以及来自 modusergrades 的汇总信息。

我在表中没有看到最大问题值，因此我假设它是某种参数。

您想要的查询类似于:

SELECT m.Id, m.Title, m.Attended, a.FirstName, a.LastName, a.Email, a.TermId,
       (CASE WHEN m.Attended = 0 THEN 'Registered'
             WHEN mug.modid IS NULL THEN 'Attended'
             WHEN mug.numgraded < $maxcnt THEN 'Started'
             WHEN mug.numgraded = $maxcnt THEN 'Finished'
        END) as status
FROM all a INNER JOIN
     mods m
     ON m.Id = a.ModId LEFT JOIN
     (SELECT modid, COUNT(*) as numgraded
      FROM modusergrades mug
      WHERE QuestionValues is not null AND Grade is not null
      GROUP BY modid
     ) mug
     ON mug.modid = m.id