按列计算的MySQL MIN()限

Question

我有一个MySQL表，格式如下：

group_clue:
---------------------------------------------------
| id | group_id | clue_id | completed | run_order |
---------------------------------------------------
|  1 |        1 |       2 |         1 |         1 |
|  2 |        1 |       6 |         1 |         2 |
|  3 |        1 |       4 |         1 |         3 |
|  4 |        1 |       3 |         0 |         4 |
|  5 |        1 |       1 |         0 |         5 |
|  6 |        1 |       5 |         0 |         6 |
|  7 |        2 |       9 |         1 |         1 |
|  8 |        2 |       2 |         0 |         2 |
 ...
---------------------------------------------------

在group_clue中，上面的数据被构造成每个group_id在某个run_order上都有每个clue_id (从1到clue_id的数量不等，并且不对特定的组重复)。

第一个问题

我想要创建一个表，显示每个group_id的第一个group_id，其中completed = 0按run_order (别名为current_clue)排序。使用上面的例子，这将提供：

---------------------------
| group_id | current_clue |
---------------------------
|        1 |            3 |
|        2 |            2 |
---------------------------

我的初步尝试是：

SELECT group_id, MIN(clue_id) as current_clue
FROM group_clue
WHERE completed = 0
GROUP BY group_id

但是，这将为每个clue_id返回相同的group_id。

第二个问题

根据第一个问题中的数据，我想组成一个最后的表，在其中我将GROUP_CONCAT()这些结果，以便它包含每个current_clue和每个包含该current_clue的group_id。我也希望它能从那些group_ids最多的线索中订购到最少的线索。产生表的一个例子是：

--------------------
| clue | group_ids |
--------------------
|    3 | 1,5,4,3   |
|    2 | 2,6       |
--------------------

我搞不懂点什么。我的初步尝试是：

SELECT clue_id, GROUP_CONCAT(group_id)
FROM [resulting_table]
GROUP BY clue_id
ORDER BY [something]

任何帮助都很感激:什么样的查询适合这种情况？

Answer 1

您问题的第一部分可以这样解决(它期望每个组的run_order是唯一的)：

    SELECT t1.group_id,
           t1.clue_id AS current_clue
      FROM group_clue t1
INNER JOIN (SELECT group_id,
                   MIN(run_order) as run_order
              FROM group_clue
             WHERE completed = 0
          GROUP BY group_id) t2 USING (group_id, run_order)

这个查询的逻辑非常简单：

1. 内部查询选择group_id的对和具有completed = 0的run_order的对应最小值。
2. 之后，我们将原始表加入到这组对中，以便可以另外选择相应的clue_id。

您可以使用以下方法对每个组的元素数进行排序

ORDER BY COUNT(*) DESC