MYSQL-GROUP_CONCAT和FIND_IN_SET是混合值/顺序吗? [英] MYSQL - GROUP_CONCAT AND FIND_IN_SET are mixing values/order?

问题描述

我遇到以下问题:我尝试选择用户进行的所有投票，然后将它们放在一栏中.我为此使用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是混合值/顺序吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！