SQL查询：合并同一容器与操作下的去重resourcename字段值

你当前的SQL查询因多表关联产生了大量重复行，需要将同一容器（containername）和操作（operationname）对应的不同工作站（resourcename）去重后合并为逗号分隔的字符串，而非返回重复条目。以下是针对不同数据库的解决方案：

SQL Server（2017及以上版本） #

使用STRING_AGG函数实现去重拼接，配合GROUP BY分组：

SELECT TOP 10000
    c.containername,
    o.operationname,
    hml.txndate,
    STRING_AGG(DISTINCT r.resourcename, ', ') WITHIN GROUP (ORDER BY r.resourcename) AS resourcename
FROM HistoryMainline hml
INNER JOIN ExecuteTaskHistory th ON th.HistoryMainlineId = hml.HistoryMainlineId
INNER JOIN Container c ON c.ContainerId = hml.ContainerId
INNER JOIN Operation o ON o.OperationId = hml.OperationId
INNER JOIN ResourceDef r ON r.ResourceId = th.WorkstationId
GROUP BY c.containername, o.operationname, hml.txndate
ORDER BY hml.TxnDate DESC

MySQL #

使用GROUP_CONCAT函数完成去重拼接：

SELECT
    c.containername,
    o.operationname,
    hml.txndate,
    GROUP_CONCAT(DISTINCT r.resourcename ORDER BY r.resourcename SEPARATOR ', ') AS resourcename
FROM HistoryMainline hml
INNER JOIN ExecuteTaskHistory th ON th.HistoryMainlineId = hml.HistoryMainlineId
INNER JOIN Container c ON c.ContainerId = hml.ContainerId
INNER JOIN Operation o ON o.OperationId = hml.OperationId
INNER JOIN ResourceDef r ON r.ResourceId = th.WorkstationId
GROUP BY c.containername, o.operationname, hml.txndate
ORDER BY hml.TxnDate DESC
LIMIT 10000;

PostgreSQL #

同样使用STRING_AGG函数实现需求：

SELECT
    c.containername,
    o.operationname,
    hml.txndate,
    STRING_AGG(DISTINCT r.resourcename, ', ' ORDER BY r.resourcename) AS resourcename
FROM HistoryMainline hml
INNER JOIN ExecuteTaskHistory th ON th.HistoryMainlineId = hml.HistoryMainlineId
INNER JOIN Container c ON c.ContainerId = hml.ContainerId
INNER JOIN Operation o ON o.OperationId = hml.OperationId
INNER JOIN ResourceDef r ON r.ResourceId = th.WorkstationId
GROUP BY c.containername, o.operationname, hml.txndate
ORDER BY hml.TxnDate DESC
LIMIT 10000;

说明 #

SELECT TOP 1无法满足需求的原因是它仅返回单条数据，而你需要的是按容器、操作分组后，将每组内的工作站名称合并，分组聚合才是正确的处理方式。

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