获取所有重复ID [英] Get all Duplicate IDs

问题描述

我有一张桌子

I have a table

ProcessID	ID	    DupId
4	        5555	 4444
3	        4444	 3333
2	        3333	 2222
1	        2222	  0
44	        1212	 1111
33	        1111	 1010
22	        1010	  0

If i pass parameter as ProsessID = 4 i need output like this:

ID
5555
4444
3333

If i pass parameter as ProsessID = 44 i need output like this:

ID
1212
1111
1010

Please help

推荐答案

如果它只是一个单独的层次结构，您可以使用它，如下所示，

If it is just a single hierarchy find you can use it as below,

<pre lang="C#">
Declare @i int = 44 -- This will be your process Id
Select P1.Id, P1.DupId, P2.DupId from ProcessTable P1
left join ProcessTable P2 on P1.DupId = P2.Id
where P1.ProcessId = @i
But if your hierarchy is more than 1 then you can use Common Table expresssion.
Also your result will come in single row and not mutiple rows.</pre>

我认为这个是你在看什么

I think this is what you are looking

DECLARE @ProcessId AS int = 44;
with Duplicates(ID) as
(
SELECT distinct ID FROM ProcessWithDuplicates where ProcessID = @ProcessId
UNION ALL
SELECT DupId FROM ProcessWithDuplicates where ProcessID = @ProcessId
UNION ALL
(
    SELECT DupId FROM ProcessWithDuplicates where ID IN (SELECT DupId FROM ProcessWithDuplicates where ProcessID = @ProcessId)
        or  DupId in (SELECT DupId FROM ProcessWithDuplicates where ProcessID = @ProcessId)
)
)
select distinct ID from Duplicates;

赞

Like

SELECT DupId FROM ProcessWithDuplicates where ID IN	(SELECT DupId FROM ProcessWithDuplicates where ProcessID = @ProcessId) 
			or  DupId in (SELECT DupId FROM ProcessWithDuplicates where ProcessID = @ProcessId)

你需要。希望这会有所帮助。

You need.Hope this will help.

这篇关于获取所有重复ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！