在MSSQL里,如何筛选出多个列的内容相同的数据,如:
ID Name Desc. Unit QTY
001 ABC 15*20 PCS 15
001 ABC 15*20 PCS 16
001 ABC 15*30 PCS 10
002 DEE 21/25 PCS 110
002 DEE 21/25 PCS 10
002 DEE 18/25 PCS 110
要把 ID,Name, Desc. 内容都相同的数据选择出来。语句要怎么写?
create table t1
(
ID varchar(10),
Name varchar(10),
[Desc.] varchar(10),
Unit varchar(10),
QTY int
)
insert into t1 select '001', 'ABC', '15*20', 'PCS', 15
union all select '001', 'ABC', '15*20', 'PCS', 16
union all select '001', 'ABC', '15*30', 'PCS', 10
union all select '002', 'DEE', '21/25', 'PCS', 110
union all select '002', 'DEE', '21/25', 'PCS', 10
union all select '002', 'DEE', '18/25', 'PCS', 110
查询:
select * from t1 as a where (select count(1) from t1 where [id]=a.[id] and [name]=a.[name]
and [Desc.]=a.[Desc.])>1
结果:
select ID,Name, [Desc.]
from a
group by ID,Name, [Desc.]
having count(Unit)>1
ID Name Desc.
---------- ---------- ----------
001 ABC 15*20
002 DEE 21/25
(所影响的行数为 2 行)