SQL 重复数据查询

[yajun_snow]

item oem oem_no
0001 a b
0001 c m
0001 a c
0002 q v
0002 q v
0003 r a


想得到，相同的item只取一条数据（可以任意一条）

得到
0001 a b
0002 q v
0003 r a

[中国风]

select *
from 
(select *,row=row_number()over(partition by item order by oem) from t1)t
where row=1

[SQL77]

SELECT DISTINCT * FROM TB T WHERE OEM=(SELECT MIN(OEM) FROM TB WHERE ITEM=T.ITEM)

[中国风]

SQL2000參照
http://topic.csdn.net/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html?32698

[SQL77]

SELECT DISTINCT * FROM TB T WHERE OEM=(SELECT MAX(OEM) FROM TB WHERE ITEM=T.ITEM)

[feixianxxx]

[Quote=引用 3 楼 roy_88 的回复:]
SQL2000參照
http://topic.csdn.net/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html?32698
[/Quote]
很久不见ROY哥

[dawugui]

--如果是2005用row_number() over()

--以下是2000的写法

create table tb(item varchar(10), oem varchar(10), oem_no varchar(10))
insert into tb values('0001' , 'a' , 'b') 
insert into tb values('0001' , 'c' , 'm') 
insert into tb values('0001' , 'a' , 'c') 
insert into tb values('0002' , 'q' , 'v') 
insert into tb values('0002' , 'q' , 'v') 
insert into tb values('0003' , 'r' , 'a')
go


select distinct t.* from tb t where oem_no = (select min(oem_no) from tb where item = t.item)

select distinct t.* from tb t where not exists (select 1 from tb where item = t.item and oem_no < t.oem_no)

select distinct item,oem,oem_no from
(
select t.* , px = (select count(1) from tb where item = t.item and (oem_no < t.oem_no or (oem_no = t.oem_no and oem < t.oem) )) + 1 from tb t
) m where px = 1

drop table tb 

/*
item       oem        oem_no     
---------- ---------- ---------- 
0001       a          b
0002       q          v
0003       r          a

（所影响的行数为 3 行）
*/

[--小F--]

select 
  distinct * 
from 
  tb t 
where 
  not exists (select 1 from tb where item = t.item and oem_no < t.oem_no)

[gengyin622]

可以用group by 实现， 计数count>1就行了

这样你还可以知道有几个重复值

[wldzjj]

SELECT * FROM `tb` group by item

[fhjzgsy]

DECLARE @t TABLE (id INT,tac VARCHAR(2),tbc VARCHAR(2))
INSERT INTO @t
SELECT 001,'a','b' UNION ALL
SELECT 001,'c','m' UNION ALL
SELECT 001,'a','c' UNION ALL
SELECT 002,'q','v' UNION ALL
SELECT 002,'q','v' union all
SELECT 003,'r','a'

select id,tac,tbc
from 
(select *,row=row_number()over(partition by id order by id) from @t)t
where row=3