我的表名为详细信息。样本记录如下
ID PNO ActionsTaken
52101 2 Received
52101 5 Received
52101 3 Received
52101 3 Send To HO
52101 3 Send to RD
52101 7 Received
52101 8 Received
35501 2 Received
35501 7 Received
35501 7 Send To HO
我需要以下查询记录
ID PNO ActionsTaken
52101 2 Received
52101 5 Received
52101 7 Received
52101 8 Received
35501 2 Received
ActionTaken仅是"接收"的,没有获得其他操作,例如"发送到HO"或发送到RD。
表包含 7 Records 带有 [ActionStaken] as"接收"。但是结果集仅需要 5记录 [actionstaken] as"接收",这意味着结果需要具有 [ActionStaken] 为"接收" [ID] 和 [PNO] 不应具有 [ActionStaken] 以外的"接收"。
SELECT T1.*
FROM [Table] T1
WHERE T1.ActionsTaken = 'Received'
AND NOT EXISTS (
SELECT TOP 1 NULL
FROM [Table] T2
WHERE T2.ID = T1.ID
AND T2.PNO = T1.PNO
AND T2.ActionsTaken <> 'Received'
)
您的查询是
Select * from Details where ActionsTaken = 'Received'
@juozas op不想记录不同的记录,他只想以Actions_taken的速度进行记录,因此这对于以下问题非常完美:
SELECT ID,PNO,ActionsTaken FROM Details WHERE ActionsTaken = 'Received';
选择不同的[D1]。*从@details as [d1]左JOIN @details as [d2][d1]。[id] = [d2]。[id]和[d1]。[pno] = [d2]。[pno]和[d2]和[actionstaken]&lt;>'接收'其中[d1]。[actionstaken] ='接收'和[d2]。[id]为null;
;WITH CTE AS (
select ID,PNO from @Table1 where ActionsTaken <> 'Received'
)
select T.*
from @Table1 T
left join CTE c on t.ID=c.ID AND t.PNO = c.PNO
WHERE c.ID IS NULL
说明: -
在代码片段中,我使用CTE获取ID,所有记录的PNO,这些记录没有"接收"的值。因此,在这种情况下,CTE中将有2个记录
ID PNo
52101 3
35501 7
在下一个SQL语句中,它与CTE一起连接主表。没有任何地方条件(C.ID为null),结果集将为
ID PNO ActionsTaken ID PNo
52101 2 Received NULL NULL
52101 5 Received NULL NULL
52101 3 Received 52101 3
52101 3 Send To HO 52101 3
52101 3 Send to RD 52101 3
52101 7 Received NULL NULL
52101 8 Received NULL NULL
35501 2 Received NULL NULL
35501 7 Received 35501 7
35501 7 Send To HO 35501 7
现在,当应用哪个条件(C.ID为null)时,我们将使用ActionTaken获得记录。