關閉標題：V_MTRREAD
內容：


/*

舊寫法

WITH V_MTRLOC_dedup AS (
        SELECT *, 
               ROW_NUMBER() OVER (PARTITION BY interface_id, sqnc ORDER BY (SELECT NULL)) AS rn
        FROM dbo.V_MTRLOC
    ),
    All_MtrRead AS (
        SELECT interface_id, sqnc, rcvtime, value_i, value_c, sgnl_type
        FROM dbo.mtrread_l2
        )
    SELECT 
        V.mtrname, V.unit_id, V.ctgr, V.sttn_id, V.sttn_name, V.lat, V.lng, V.unit_name,
        M.interface_id, M.sqnc, M.rcvtime, 
        M.value_i, M.value_c, M.sgnl_type, V.rm, 
        dbo.FN_CONVERT_TO_DATETIME(M.rcvtime) AS RCV
    FROM All_MtrRead M
    INNER JOIN V_MTRLOC_dedup V 
        ON M.interface_id = V.interface_id 
       AND M.sqnc = V.sqnc 
       AND V.rn = 1
			 
*/


WITH V_MTRLOC_dedup AS (
    SELECT interface_id, sqnc, mtrname, unit_id, ctgr, sttn_id, 
           sttn_name, lat, lng, unit_name, rm
    FROM (
        SELECT *,
               ROW_NUMBER() OVER (
                   PARTITION BY interface_id, sqnc 
                   ORDER BY mtrloc_id DESC   -- 明確排序
               ) rn
        FROM dbo.V_MTRLOC
    ) A
    WHERE rn = 1
)
SELECT 
    V.mtrname, V.unit_id, V.ctgr, V.sttn_id, V.sttn_name, V.lat, V.lng, V.unit_name,
    M.interface_id, M.sqnc, M.rcvtime, 
    M.value_i, M.value_c, M.sgnl_type, 
    V.rm,
    dbo.FN_CONVERT_TO_DATETIME(M.rcvtime) AS RCV
FROM dbo.mtrread_l2 M
INNER JOIN V_MTRLOC_dedup V 
    ON M.interface_id = V.interface_id 
   AND M.sqnc = V.sqnc;