多对多关系中SQL求和及prod_id合并实现技术咨询

原始数据示例 #

id  name  prod_id  price
------------------------
9    A     23      4100
94   B     40      1500
94   B     36      1500

期望结果 #

id  name  prod_id  price
------------------------
9    A     23      4100
94   B     40,36   1500

你的尝试代码（未完成） #

SELECT 
    company.id,
    company.name,
    order_product.product_id,
    SUM(orders.price)
FROM
    orders
INNER JOIN users ON orders.user_id = users.id
INNER JOIN order_product ON orders.id = order_product.order_id
INNER JOIN customer ON orders.customer_id = customer.id
INNER JOIN company ON customer.company_id = company.id
GROUP BY
company.id,

解决方案 #

要实现按company.id和company.name分组，需完成两个核心聚合操作：合并同组的product_id为逗号分隔字符串、对orders.price求和。以下是针对主流数据库的具体写法：

MySQL/MariaDB #

使用GROUP_CONCAT()实现字符串合并，同时补全GROUP BY的必填字段：

SELECT 
    company.id,
    company.name,
    GROUP_CONCAT(order_product.product_id SEPARATOR ',') AS prod_id,
    SUM(orders.price) AS price
FROM
    orders
INNER JOIN users ON orders.user_id = users.id
INNER JOIN order_product ON orders.id = order_product.order_id
INNER JOIN customer ON orders.customer_id = customer.id
INNER JOIN company ON customer.company_id = company.id
GROUP BY
    company.id, company.name;

PostgreSQL #

使用STRING_AGG()函数，注意需将数值类型的product_id转换为字符串：

SELECT 
    company.id,
    company.name,
    STRING_AGG(order_product.product_id::TEXT, ',') AS prod_id,
    SUM(orders.price) AS price
FROM
    orders
INNER JOIN users ON orders.user_id = users.id
INNER JOIN order_product ON orders.id = order_product.order_id
INNER JOIN customer ON orders.customer_id = customer.id
INNER JOIN company ON customer.company_id = company.id
GROUP BY
    company.id, company.name;

SQL Server（2017及以上版本） #

直接使用STRING_AGG()函数：

SELECT 
    company.id,
    company.name,
    STRING_AGG(order_product.product_id, ',') AS prod_id,
    SUM(orders.price) AS price
FROM
    orders
INNER JOIN users ON orders.user_id = users.id
INNER JOIN order_product ON orders.id = order_product.order_id
INNER JOIN customer ON orders.customer_id = customer.id
INNER JOIN company ON customer.company_id = company.id
GROUP BY
    company.id, company.name;

SQL Server（旧版本） #

用STUFF结合FOR XML PATH实现字符串合并：

SELECT 
    c.id,
    c.name,
    STUFF((
        SELECT ',' + CAST(op.product_id AS VARCHAR)
        FROM order_product op
        JOIN orders o ON op.order_id = o.id
        JOIN customer cust ON o.customer_id = cust.id
        WHERE cust.company_id = c.id
        FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS prod_id,
    SUM(o.price) AS price
FROM company c
JOIN customer cust ON c.id = cust.company_id
JOIN orders o ON cust.id = o.customer_id
GROUP BY c.id, c.name;

关键注意事项 #

• GROUP BY必须包含所有非聚合字段，即company.id和company.name
• 字符串聚合函数需根据使用的数据库选择对应实现
• 若product_id为数值类型，部分数据库需要先转换为字符串再聚合

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