MySQL LEFT JOIN合并重叠有效期仪表名：类似PIVOT的子查询

存在与仪表关联的连续数据点，仪表名称随时间变更且有效期存在重叠。

表结构及示例数据 #

meter表 #

data表 #

当前查询问题 #

执行以下SQL时，仪表有效期重叠的时间点会生成重复行：

SELECT
    d.Timestamp,
    d.DataID,
    m.MeterName,
    d.Value
FROM
    data d
LEFT JOIN
    meter m
ON
    m.DataID = d.DataID
    AND d.Timestamp >= m.ValidFrom
    AND d.Timestamp <= m.ValidTo
WHERE
    d.DataID=1

由于data表近1.9亿行，meter表2200行，现有查询基于索引性能优异，需修改查询将同一Timestamp下的MeterName以逗号分隔合并，确保同一时间点仅返回一行数据。

根据使用的SQL方言，选择对应的字符串聚合函数，以下是主流数据库的实现方案：

SQL Server/PostgreSQL #

使用STRING_AGG函数聚合仪表名称，保留原查询的关联逻辑并通过分组消除重复行：

SELECT
    d.Timestamp,
    d.DataID,
    STRING_AGG(m.MeterName, ', ') AS MeterNames,
    d.Value
FROM
    data d
LEFT JOIN
    meter m
ON
    m.DataID = d.DataID
    AND d.Timestamp >= m.ValidFrom
    AND d.Timestamp <= m.ValidTo
WHERE
    d.DataID = 1
GROUP BY
    d.Timestamp, d.DataID, d.Value

MySQL #

使用GROUP_CONCAT函数：

SELECT
    d.Timestamp,
    d.DataID,
    GROUP_CONCAT(m.MeterName SEPARATOR ', ') AS MeterNames,
    d.Value
FROM
    data d
LEFT JOIN
    meter m
ON
    m.DataID = d.DataID
    AND d.Timestamp >= m.ValidFrom
    AND d.Timestamp <= m.ValidTo
WHERE
    d.DataID = 1
GROUP BY
    d.Timestamp, d.DataID, d.Value

Oracle #

使用LISTAGG函数：

SELECT
    d.Timestamp,
    d.DataID,
    LISTAGG(m.MeterName, ', ') WITHIN GROUP (ORDER BY m.MeterName) AS MeterNames,
    d.Value
FROM
    data d
LEFT JOIN
    meter m
ON
    m.DataID = d.DataID
    AND d.Timestamp >= m.ValidFrom
    AND d.Timestamp <= m.ValidTo
WHERE
    d.DataID = 1
GROUP BY
    d.Timestamp, d.DataID, d.Value

• 原查询的索引策略依然生效：确保data表存在(DataID, Timestamp)的复合索引，meter表存在(DataID, ValidFrom, ValidTo)的复合索引，可快速匹配关联条件。
• 聚合操作开销可控：meter表仅2200行，每个data行匹配的仪表数量有限，分组聚合不会带来显著性能损耗。

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