如何在SQL SELECT语句中获取数字对应的所有前置连续整数

核心逻辑是将你原有查询封装为基础数据集，再基于num_sales的值生成1到该值的连续整数序列，最后拼接为逗号分隔的字符串即可，不同主流数据库的实现写法如下：

MySQL 8.0+ 版本 #

WITH sales_stats AS (
    -- 原有查询逻辑
    SELECT 
        COUNT(CASE WHEN tb2.status = 'C' THEN 1 END) AS num_sales
    FROM 
        table1 AS tb1
    INNER JOIN 
        table2 AS tb2 ON tb1.id = tb2.id_sales
),
-- 递归生成连续序列
recursive_seq AS (
    SELECT num_sales, 1 AS seq FROM sales_stats WHERE num_sales > 0
    UNION ALL
    SELECT num_sales, seq + 1 FROM recursive_seq WHERE seq < num_sales
)
SELECT 
    s.num_sales,
    GROUP_CONCAT(r.seq ORDER BY r.seq SEPARATOR ',') AS predecessors
FROM sales_stats s
LEFT JOIN recursive_seq r ON s.num_sales = r.num_sales
GROUP BY s.num_sales;

PostgreSQL 版本 #

借助内置的generate_series函数可以简化实现：

WITH sales_stats AS (
    SELECT 
        COUNT(CASE WHEN tb2.status = 'C' THEN 1 END) AS num_sales
    FROM 
        table1 AS tb1
    INNER JOIN 
        table2 AS tb2 ON tb1.id = tb2.id_sales
)
SELECT 
    num_sales,
    CASE WHEN num_sales > 0 THEN STRING_AGG(seq::TEXT, ',' ORDER BY seq) ELSE NULL END AS predecessors
FROM sales_stats
LEFT JOIN GENERATE_SERIES(1, (SELECT MAX(num_sales) FROM sales_stats)) seq ON seq <= num_sales
GROUP BY num_sales;

SQL Server 版本 #

WITH sales_stats AS (
    SELECT 
        COUNT(CASE WHEN tb2.status = 'C' THEN 1 END) AS num_sales
    FROM 
        table1 AS tb1
    INNER JOIN 
        table2 AS tb2 ON tb1.id = tb2.id_sales
),
recursive_seq AS (
    SELECT num_sales, 1 AS seq FROM sales_stats WHERE num_sales > 0
    UNION ALL
    SELECT num_sales, seq + 1 FROM recursive_seq WHERE seq < num_sales
)
SELECT 
    s.num_sales,
    STRING_AGG(r.seq, ',') WITHIN GROUP (ORDER BY r.seq) AS predecessors
FROM sales_stats s
LEFT JOIN recursive_seq r ON s.num_sales = r.num_sales
GROUP BY s.num_sales;

如果使用不支持CTE的低版本数据库，可以预先构建一张存储连续整数的数字辅助表，关联时用数字表.id <= num_sales的条件过滤后拼接字符串，逻辑和上述方案一致。

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