SQL技术问询：如何基于现有列生成步长为5的区间列并聚合字段

核心思路 #

通过计算分组ID实现动态区间划分，再结合字符串聚合函数合并同区间的Column B内容，无需硬编码区间范围。

只显示有数据的区间 #

以下是不同数据库的实现代码：

MySQL

SELECT
  CONCAT(
    CASE WHEN group_id = 0 THEN 0 ELSE group_id * 5 + 1 END,
    ' - ',
    (group_id + 1) * 5
  ) AS `Column A`,
  GROUP_CONCAT(Column_B SEPARATOR ', ') AS `Column B`
FROM (
  SELECT
    Column_B,
    FLOOR((Column_A - 1) / 5) AS group_id
  FROM your_table -- 替换为你的表名
) t
GROUP BY group_id
ORDER BY group_id;

PostgreSQL

SELECT
  CONCAT(
    CASE WHEN group_id = 0 THEN 0 ELSE group_id * 5 + 1 END,
    ' - ',
    (group_id + 1) * 5
  ) AS "Column A",
  STRING_AGG(Column_B, ', ') AS "Column B"
FROM (
  SELECT
    Column_B,
    FLOOR((Column_A - 1) / 5)::INT AS group_id
  FROM your_table -- 替换为你的表名
) t
GROUP BY group_id
ORDER BY group_id;

SQL Server（2017+）

SELECT
  CONCAT(
    CASE WHEN group_id = 0 THEN 0 ELSE group_id * 5 + 1 END,
    ' - ',
    (group_id + 1) * 5
  ) AS [Column A],
  STRING_AGG(Column_B, ', ') AS [Column B]
FROM (
  SELECT
    Column_B,
    FLOOR((Column_A - 1) / 5) AS group_id
  FROM your_table -- 替换为你的表名
) t
GROUP BY group_id
ORDER BY group_id;

SQL Server（2016及以下）

WITH grouped_data AS (
  SELECT
    Column_B,
    FLOOR((Column_A - 1) / 5) AS group_id,
    CASE WHEN FLOOR((Column_A - 1) / 5) = 0 THEN '0 - 5'
         ELSE CONCAT(FLOOR((Column_A - 1) / 5) * 5 + 1, ' - ', (FLOOR((Column_A - 1) / 5) + 1) * 5)
    END AS interval_label
  FROM your_table -- 替换为你的表名
)
SELECT
  interval_label AS [Column A],
  STUFF(
    (SELECT ', ' + Column_B FROM grouped_data g2 WHERE g2.group_id = g1.group_id FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
    1, 2, ''
  ) AS [Column B]
FROM grouped_data g1
GROUP BY group_id, interval_label
ORDER BY group_id;

显示所有可能的区间（含空区间） #

如果需要展示所有从0到最大Column_A的区间，即使区间内没有数据，可通过递归CTE生成区间序列后左连接：

MySQL（8.0+）

WITH intervals AS (
  SELECT
    0 AS group_id,
    '0 - 5' AS interval_label
  UNION ALL
  SELECT
    group_id + 1,
    CONCAT((group_id + 1) * 5 + 1, ' - ', (group_id + 2) * 5)
  FROM intervals
  WHERE (group_id + 2) * 5 <= (SELECT MAX(Column_A) FROM your_table)
)
SELECT
  i.interval_label AS `Column A`,
  COALESCE(GROUP_CONCAT(t.Column_B SEPARATOR ', '), '') AS `Column B`
FROM intervals i
LEFT JOIN (
  SELECT
    Column_B,
    FLOOR((Column_A - 1) / 5) AS group_id
  FROM your_table
) t ON i.group_id = t.group_id
GROUP BY i.group_id, i.interval_label
ORDER BY i.group_id;

说明 #

• 分组逻辑：通过FLOOR((Column_A - 1)/5)生成分组ID，自动将0-5归为一组，6-10归为下一组，以此类推，实现动态区间划分。
• 字符串合并：不同数据库对应不同的聚合函数，MySQL用GROUP_CONCAT，PostgreSQL和SQL Server 2017+用STRING_AGG。

内容的提问来源于stack exchange，提问作者ketan maurya