问题描述
我遇到以下问题:我尝试选择用户进行的所有投票，然后将它们放在一栏中.我为此使用GROUP_CONCAT，但是不知何故它在混合值顺序.
I have the following problem: I try to select all the votes a user made and put them out in one column. I use GROUP_CONCAT for this, but somehow it is mixing the values order.
这是SQL代码:
SELECT
GROUP_CONCAT(DISTINCT options.option_name SEPARATOR ',') AS selected,
user_login.firstname, user_login.lastname,
event.event_title
FROM
options, user_login, event, votes, questions
WHERE
event.id = ? AND questions.Event_id = event.id
AND votes.user_id = user_login.id AND votes.question_id = questions.id
AND FIND_IN_SET(options.id, selected)
组别 user_login.id 订购 user_login.class
GROUP BY user_login.id ORDER BY user_login.class
投票的示例值为:
id | event_id | question_id | selected | user_id
25 14 42 52,46,41 1
26 14 43 68 1
选项类似于:
id | option_name | question_id
40 Project A 42
41 Project B 42
46 Project C 42
52 Project D 42
67 Hello 43
68 Bye 43
问题就像:
id | question_name | event_id
42 Project Number 14
43 Greeting 14
事件就像:
id | title
14 Project Testing
给定代码的输出为:
selected | event_title
Project C, Bye, ProjectD, Project B Test
如何保持原始订单，以便其输出我:项目D，项目C，项目B，再见?
How can I keep the original order, so that it outputs me:Project D, Project C, Project B, Bye?
推荐答案
是否会进行这项工作?基本上，您说的是按字段值排序，并使它们看起来像"52"，"46"等...
would something like this work? basically you say order by the field values and make them look like '52','46',... etc.
SELECT
GROUP_CONCAT(DISTINCT options.option_name
ORDER BY FIELD( options.id,
concat('"',
replace(selected, ',', '","'),
'"')
)
SEPARATOR ','
) AS selected,
user_login.firstname, user_login.lastname,
event.event_title
FROM options, user_login, event, votes, questions
WHERE event.id = ? AND questions.Event_id = event.id
AND votes.user_id = user_login.id AND votes.question_id = questions.id
AND FIND_IN_SET(options.id, selected)
GROUP BY user_login.id
ORDER BY user_login.class
执行此操作的首选方法是使具有字符串的变量..更易于阅读，并且可以确保以这种方式执行正确的顺序.
my preferred way to do this is to make a variable that has the string.. its easier to read and you can ensure it does the correct order this way..
SET @order_field := (
SELECT
group_concat(
CONCAT('"', replace(selected, ',', '","'), '"')
)
FROM votes);
然后查询将更容易阅读...
then the query would be a lot easier to read...
SELECT
GROUP_CONCAT(DISTINCT options.option_name
ORDER BY FIELD( options.id, @order_field)
SEPARATOR ','
) AS selected,
user_login.firstname, user_login.lastname,
event.event_title
FROM options, user_login, event, votes, questions
WHERE event.id = ? AND questions.Event_id = event.id
AND votes.user_id = user_login.id AND votes.question_id = questions.id
AND FIND_IN_SET(options.id, selected)
GROUP BY user_login.id
ORDER BY user_login.class
这篇关于MYSQL-GROUP_CONCAT和FIND_IN_SET是混合值/顺序吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！