如何实现SQL条件连接，按规则匹配TEST表数据，每个ID仅返回一行结果

两张表通过Id字段关联，具体规则与问题如下：

• 取数规则：当TEST表的Orgorder为1且[ORGTYPE]为'ABC'时，取该行LEAD值；当[ORGTYPE]为'ABC'且Orgorder不等于1时，取ORGID最小的对应行LEAD值。
• 存在问题：两张表连接后，ID=100返回了两行数据，不符合需求，期望每个ID仅返回一行结果，其中ID=101需匹配ORGID最小值0对应的LEAD值。

CREATE TABLE TES(
ID int,
Name Varchar(10)
)
Insert into TES (ID,NAME) VALUES (100,'XYZ')
Insert into TES (ID,NAME) VALUES (101,'XYZ')

CREATE TABLE [dbo].[TEST](
    [ORGORDER] [int] NULL,
    [Id] [int] NOT NULL,
    [ORGTYPE] [varchar](30) NULL,
    ORGID INT NULL,
    [LEAD] [decimal](19, 2) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TEST] ([ORGORDER], [Id], [ORGTYPE] ,ORGID, [LEAD]) VALUES (1, 100, N'ABC',1, NULL)
GO
INSERT [dbo].[TEST] ([ORGORDER], [Id], [ORGTYPE],ORGID, [LEAD]) VALUES (0, 100, N'ABC',2, 0)
GO
INSERT [dbo].[TEST] ([ORGORDER], [Id], [ORGTYPE],ORGID, [LEAD]) VALUES (0, 100, N'ACD',1, NULL)
GO
INSERT [dbo].[TEST] ([ORGORDER], [Id], [ORGTYPE],ORGID, [LEAD]) VALUES (0, 101, N'ABC',0, 1)
GO
INSERT [dbo].[TEST] ([ORGORDER], [Id], [ORGTYPE],ORGID, [LEAD]) VALUES (2, 101, N'ABC',4, NULL)
GO

SELECT  * FROM TES LEFT JOIN TEST on TES.ID=TEST.ID
AND TEST.ORGTYPE='ABC' and (TEST.ORGORDER=1
OR OrgOrder=(
Select top 1 [LEAD]  from TEST 
where ORGID in (select min(ORGID) from TEST
where OrgType = 'ABC' group by ID)))

原有SQL逻辑错误：子查询错误将LEAD字段值作为OrgOrder的匹配条件，且未按ID分组筛选唯一行，导致返回多行数据。

使用窗口函数按优先级排序后取每个ID的第一行即可符合需求：

SELECT * 
FROM TES t1
LEFT JOIN (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY Id 
            ORDER BY 
                CASE WHEN ORGORDER=1 AND ORGTYPE='ABC' THEN 1 ELSE 2 END,
                ORGID ASC
        ) AS rn
    FROM TEST
    WHERE ORGTYPE='ABC'
) t2 ON t1.ID = t2.Id AND t2.rn = 1

• 实际运行结果：
• 预期结果：

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