select id, myText, myFlag from tablename group by id, myText, myFlag having count(1) > 1
select id, myText, myFlag from tablename group by id, myText, myFlag having count(1) > 1
declare @ta table (id int, myText varchar(5), myFlag varchar(2)) insert @ta select 1, '工业', 'G'union all select 1, '工业', 'G'union all select 2, '农业', 'J'union all select 2, '农业', 'X'union all select 3, '商业', 'J'select distinct * from @ta where BINARY_CHECKSUM(id,myText,myFlag)in (select BINARY_CHECKSUM(*) from @ta group by BINARY_CHECKSUM(id,myText,myFlag) having count(1)>1)(所影响的行数为 5 行)id myText myFlag ----------- ------ ------ 1 工业 G(所影响的行数为 1 行)
select * from 表 group by id,myText,myFlag having count(*)>1
declare @ta table (id int, myText varchar(5), myFlag varchar(2)) insert @ta select 1, '工业', 'G'union all select 1, '工业', 'G'union all select 2, '农业', 'J'union all select 2, '农业', 'X'union all select 3, '商业', 'J' --BINARY_CHECKSUM(id,myText,myFlag)--在括号内指定列 select distinct * from @ta where BINARY_CHECKSUM(id,myText,myFlag)in (select BINARY_CHECKSUM(id,myText,myFlag) from @ta group by BINARY_CHECKSUM(id,myText,myFlag) having count(1)>1)
from tablename
group by id, myText, myFlag
having count(1) > 1
from tablename
group by id, myText, myFlag
having count(1) > 1
insert @ta
select 1, '工业', 'G'union all
select 1, '工业', 'G'union all
select 2, '农业', 'J'union all
select 2, '农业', 'X'union all
select 3, '商业', 'J'select distinct * from @ta where BINARY_CHECKSUM(id,myText,myFlag)in
(select BINARY_CHECKSUM(*) from @ta group by BINARY_CHECKSUM(id,myText,myFlag) having count(1)>1)(所影响的行数为 5 行)id myText myFlag
----------- ------ ------
1 工业 G(所影响的行数为 1 行)
insert @ta
select 1, '工业', 'G'union all
select 1, '工业', 'G'union all
select 2, '农业', 'J'union all
select 2, '农业', 'X'union all
select 3, '商业', 'J'
--BINARY_CHECKSUM(id,myText,myFlag)--在括号内指定列
select distinct * from @ta where BINARY_CHECKSUM(id,myText,myFlag)in
(select BINARY_CHECKSUM(id,myText,myFlag) from @ta group by BINARY_CHECKSUM(id,myText,myFlag) having count(1)>1)