MySQL - 使用由子查询 group_concat 或 join 问题创建的 Order By 结果

Question

这是我困惑了相当长一段时间的一个问题，我一直没能让它正常工作，经过大约 40 个小时的思考，我终于明白了这一点。

设置

对于示例问题，我们有 2 个表，一个是...

field_site_id             field_sitename           field_admins
1                         Some Site                1,
2                         Other Site               1,2,

，另一个是管理员之类...

field_user_id             field_firstname          field_lastname
1                         Joe                      Bloggs
2                         Barry                    Wills

现在所有此查询的设计目的如下：

• 列出数据库中的所有站点
• 使用用于拉取每个管理员的 JOIN 和 FIND_IN_SET
  • GROUP_CONCAT(field_firstname, ' ', field_lastname) 与 GROUP BY 一起构建包含真实用户名的字段。
• 还允许 HAVING 过滤自定义结果以进一步缩小结果范围。

所有这部分都工作得很好。

我不知道如何实现的是按 GROUP_CONCAT 结果对结果进行排序，我想这是 ORDER BY 在 concat 函数之前起作用，因此数据不存在可按其排序，那么替代方案是什么是？

代码示例：

SELECT *

GROUP_CONCAT(DISTINCT field_firstname, ' ', field_lastname ORDER BY field_lastname SEPARATOR ', ') AS field_admins_fullname,

FROM `table_sites`
LEFT JOIN `table_admins` ON FIND_IN_SET( `table_admins`.`field_user_id`, `table_sites`.`field_site_id` ) > 0
GROUP BY field_site_id

我还尝试了一个使用子查询来收集 group_concat 结果的查询，如下所示...

( SELECT GROUP_CONCAT(field_firstname, ' ', field_lastname ORDER BY field_lastname ASC SEPARATOR ', ') FROM table_admins 
WHERE FIND_IN_SET( `table_admins`.`field_user_id`, `table_sites`.`field_admins` ) > 0
) AS field_admins_fullname

结论

无论哪种方式尝试 ORDER BY field_admins_fullname 都不会创建正确的结果，它不会出错，但假设这是因为给定的 ORDER BY 是空白的，所以它只是做它想做的事情。

欢迎提出任何建议，如果这是不可能的，另一种推荐的指数方法是什么？

Answer 1

我认为有两件事是错误的：

第一，是JOIN。它应该使用 s.field_admins 而不是 field_site_id ：

    ON FIND_IN_SET( a.field_user_id, s.field_admins ) > 0

第二，您应该使用 CONCAT() 函数（连接同一行中的字段） ）在GROUP_CONCAT()内。

试试这个：

SELECT s.field_site_id
     , s.field_sitename
     , GROUP_CONCAT( CONCAT(a.field_firstname, ' ', a.field_lastname)
                     ORDER BY a.field_lastname ASC
                     SEPARATOR ', '
                   )
       AS field_admins_fullname
FROM table_sites s
  LEFT JOIN table_admins a
    ON FIND_IN_SET( a.field_user_id, s.field_admins ) > 0
GROUP BY s.field_site_id

---

友情建议：

Don't use         Do use
------------      --------
table_sites       site
table_admins      admin

field_site_id     site_id 
field_sitename    sitename
field_admins      admins

---

但真正应该强调的是您的设置。使用逗号分隔值的字段会导致这种可怕的查询，这些查询使用 FIND_IN_SET() 进行连接，并使用 GROUP_CONCAT() 显示结果。看起来很糟糕，很难维护，最重要的是，非常非常慢，因为没有索引可以使用。

你应该有这样的东西：

设置建议

Table:  site

site_id      sitename       
1            Some Site      
2            Other Site     


Table:  site_admin

site_id      admin_id      
1            1
2            1 
2            2   


Table:  admin

user_id      firstname      lastname
1            Joe            Bloggs
2            Barry          Wills

Answer 2

我认为您需要重复在 ORDER BY 中选择的复杂 CONCAT 语句。

所以你的订单更像是......

ORDER BY (GROUP_CONCAT(DISTINCT field_firstname, ' ',
    field_lastname ORDER BY field_lastname SEPARATOR ', ')) ASC

我没有尝试过这个，但我有一个类似的问题，这似乎可以解决，但如果没有 DISTINCT 等，它会简单得多。

Answer 3

分组依据错误，试试这个？

GROUP BY field_site_id