SQL查询需求：提取10个唯一客户，合并订单号或获取全量订单行

嘿，作为SQL新手碰到这类分组合并或者精准筛选的需求太正常了，我来给你详细拆解这两个需求的实现方法，适配主流数据库（MySQL、SQL Server、PostgreSQL等）：

需求1：选取10个客户，合并每个客户的所有订单号到一个单元格 #

不同数据库的字符串聚合函数略有差异，下面是对应版本的代码：

MySQL 版本 #

SELECT 
    t.customer_number,
    GROUP_CONCAT(t.order_number SEPARATOR ', ') AS order_numbers
FROM (
    -- 先筛选出10个唯一客户
    SELECT DISTINCT customer_number
    FROM your_table_name
    LIMIT 10
) AS top_10_customers
JOIN your_table_name t ON top_10_customers.customer_number = t.customer_number
GROUP BY t.customer_number;

SQL Server 版本 #

SELECT 
    t.customer_number,
    STRING_AGG(t.order_number, ', ') AS order_numbers
FROM (
    SELECT DISTINCT TOP 10 customer_number
    FROM your_table_name
) AS top_10_customers
JOIN your_table_name t ON top_10_customers.customer_number = t.customer_number
GROUP BY t.customer_number;

PostgreSQL 版本 #

SELECT 
    t.customer_number,
    STRING_AGG(t.order_number::TEXT, ', ') AS order_numbers
FROM (
    SELECT DISTINCT customer_number
    FROM your_table_name
    LIMIT 10
) AS top_10_customers
JOIN your_table_name t ON top_10_customers.customer_number = t.customer_number
GROUP BY t.customer_number;

需求2：生成包含10个唯一客户的所有订单行 #

这个需求更直接，只需要先锁定10个唯一客户，再取出他们的全部订单即可：

通用版本（MySQL、PostgreSQL适用） #

SELECT *
FROM your_table_name
WHERE customer_number IN (
    SELECT DISTINCT customer_number
    FROM your_table_name
    LIMIT 10
);

SQL Server 版本 #

SELECT *
FROM your_table_name
WHERE customer_number IN (
    SELECT DISTINCT TOP 10 customer_number
    FROM your_table_name
);

注意：记得把代码里的 your_table_name 替换成你实际的数据表名称哦！

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