行返回ID具有重复数据
问题描述:
我需要得到具有行的行ID重复行返回ID具有重复数据
Select Name from table1 group by Name having count(1) > 1
table1的
ID | Name | ClientID
----------------------------
01 | John | 01
02 | Sam | 01
03 | Sue | 01
04 | John | 02
05 | John | 01
唯一的问题是,它只会返回名称而不是列的ID,因为我在同一个表上有多个客户端,我不想将其他客户端的名称作为重复项。
那么有没有办法在表中查找重复数据并返回行的ID,以便我可以在另一个查询中使用这些ID?
答案我有修改
去感谢你们每一个人,因为我这一个这是一个我标记为答案的修改去了答案。
select t1.*
from (select count(*) over (partition by entityname) as cnt ,t1.*
from table1 t1 where ClientID = 1
) t1
where cnt > 1 and ClientID = 1 order by cnt;
答
使用窗函数:行每个名称的
select t1.*
from (select t1.*, count(*) over (partition by name) as cnt
from table1 t1
) t1
where cnt > 1;
的count(*) over (partition by name)
次数进行计数。但是,它通过在每行上附加计数来实现此目的,而不是通过减少行数来实现。这是您选择行所需的信息。这里
答
要看到受骗者的链接
Declare @Yourtable table (ID varchar(25),Name varchar(50),Client_ID varchar(25))
Insert into @Yourtable values
('01','John','01'),
('02','Sam' ,'01'),
('03','Sue' ,'01'),
('04','John','02'),
('05','John','01')
Select A.*
,B.Dupes
From @YourTable A
Cross Apply (Select Dupes=(Select Stuff((Select Distinct ',' + cast(ID as varchar(25))
From @YourTable
Where ID<>A.ID and Name=A.Name
For XML Path ('')),1,1,'')
)
) B
Where Dupes is not null
返回
ID Name Client_ID Dupes
01 John 01 04,05
04 John 02 01,05
05 John 01 01,04
答
可以查询这样
;WITH cte_duplicates
AS (SELECT
id, name, client_id,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) AS rc
FROM @Yourtable)
SELECT
id, name, client_id
FROM cte_duplicates
WHERE rc > 1
答
如果你想fileter了基于这两个名字的重复和ClientId,使用下面的查询。
; with cte_1
as (select *, count(*) over (partition by name,client_id order by ID) as dups
from table1 )
Select *
From cte_1
where dups> 1;
哇。好,那么你能为我解释一下吗?这看起来像我想要使用,但我不明白那里发生了什么。谢谢。 – Xaedblade