select * from tb a where exists(select 1 from tb where col1=a.col1 and col2=a.col2 and col3=a.col3 and...)
select * from tb group by ..... having count(*) > 1 -- group by 后所有字段!
http://www.cnblogs.com/zengxiangzhan/archive/2009/09/12/1565320.html --drop table t --DELETE tINSERT INTO t SELECT '1' UNION ALL SELECT '2' UNION ALL SELECT '3' UNION ALL SELECT '5' UNION ALL SELECT '5' UNION ALL SELECT '5' UNION ALL SELECT '6' UNION ALL SELECT '3' UNION ALL SELECT '4'
/* 找出相同的 */3 5 5 5 3--1. in SELECT * FROM t WHERE NAME IN ( SELECT name FROM t GROUP BY name HAVING COUNT(name) > 1 ) --2. joinSELECT t.* FROM t JOIN ( SELECT name FROM t GROUP BY name HAVING COUNT(name) > 1 ) a ON T.NAME = a.name --3 . EXISTS SELECT * FROM t WHERE EXISTS ( SELECT * FROM ( SELECT name FROM t GROUP BY name HAVING COUNT(name) > 1 ) a WHERE a.NAME = t.name )-- 4. 2005 ROW_NUMBER() SELECT t.* FROM t JOIN ( SELECT * FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY NAME ORDER BY name ) AS id , name FROM t ) a WHERE id = 2 ) b ON t.NAME = b.name
if object_id('tb') is not null drop table tb create table tb (id int , c1 varchar(200), c2 varchar(200)) goinsert tb select 1, 'asd','a' union all select 1, 'asd','a' union all select 2, 'asd','b' union all select 3, 'asdf','c' union all select 3, 'asdf','c';with a as ( select row_number() over(partition by checksum(*) order by id) as num,checksum(*) as cs,* from tb )select * from a where num > 1
select * from tb group by ..... having count(*) > 1
-- group by 后所有字段!
--drop table t
--DELETE tINSERT INTO t
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '3' UNION ALL
SELECT '5' UNION ALL
SELECT '5' UNION ALL
SELECT '5' UNION ALL
SELECT '6' UNION ALL
SELECT '3' UNION ALL
SELECT '4'
/* 找出相同的 */3
5
5
5
3--1. in
SELECT *
FROM t
WHERE NAME IN ( SELECT name
FROM t
GROUP BY name
HAVING COUNT(name) > 1 )
--2. joinSELECT t.*
FROM t
JOIN ( SELECT name
FROM t
GROUP BY name
HAVING COUNT(name) > 1
) a ON T.NAME = a.name
--3 . EXISTS
SELECT *
FROM t
WHERE EXISTS ( SELECT *
FROM ( SELECT name
FROM t
GROUP BY name
HAVING COUNT(name) > 1
) a
WHERE a.NAME = t.name )-- 4. 2005 ROW_NUMBER()
SELECT t.*
FROM t
JOIN ( SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY NAME ORDER BY name ) AS id ,
name
FROM t
) a
WHERE id = 2
) b ON t.NAME = b.name
drop table tb
create table tb (id int , c1 varchar(200), c2 varchar(200))
goinsert tb
select 1, 'asd','a'
union all
select 1, 'asd','a'
union all
select 2, 'asd','b'
union all
select 3, 'asdf','c'
union all
select 3, 'asdf','c';with a as
(
select row_number() over(partition by checksum(*) order by id) as num,checksum(*) as cs,* from tb
)select * from a where num > 1