select id from tb
group by id
having count(1)>1
;with ldslove
as(
select *,
px=row_number()over(partition by id order by (select 1))
from [aa])
select *
from aa
where px=1
select * from aa a
where (select count(1) from aa where a.id=id)=1
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([id] [int],[name] [nvarchar](10))
INSERT INTO [tb]
SELECT '1','张三' UNION ALL
SELECT '2','李四' UNION ALL
SELECT '2','李四' UNION ALL
SELECT '2','' UNION ALL
SELECT '3','王五' UNION ALL
SELECT '3','王五'
-->SQL查询如下:
SELECT DISTINCT * FROM [tb] WHERE ISNULL(name,'')<>''
/*
id name
----------- ----------
1 张三
2 李四
3 王五
(3 行受影响)
*/
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([id] [int],[name] [nvarchar](10))
INSERT INTO [tb]
SELECT '1','张三' UNION ALL
SELECT '2','李四' UNION ALL
SELECT '2','..' UNION ALL
SELECT '3','王五' UNION ALL
SELECT '3','王五'
id name
----------- ----------
1 张三
2 ..
3 王五
select id1=identity (int,1,1),* into #t from aa where name is not null and ltrim(name) <>''
select id,name from #t t1 where not exists(select 1 from #t where name=t1.name and id1<t1.id1)
