TSQL查找复制
问题描述:
查找重复行与计数器值TSQL查找复制
假设一个表中给出
ID Name Age
-----------------
1 Jon 30
2 Skeet 30
1 Jon 30
4 Gravell 30
5 NULL 30
4 Gravell 30
5 NULL 30
7 James 40
需要的输出(NULL也应该比较的)
ID Name Age Description
----------------- -----------
1 Jon 30 Found 1
1 Jon 30 Found 2
4 Gravell 30 Found 1
4 Gravell 30 Found 2
5 NULL 30 Found 1
5 NULL 30 Found 2
7 James 40 Found 1
2 Skeet 30 Found 1
为了找到重复我可以执行查询
select ID,Name,Age from tableA group by ID,Name,Age having count(*) >1
如何生成描述?
答
SELECT
ID, Name, Age,
'Found ' + CAST(ROWNUMBER() OVER (PARITION BY Name ORDER BY ID) AS varchar(10)) AS Description
FROM
MyTable
ORDER BY
ID, Description
你期望的输出顺序是在ID/Name级别
找到重复的基本上是随机的...
SELECT
ID, Name, Age, 'Found ' + Countof AS Description
FROM
(
SELECT
ID, Name, Age,
CAST(ROWNUMBER() OVER (PARITION BY Name ORDER BY ID) AS varchar(10)) AS Countof
FROM
MyTable
) foo
WHERE
Countof > 1
答
试试这个 -
select ID,Name,Age, ('Found ' + cast(count(*) as varchar(5))) as description
from tableA group by ID,Name,Age having count(*) >1
不知道我理解。你怎么能有1 - 乔恩 - 30 - 找到1,然后1乔恩 - 30 - 找到2.不应该1 - 乔恩 - 30 - 发现2是唯一的记录(因为它的计数) – RPM1984 2010-09-04 09:01:58