我有一个组织的SQL Server数据库，有许多重复的行。我想运行一个选择语句来获取所有这些和被欺骗的数量，同时还返回与每个组织相关的id。
这样的陈述:
SELECT orgName, COUNT(*) AS dupes
FROM organizations
GROUP BY orgName
HAVING (COUNT(*) > 1)
将返回如下内容
orgName | dupes
ABC Corp | 7
Foo Federation | 5
Widget Company | 2
但我也想要他们的id。有什么办法可以做到吗?也许就像
orgName | dupeCount | id
ABC Corp | 1 | 34
ABC Corp | 2 | 5
...
Widget Company | 1 | 10
Widget Company | 2 | 2
原因是还有一个单独的用户表链接到这些组织，我想把它们统一起来(因此删除dupes，用户链接到同一个组织，而不是dupe组织)。但我想手动部分，所以我不会搞砸任何事情，但我仍然需要一个语句返回所有的dupe组织的id，这样我就可以通过用户列表。
假设我们有一个表'Student'，有两列:
student_id int
student_name varchar
记录:
+------------+---------------------+
| student_id | student_name |
+------------+---------------------+
| 101 | usman |
| 101 | usman |
| 101 | usman |
| 102 | usmanyaqoob |
| 103 | muhammadusmanyaqoob |
| 103 | muhammadusmanyaqoob |
+------------+---------------------+
现在我们希望看到重复的记录
使用这个查询:
select student_name,student_id ,count(*) c from student group by student_id,student_name having c>1;
+---------------------+------------+---+
| student_name | student_id | c |
+---------------------+------------+---+
| usman | 101 | 3 |
| muhammadusmanyaqoob | 103 | 2 |
+---------------------+------------+---+
有几种方法可以选择重复的行。
对于我的解决方案，首先考虑以下表格为例
CREATE TABLE #Employee
(
ID INT,
FIRST_NAME NVARCHAR(100),
LAST_NAME NVARCHAR(300)
)
INSERT INTO #Employee VALUES ( 1, 'Ardalan', 'Shahgholi' );
INSERT INTO #Employee VALUES ( 2, 'name1', 'lname1' );
INSERT INTO #Employee VALUES ( 3, 'name2', 'lname2' );
INSERT INTO #Employee VALUES ( 2, 'name1', 'lname1' );
INSERT INTO #Employee VALUES ( 3, 'name2', 'lname2' );
INSERT INTO #Employee VALUES ( 4, 'name3', 'lname3' );
第一个解决方案:
SELECT DISTINCT *
FROM #Employee;
WITH #DeleteEmployee AS (
SELECT ROW_NUMBER()
OVER(PARTITION BY ID, First_Name, Last_Name ORDER BY ID) AS
RNUM
FROM #Employee
)
SELECT *
FROM #DeleteEmployee
WHERE RNUM > 1
SELECT DISTINCT *
FROM #Employee
第二个解决方案:使用标识字段
SELECT DISTINCT *
FROM #Employee;
ALTER TABLE #Employee ADD UNIQ_ID INT IDENTITY(1, 1)
SELECT *
FROM #Employee
WHERE UNIQ_ID < (
SELECT MAX(UNIQ_ID)
FROM #Employee a2
WHERE #Employee.ID = a2.ID
AND #Employee.FIRST_NAME = a2.FIRST_NAME
AND #Employee.LAST_NAME = a2.LAST_NAME
)
ALTER TABLE #Employee DROP COLUMN UNIQ_ID
SELECT DISTINCT *
FROM #Employee
所有解决方案的最后使用这个命令
DROP TABLE #Employee
我使用两个方法来查找重复的行。
第一种方法是最著名的用分组的方法。
第二种方法是使用CTE -公共表表达式。
正如@RedFilter提到的那样，这种方式也是正确的。很多时候我发现CTE方法对我也很有用。
WITH TempOrg (orgName,RepeatCount)
AS
(
SELECT orgName,ROW_NUMBER() OVER(PARTITION by orgName ORDER BY orgName)
AS RepeatCount
FROM dbo.organizations
)
select t.*,e.id from organizations e
inner join TempOrg t on t.orgName= e.orgName
where t.RepeatCount>1
在上面的例子中，我们通过使用ROW_NUMBER和PARTITION by找到重复出现的情况来收集结果。然后应用where子句只选择重复次数大于1的行。将所有结果收集到CTE表中，并与Organizations表进行联接。
来源:CodoBee
