SQL查询行合并需求：需按仪表名称合并相同名称对应行

看起来你需要把相同Meter Name的行合并到一起，这类问题通常用GROUP BY配合聚合函数就能搞定，我给你分两种常见场景来写解决方案：

场景1：合并所有相同仪表名称的行 #

如果是要把某个仪表的所有对应行都合并，核心是用GROUP BY指定分组的依据列（比如Client Number、MID、Meter Name），然后对其他需要合并的列用合适的聚合函数。假设你的完整查询大概是这样：

SELECT 
    CASE WHEN v.MID IS NULL THEN '' ELSE v.ClientNumber END AS 'Client Number',
    CASE WHEN v.MID IS NULL THEN '' ELSE v.MID END AS MID,
    CASE WHEN v.MID IS NULL THEN '' ELSE v.MeterName END AS 'Meter Name',
    v.ReadingValue AS 'Reading',
    v.ReadingDate AS 'Reading Date',
    v.Remarks AS 'Remarks'
FROM YourView v
-- 原有的WHERE/JOIN逻辑

修改后的合并查询（以SQL Server为例，不同数据库的字符串合并函数有差异）：

SELECT 
    CASE WHEN v.MID IS NULL THEN '' ELSE v.ClientNumber END AS 'Client Number',
    CASE WHEN v.MID IS NULL THEN '' ELSE v.MID END AS MID,
    CASE WHEN v.MID IS NULL THEN '' ELSE v.MeterName END AS 'Meter Name',
    -- 数值列：比如合并读数总和
    SUM(v.ReadingValue) AS 'Total Reading',
    -- 日期列：取最早/最晚的日期
    MIN(v.ReadingDate) AS 'First Reading Date',
    MAX(v.ReadingDate) AS 'Last Reading Date',
    -- 字符串列：拼接所有备注（SQL Server用STRING_AGG，MySQL用GROUP_CONCAT，PostgreSQL用STRING_AGG）
    STRING_AGG(v.Remarks, ', ') AS 'Combined Remarks'
FROM YourView v
-- 保留原有的WHERE/JOIN条件
WHERE ...
GROUP BY 
    CASE WHEN v.MID IS NULL THEN '' ELSE v.ClientNumber END,
    CASE WHEN v.MID IS NULL THEN '' ELSE v.MID END,
    CASE WHEN v.MID IS NULL THEN '' ELSE v.MeterName END
ORDER BY 'Meter Name'

关键说明：GROUP BY里必须包含所有没有用聚合函数的列，也就是你希望作为分组标识的列；聚合函数的选择要贴合你的业务需求——比如数值列用SUM/AVG，日期列用MIN/MAX，文本列用字符串拼接函数。

场景2：只合并每个仪表名称的前两行 #

如果你的需求是仅合并每个仪表对应的前两行，那需要先用窗口函数ROW_NUMBER()筛选出前两行，再进行聚合：

WITH RankedMeters AS (
    SELECT 
        CASE WHEN v.MID IS NULL THEN '' ELSE v.ClientNumber END AS 'Client Number',
        CASE WHEN v.MID IS NULL THEN '' ELSE v.MID END AS MID,
        CASE WHEN v.MID IS NULL THEN '' ELSE v.MeterName END AS 'Meter Name',
        v.ReadingValue AS 'Reading',
        v.ReadingDate AS 'Reading Date',
        v.Remarks AS 'Remarks',
        -- 按仪表名称分组，给每行按日期排序排名
        ROW_NUMBER() OVER (PARTITION BY v.MeterName ORDER BY v.ReadingDate) AS rn
    FROM YourView v
    WHERE ...
)
SELECT 
    'Client Number',
    MID,
    'Meter Name',
    SUM(Reading) AS 'Total Reading from Top 2 Rows',
    STRING_AGG(Remarks, ', ') AS 'Combined Remarks from Top 2 Rows'
FROM RankedMeters
WHERE rn <= 2 -- 只保留每个仪表的前两行
GROUP BY 'Client Number', MID, 'Meter Name'
ORDER BY 'Meter Name'

这里的PARTITION BY v.MeterName会把数据按仪表名称分组，ROW_NUMBER()给每组内的行按日期排序编号，最后筛选出编号≤2的行再合并。

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