SQL Server 2016：单表中多行多列的值比较方法

我来帮你搞定这个多行多列的比较需求～首先先把你提供的测试表和数据整理完善（修正了字段名的拼写错误，补全了最后一行示例数据，保证能正常运行）：

USE dev_db
GO
CREATE TABLE T1_VALS (
 [SITE_ID] [int] NULL,
 [LATITUDE] [numeric](10, 6) NULL,
 [UNIQUE_ID] [int] NULL,
 [COLLECT_RANK] [int] NULL,
 [CREATED_RANK] [int] NULL,
 [UNIQUE_ID_RANK] [int] NULL,
 [UPDATE_FLAG] [int] NULL
)
GO
INSERT INTO T1_VALS (SITE_ID,LATITUDE,UNIQUE_ID,COLLECT_RANK,CREATED_RANK,UNIQUE_ID_RANK)
VALUES
(207442,40.900470,59664,1,1,1),
(207442,40.900280,61320,1,1,2),
(204314,40.245220,48685,1,2,2),
(204314,40.245910,59977,1,1,1),
(202416,39.449530,9295,1,1,2),
(202416,39.449680,62123,1,2,1);
GO

下面针对几种常见的多行多列比较场景，给你具体的实现方法：

场景1：对比同SITE_ID下不同行的字段差异 #

如果想直观看到同一个站点（SITE_ID）下两行的纬度、唯一ID等字段的差异，用自连接就能轻松实现：

SELECT
    t1.SITE_ID,
    t1.LATITUDE AS 纬度_行1,
    t2.LATITUDE AS 纬度_行2,
    ABS(t1.LATITUDE - t2.LATITUDE) AS 纬度差值,
    t1.UNIQUE_ID AS 唯一ID_行1,
    t2.UNIQUE_ID AS 唯一ID_行2,
    t1.CREATED_RANK AS 创建排名_行1,
    t2.CREATED_RANK AS 创建排名_行2
FROM T1_VALS t1
JOIN T1_VALS t2 ON t1.SITE_ID = t2.SITE_ID AND t1.UNIQUE_ID <> t2.UNIQUE_ID;

这个查询会把每个站点下的不同行两两配对，直接展示各字段的数值差异，比如纬度的差值一目了然。

场景2：按优先级筛选每个站点的最优行 #

从你的数据来看，COLLECT_RANK、CREATED_RANK这些字段应该是优先级标识（数值越小优先级越高）。如果要为每个站点选出综合优先级最高的行，用窗口函数ROW_NUMBER()就很方便：

WITH 站点排名 AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY SITE_ID
            ORDER BY COLLECT_RANK, CREATED_RANK, UNIQUE_ID_RANK
        ) AS 行序号
    FROM T1_VALS
)
SELECT *
FROM 站点排名
WHERE 行序号 = 1;

这里的排序逻辑是先看COLLECT_RANK，再看CREATED_RANK，最后看UNIQUE_ID_RANK，每个站点下排第一的就是最优行。如果存在多个行的优先级完全相同的情况，你可以把ROW_NUMBER()换成RANK()，这样会保留所有并列的最优行。

场景3：标记需要更新的行（设置UPDATE_FLAG） #

如果要根据同站点内的行对比结果，自动设置UPDATE_FLAG（比如把非最优行标记为待更新），可以结合窗口函数和条件判断来实现：

WITH 站点基准值 AS (
    SELECT
        *,
        MIN(CREATED_RANK) OVER (PARTITION BY SITE_ID) AS 最小创建排名,
        MIN(UNIQUE_ID_RANK) OVER (PARTITION BY SITE_ID) AS 最小唯一ID排名
    FROM T1_VALS
)
UPDATE 站点基准值
SET UPDATE_FLAG = CASE
    WHEN CREATED_RANK = 最小创建排名 AND UNIQUE_ID_RANK = 最小唯一ID排名 THEN 0  -- 最优行，无需更新
    ELSE 1  -- 非最优行，标记为待更新
END;

这个语句会先为每个站点找出最小的CREATED_RANK和UNIQUE_ID_RANK，然后自动把不满足这两个最优条件的行的UPDATE_FLAG设为1，非常适合批量处理更新标记的需求。

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