mysql - 嵌套 SELECT 查找最高值，如果未找到则显示 0

Question

感谢 stackoverflow 社区!这些年来我从你那里学到了很多东西，最近我创建了一个帐户。我希望这个问题的答案显然还没有在某个地方，但如果我读另一篇文章，我会斗鸡眼。这是我的问题:

我最近使用嵌套 SELECT 从表中获取每个学生的最高分。我通过另一篇文章教我的一个小技巧做到了这一点。我找不到我从中学到的确切帖子，但这里有一个片段，本质上是相同的技巧。我想，如果你精通 sql，这对你来说并不新鲜:

SELECT id, authorId, answer, votes
FROM (  SELECT id, authorId, answer, votes
        FROM answers
        ORDER BY votes DESC) AS h
GROUP BY authorId

ORDER BY ____ DESC 使最后一个值(最高的值)覆盖所有先前的值，因此您最终只能得到它......如果我理解正确的话。所以，这太棒了，我根据我的需要定制了它。唯一的问题是，现在，我想再给它添加一项功能，我正在绞尽脑汁。我希望有一个慷慨的人能够纠正我。我想从我的“花名册”表中获取完整的学生列表，如果给定学生没有分数，在我的“持有者”表中，我希望它显示“0”。这是我所拥有的，但我不知道如何调整它来做到这一点:

 SELECT *
 FROM (
      SELECT
      holder.id,
      #IFNULL(holder.score, 0) AS score,
      holder.score AS score,
      holder.total,
      holder.student_id AS stu_id,
      holder.date AS date,
      users.firstname AS first,
      users.lastname AS last,
      users.stu_number AS stuno,
      assignments.name AS test,
      rosters.p_id,
      preps.period AS period,
      preps.user

      FROM holder

      JOIN rosters
      ON rosters.stu_id = holder.student_id

      JOIN users
      ON users.id = holder.student_id

      JOIN assignments
      ON assignments.id = holder.t_id

      JOIN preps
      ON preps.id = rosters.p_id

      WHERE holder.visible = 0
      AND preps.user = 1
      AND assignments.user = 1
      AND holder.t_id = 1
      AND preps.period = 2
      ORDER BY score DESC
      ) x
 GROUP BY stuno
 ORDER BY last

您可以看到我注释掉的那一行是我试图让它在 NULL 时显示“0”的无力尝试之一，但它不起作用。我得到了完整的列表，但如果未找到某个学生的分数，则该学生不会出现在我的列表中。有人有解决方案/想法供我尝试吗？

我是否过度使用 JOIN 并使我的生活变得比需要的更加困难？我主要是自学成才，所以我知道我在基础知识上有一些漏洞。但这并没有阻止我创造一些疯狂的酷项目……但时不时地我确信我给自己带来了一些不必要的悲伤。

////////////////////////////////////////////////////////////////////////////////////////

这是我对下面的答案所做的操作，以便它从我的表格中获取信息:

SELECT au.stu_id, 
    COALESCE(t.id, 0) as id,
    COALESCE(t.score , 0) as score               
 FROM rosters au
 LEFT JOIN  (
    SELECT *
         FROM (
           SELECT a.*, 
              @rownum := if(@prev_value = student_id, 
              @rownum + 1, 
           1) rn,
         @prev_value := student_id as prev    
         FROM holder a, 
        (SELECT @rownum := 0, @prev_value := '') r
      ORDER BY student_id, score DESC
    ) T
  WHERE T.rn = 1) T  
ON au.stu_id = T.student_id

所以，这非常有效，只是它不会显示没有给定测试分数的学生。如果在“持有者”表中找不到他们的分数，我希望它显示为“0”。

//////////////////

等一下!我可能说错了...我认为它工作正常。我需要调整一些东西并回复您。顺便说一句，非常感谢您花时间帮助我!

Answer 1

你的第一种方法之所以有效，是因为 MySQL 上的设计很糟糕。

正确的方法应该是

<强> SQL Fiddle Demo

SELECT a.id, a.authorId, a.answer, a.votes
FROM (  SELECT authorId, 
               MAX(votes) as votes
        FROM answers
        GROUP BY authorId ) AS h
JOIN answers a
  ON a.authorId = h.authorId
 AND a.votes = h.votes;

输出

| id | authorId | answer | votes |
|----|----------|--------|-------|
|  2 |        a |     x2 |    21 |
|  4 |        b |     x1 |    23 |  ==> 
|  5 |        b |     x2 |    23 |  ==> duplicates max value are possible

但是如果几个答案具有相同的分数，这就会出现问题。您需要包含一些逻辑来决定显示哪一个。

您还可以使用变量来获得最高分。

SELECT *
FROM (
      SELECT a.*, 
            @rownum := if(@prev_value = authorId, 
                          @rownum + 1, 
                          1) rn,
            @prev_value := authorId as prev    
      FROM answers a, 
            (SELECT @rownum := 0, @prev_value := '') r
      ORDER BY authorId, votes DESC
     ) T
WHERE T.rn = 1;

输出

| id | authorId | answer | votes | rn | prev |
|----|----------|--------|-------|----|------|
|  4 |        b |     x1 |    23 |  1 |    b | => only one is show but would      
|  2 |        a |     x2 |    21 |  1 |    a |    be random unless you specify some rule.

现在，对于您的问题，您还需要使用 LEFT JOIN 而不是 JOIN 来获取没有分数的学生。

类似这样的事情

SELECT au.authorId, 
       COALESCE(t.id, 0) as id,
       COALESCE(t.answer , 0) as answer ,
       COALESCE(t.votes , 0) as votes               
FROM authors au
LEFT JOIN  (
            SELECT *
            FROM (
                  SELECT a.*, 
                        @rownum := if(@prev_value = authorId, 
                                      @rownum + 1, 
                                      1) rn,
                        @prev_value := authorId as prev    
                  FROM answers a, 
                        (SELECT @rownum := 0, @prev_value := '') r
                  ORDER BY authorId, votes DESC
                 ) T
            WHERE T.rn = 1) T  
      ON au.authorId = T.authorId

输出

| authorId | id | answer | votes |
|----------|----|--------|-------|
|        a |  2 |     x2 |    21 |
|        b |  4 |     x1 |    23 |
|        c |  0 |      0 |     0 |