SQL Server中按设备聚合不同班次AKG/BKG/CKG最新非零数据

针对你的需求，核心是为每个设备分别提取AKG、BKG、CKG的最新非零值，再合并到同一行。以下提供几种可行的SQL方案：

方案1：多子查询关联（通用所有SQL数据库） #

通过三个独立子查询分别获取每个字段的最新非零记录，再通过全外连接合并结果，确保即使设备没有某类KG数据也能正常显示：

SELECT
    COALESCE(a.machineName, b.machineName, c.machineName) AS machineName,
    a.AKG,
    b.BKG,
    c.CKG
FROM
    -- 提取每个设备最新的非零AKG值
    (SELECT machineName, AKG
     FROM (
         SELECT 
             machineName, 
             AKG,
             ROW_NUMBER() OVER (PARTITION BY machineName ORDER BY time DESC) AS rn
         FROM vortexTable
         WHERE AKG <> 0
     ) t
     WHERE rn = 1) a
FULL OUTER JOIN
    -- 提取每个设备最新的非零BKG值
    (SELECT machineName, BKG
     FROM (
         SELECT 
             machineName, 
             BKG,
             ROW_NUMBER() OVER (PARTITION BY machineName ORDER BY time DESC) AS rn
         FROM vortexTable
         WHERE BKG <> 0
     ) t
     WHERE rn = 1) b ON a.machineName = b.machineName
FULL OUTER JOIN
    -- 提取每个设备最新的非零CKG值
    (SELECT machineName, CKG
     FROM (
         SELECT 
             machineName, 
             CKG,
             ROW_NUMBER() OVER (PARTITION BY machineName ORDER BY time DESC) AS rn
         FROM vortexTable
         WHERE CKG <> 0
     ) t
     WHERE rn = 1) c ON COALESCE(a.machineName, b.machineName) = c.machineName
ORDER BY LEN(machineName), machineName;

方案2：条件聚合+窗口函数（更简洁） #

在CTE中为每个设备的每个KG字段单独计算最新非零记录的排名，再通过聚合函数将各字段值合并到同一行：

WITH ranked_records AS (
    SELECT
        machineName,
        AKG,
        BKG,
        CKG,
        -- 为每个KG字段单独标记最新非零记录的排名
        ROW_NUMBER() OVER (PARTITION BY machineName ORDER BY CASE WHEN AKG <> 0 THEN time ELSE NULL END DESC) AS rn_akg,
        ROW_NUMBER() OVER (PARTITION BY machineName ORDER BY CASE WHEN BKG <> 0 THEN time ELSE NULL END DESC) AS rn_bkg,
        ROW_NUMBER() OVER (PARTITION BY machineName ORDER BY CASE WHEN CKG <> 0 THEN time ELSE NULL END DESC) AS rn_ckg
    FROM vortexTable
)
SELECT
    machineName,
    MAX(CASE WHEN rn_akg = 1 THEN AKG END) AS AKG,
    MAX(CASE WHEN rn_bkg = 1 THEN BKG END) AS BKG,
    MAX(CASE WHEN rn_ckg = 1 THEN CKG END) AS CKG
FROM ranked_records
GROUP BY machineName
ORDER BY LEN(machineName), machineName;

方案3：利用LAST_VALUE简化（支持IGNORE NULLS的数据库） #

如果你的数据库（如PostgreSQL、Oracle）支持FILTER和IGNORE NULLS，可以用LAST_VALUE窗口函数直接提取最新非零值，代码更简洁：

WITH latest_values AS (
    SELECT
        DISTINCT machineName,
        LAST_VALUE(AKG) OVER (
            PARTITION BY machineName 
            ORDER BY time DESC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) FILTER (WHERE AKG <> 0) AS AKG,
        LAST_VALUE(BKG) OVER (
            PARTITION BY machineName 
            ORDER BY time DESC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) FILTER (WHERE BKG <> 0) AS BKG,
        LAST_VALUE(CKG) OVER (
            PARTITION BY machineName 
            ORDER BY time DESC 
            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        ) FILTER (WHERE CKG <> 0) AS CKG
    FROM vortexTable
)
SELECT * FROM latest_values
ORDER BY LEN(machineName), machineName;

注：SQL Server不支持FILTER语法，可将FILTER (WHERE X <> 0)替换为CASE WHEN X <> 0 THEN X ELSE NULL END，结合LAST_VALUE(...) IGNORE NULLS使用。

内容的提问来源于stack exchange，提问作者B. Ozen