如何用一條Sql語句搜索重復的記錄 和 如何用一條Sql語句查找出不重復的記錄

[huole]

如何用一條Sql語句篩選出重復的記錄

和 如何用一條Sql語句篩選出不重復的記錄

[dawugui]

如何用一條Sql語句篩選出重復的記錄

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

和 如何用一條Sql語句篩選出不重復的記錄

select a.* from tb a,
(select id , max(name) name from tb group by id) b
where a.id = b.id and a.name = b.name

select a.* from tb a where name = (select max(name) from tb where id = a.id)

[dawugui]

和 如何用一條Sql語句篩選出不重復的記錄 用min也行.

select a.* from tb a,
(select id , min(name) name from tb group by id) b
where a.id = b.id and a.name = b.name

select a.* from tb a where name = (select min(name) from tb where id = a.id)

[dawugui]

第二问题的更多写法:

c1 c2 type
第1行 a 9 5
第2行 b 8 5
第3行 c 7 6
第4行 d 6 6
第5行 e 5 7


--@Test
declare @test table(c1 varchar(1),c2 int,type int)
insert @test
select 'a',9,5 union all
select 'b',8,5 union all
select 'c',7,6 union all
select 'd',6,6 union all
select 'e',5,7

--按记录顺序取第一条
select * from @test a where c1=(select top 1 c1 from @Test where type=a.type)

--取最小
select * from @test a where c1=(select min(c1) from @Test where type=a.type)

--取最大
select * from @test a where c1=(select max(c1) from @Test where type=a.type)

--随机取
select * from @test a where c1=(select top 1 c1 from @Test where type=a.type order by newid())

[huole]

3q 3q