我有兩個表，連接鍵是 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)))
我的結果是
預期結果是
uj5u.com熱心網友回復：
我在讀的是，你想抓住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
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/349940.html
標籤：sql sql-server