MYSQL之GROUP_CONCAT函数忽略空字符串列和内部排序

最新推荐文章于 2025-09-26 10:52:01 发布

转载最新推荐文章于 2025-09-26 10:52:01 发布 · 3.7k 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://blog.youkuaiyun.com/lilongsy/article/details/119457658

文章标签：

#mysql #GROUP_CONCAT

MYSQL 专栏收录该内容

51 篇文章

订阅专栏

本文介绍了MySQL中GROUP_CONCAT函数的高级应用技巧，包括如何忽略空字符串列、支持内部排序以及实现复杂的数据聚合操作。通过具体示例展示了如何处理多列数据并使用不同的分隔符。

1、忽略空字符串列：GROUP_CONCAT(IF(column1 = "", NULL, column1) SEPARATOR ";")

2、支持 ORDER BY 内部排序：GROUP_CONCAT(column1 ORDER BY column1 DESC)

3、原始数据：

SELECT 
  question_no AS 题号,
  GROUP_CONCAT(answer 
    ORDER BY answer ASC) AS 答案,
  GROUP_CONCAT(
    IF(
      other_content = '',
      NULL,
      other_content
    )
  ) AS 其他内容,
  uid AS 用户ID 
FROM
  `jm_answer` 
GROUP BY question_no,
  uid 
ORDER BY uid ASC,
  question_no ASC;

SELECT 
  question_no AS 题号,
  GROUP_CONCAT(answer 
    ORDER BY answer ASC) AS 答案,
  GROUP_CONCAT(
    IF(
      other_content = "",
      NULL,
      other_content
    ) SEPARATOR ";"
  ) AS 其他内容,
  uid AS 用户ID 
FROM
  `jm_answer` 
GROUP BY question_no,
  uid 
ORDER BY uid ASC,
  question_no ASC;