如果我有这样一组数据:
Key | Name |
---|---|
1 | Dan |
2 | Tom |
3 | Jon |
4 | Tom |
5 | Sam |
6 | Dan |
请问如何编写 SQL 查询语句,以筛选出 Name
字段重复两次或更多的记录呢?
即想要的结果为:
Tom |
---|
Dan |
如果我有这样一组数据:
Key | Name |
---|---|
1 | Dan |
2 | Tom |
3 | Jon |
4 | Tom |
5 | Sam |
6 | Dan |
请问如何编写 SQL 查询语句,以筛选出 Name
字段重复两次或更多的记录呢?
即想要的结果为:
Tom |
---|
Dan |
再简单不过了...
Select Name, Count(Name) As Count
From Table
Group By Name
Having Count(Name) > 1
Order By Count(Name) Desc
这也可以扩展到删除重复项:
Delete From Table
Where Key In (
Select Max(Key)
From Table
Group By Name
Having Count(Name) > 1
)
select name from table group by name having count(name) > 1
SELECT DISTINCT t1.name
FROM tbl t1
INNER JOIN tbl t2
ON t1.name = t2.name
WHERE t1.key != t2.key;