SQL中如何生成含作物唯一值、计数及对应种植户列表的新表

你可以通过分组字符串聚合函数实现需求，不同数据库的对应实现语法如下：

MySQL 实现 #

CREATE TABLE crop_summary AS
SELECT 
    crop,
    COUNT(*) AS total_count,
    GROUP_CONCAT(DISTINCT grower ORDER BY grower SEPARATOR ', ') AS growers
FROM `table`
GROUP BY crop
ORDER BY total_count DESC;

说明：GROUP_CONCAT是MySQL专属的字符串拼接聚合函数，加DISTINCT可以避免同一种作物下同一个种植户重复出现，SEPARATOR可以自定义拼接的分隔符。

PostgreSQL 实现 #

CREATE TABLE crop_summary AS
SELECT 
    crop,
    COUNT(*) AS total_count,
    STRING_AGG(DISTINCT grower, ', ' ORDER BY grower) AS growers
FROM "table"
GROUP BY crop
ORDER BY total_count DESC;

SQL Server 实现 #

SELECT 
    crop,
    COUNT(*) AS total_count,
    STRING_AGG(DISTINCT grower, ', ') WITHIN GROUP (ORDER BY grower) AS growers
INTO crop_summary
FROM "table"
GROUP BY crop
ORDER BY total_count DESC;

Oracle 实现 #

CREATE TABLE crop_summary AS
SELECT 
    crop,
    COUNT(*) AS total_count,
    LISTAGG(DISTINCT grower, ', ') WITHIN GROUP (ORDER BY grower) AS growers
FROM "table"
GROUP BY crop
ORDER BY total_count DESC;

低版本数据库兼容写法 #

如果你的数据库版本不支持在聚合函数中使用DISTINCT，可以先通过子查询对作物+种植户去重，再做聚合计算：

CREATE TABLE crop_summary AS
SELECT 
    crop,
    SUM(row_cnt) AS total_count,
    GROUP_CONCAT(grower ORDER BY grower SEPARATOR ', ') AS growers
FROM (
    SELECT crop, grower, COUNT(*) AS row_cnt
    FROM `table`
    GROUP BY crop, grower
) t
GROUP BY crop
ORDER BY total_count DESC;

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