mysql - MySQL 查询中的 GROUP_CONCAT AND ORDER BY FIELD 问题

Question

我在查询时遇到一些问题。我想按多个 id 来排序查询。这是我的查询:

此查询没有返回我想要的结果:(

SELECT *
FROM question q
INNER JOIN answer a ON a.question_id = q.question_id
WHERE q.lesson_id = 1
AND q.question_id IN (
    SELECT e.question_id
    FROM exame e
    WHERE e.inscription_id = 1
    AND e.lesson_id = 1
    AND e.attempt = 1
    ORDER BY e.question_id
) /*this subquery returns (10,2,1,8,3,12,4,11,14,7)*/
ORDER BY FIELD(q.question_id,(
    SELECT GROUP_CONCAT(DISTINCT ee.question_id ORDER BY ee.order_of_appearance SEPARATOR ',') AS final_order
    FROM exame ee
    WHERE ee.inscription_id = 1
    AND ee.lesson_id = 1
    AND ee.attempt = 1)
) /*this subquery returns (10,2,1,8,3,12,4,11,14,7)*/

如您所见，两个子查询返回相同的结果 (10,2,1,8,3,12,4,11,14,7)。如您所知，它们之间的区别在于第一个返回结果集，第二个仅返回一个包含所有 id 连接的字段。

问题#1:如果我复制第一个子查询并将其写入第二个子查询所在的位置，则会收到此错误:

1242 - Subquery returns more than 1 row

所以我创建了第二个子查询(使用 GROUP_CONCAT 函数)，但结果不是我所期望的。结果按“question_id”排序，我希望它按“order_of_appearance”字段排序。

问题#2:如果我在 ORDER BY 子句中编写子查询，我不会得到按“order_of_appearance”字段排序的结果，但如果我删除子查询并手动编写id 数组，结果按“order_of_appearance”排序!为什么？？？

此查询返回我想要的结果! :)

SELECT *
FROM question q
INNER JOIN answer a ON a.question_id = q.question_id
WHERE q.lesson_id = 1
AND q.question_id IN (
    SELECT e.question_id
    FROM exame e
    WHERE e.inscription_id = 1
    AND e.lesson_id = 1
    AND e.attempt = 1
    ORDER BY e.question_id
)
ORDER BY FIELD(q.question_id,10,2,1,8,3,12,4,11,14,7)

最后一个问题:是否可以实现我想要的而无需手动编写 id 数组？我需要动态地做到这一点。

提前致谢! (我希望你能理解我的英语!)

Answer 1

我认为您需要使用FIND_IN_SET()而不是FIELD():

来自引用手册:

FIELD(str,str1,str2,str3,...)

Returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found.

If all arguments to FIELD() are strings, all arguments are compared as strings. If all arguments are numbers, they are compared as numbers. Otherwise, the arguments are compared as double.

If str is NULL, the return value is 0 because NULL fails equality comparison with any value. FIELD() is the complement of ELT().

mysql> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');
    -> 2

mysql> SELECT FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');
    -> 0

<小时/>

FIND_IN_SET(str,strlist)

Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by “,” characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (“,”) character.

mysql> SELECT FIND_IN_SET('b','a,b,c,d');
    -> 2