mysql - 使用嵌套 SELECT 保留 SQL WHERE IN() 子句的顺序

Question

我需要一个按项目数量排序的项目名称列表。项目名称和相应的 ID 存储在 tabletwo 中，而 tableone 通过 ID 引用项目:

      tableone              tabletwo
+--------+-----------+    +----+------+
| itemid | condition |    | id | name |
+--------+-----------+    +----+------+
| 2      | satisfied |    | 1  | foo  |
+--------+-----------+    +----+------+
| 1      | satisfied |    | 2  | bar  |
+--------+-----------+    +----+------+
| 3      | satisfied |    | 3  | hurr |
+--------+-----------+    +----+------+
| 3      | satisfied |    | 4  | durr |
+--------+-----------+    +----+------+
| 3      | satisfied |
+--------+-----------+
| 4      | satisfied |
+--------+-----------+
| 4      | satisfied |
+--------+-----------+
| 3      | nope      |
+--------+-----------+
| 1      | satisfied |
+--------+-----------+

SQL 代码:

SELECT `itemname` FROM `tabletwo` WHERE `id` IN (
    SELECT `itemid` FROM (
        SELECT count(`itemid`), `itemid`
        FROM `tableone`
        WHERE `some_codition`="satisfied"
        GROUP BY `itemid`
        ORDER BY count(`itemid`) DESC
    ) alias
)

嵌套的 SELECT 返回项目 ID 的列表，按后代顺序排列:3, 4, 1, 2。然后将该列表用作 IN() 子句的参数。 整个查询的预期结果是:hurr, durr, foo, bar(按此顺序)。但顺序未保留。我知道可以这样做:ORDER BY FIELD(id, 3, 4, 1, 2) 但我不知道如何在像我的情况。我需要再次SELECT吗？还是临时表？还是在 SQL 之外构建另一个查询更好？

Answer 1

尝试使用 JOIN 代替:

SELECT t2.`itemname` 
FROM `tabletwo` AS t2
JOIN (    
   SELECT count(`itemid`) AS cnt, `itemid`
   FROM `tableone`
   WHERE `some_codition`="satisfied"
   GROUP BY `itemid`
) AS t1 ON t1.`itemid` = t2.`id`
ORDER BY t1.cnt DESC

您可以使用 IN 运算符的子查询创建派生表，并对该表执行 JOIN，这样您就可以使用 COUNT 在主查询的 ORDER BY 子句中。