常用sql：过滤重复数据查询如何写

[hxy102863729]

RT
有表[aa]
id name
1 张三
2 李四
2 李四
2 ‘’
3 王五
3 王五

过滤id重复的SQL？

[东那个升]

name 怎么筛选

[东那个升]

select id from tb 
group by id 
having count(1)>1

[bill024]

select distinct id,name from [aa]

[htl258_Tony]

select distindt * from aa t where name=(select top 1 name from aa where id=t.id)

[水族杰纶]

;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

[hxy102863729]

name 取不为NULL中选取一个

[hxy102863729]

SQL2000

[htl258_Tony]

--> 生成测试数据表: [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 行受影响)
*/

[hxy102863729]

我的意思要id 不允许重复，

[shixixi1987]

select id from table1 where [name] is not null group by id having count(1)>1
go

[hxy102863729]

[Quote=引用 9 楼 htl258 的回复:]

SQL code
--> 生成测试数据表: [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','李四' UNI……
[/Quote]

--> 生成测试数据表: [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           王五

我想id唯一。

[hxy102863729]

求解！！！

[永生天地]

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)

[hxy102863729]

[Quote=引用 14 楼 xys_777 的回复:]

SQL code


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……
[/Quote]
貌似我这得到的数据不对，而且能不用临时表么，
我只想把id重复的过滤。其他字段不为null的随便取一个就行

[htl258_Tony]

[Quote=引用 15 楼 hxy102863729 的回复:]
引用 14 楼 xys_777 的回复:

SQL code


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 n……
[/Quote]
name这一列，没什么规律吗，什么情况都有吗？

[hxy102863729]

name 无规律 就是null 和有数据2种情况

[hxy102863729]

To htl258
您的这个可以
select DISTINCT * from tb t where name =(select top 1 name from tb where id=t.id)
结贴