sql - 如何进行条件连接

Question

我有两个表，连接键是 Id，下面是详细信息。我需要在表 Test Orgorder 为 1 且 [ORGTYPE] 为“ABC”时插入数据，然后我需要从 LEAD 中获取值。当[ORGTYPE]为'ABC'且Orgorder不等于1时，取ORGID最小的lead值。

问题是，每当我加入两个表时，我都得到 id 100 的两个值，但我想要 id 100 的单行。对于 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)))

我的结果是

预期结果是

Answer 1

我读到的是，如果 ORGID = 1 可用，您想要获取它，否则基于 ORGORDER 返回。您只会为每个 ID 保留一行。

with data as (
    SELECT TES.NAME, TEST.*,
        row_number() over (
            partition by TEST.ID
            order by case when TEST.ORGID = 1 then 1 else 2 end,
                     TEST.ORGORDER
        ) as rn
    FROM TES INNER JOIN TEST on TEST.ID = TES.ID
    WHERE TEST.ORGTYPE = 'ABC'
)
select * from data where rn = 1;

这里没有任何迹象表明您需要左连接。

按照您原来的思路，您可能会想到这样的事情:

select TEST.ID, TEST.ORGORDER, TEST.ORGTYPE, TEST.ORGID,
    case when TEST.ORGID = 1 then TEST."LEAD" else (
        select "LEAD" from TEST
        where ID = TEST2.ID and ORGORDER = TEST2.MIN_ORGORDER
    ) end as "LEAD"
from TES inner join TEST on TEST.ID = TES.ID
    inner join (
        select ID,
            min(ORGORDER) as MIN_ORGORDER,
            max(case when ORGID = 1 then 'Y' else 'N' end) as HAS_ORGID1
        from TEST
        group by ID
    ) as TEST2 on TEST2.ID = TEST.ID
where TEST.ORGID = 1 or TEST2.HAS_ORGID1 = 'N' and TEST2.MIN_ORGORDER = TEST.ORGORDER;

第一个更清晰并且可能更有效。

https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=252da6d3bd2a233d161fce5c828355c7