DECLARE @table TABLE(id INT, [name] VARCHAR(100), age INT) INSERT @table SELECT 1, 'a', 14 UNION ALL SELECT 2, 'b', 15 UNION ALL SELECT 3, 'a', 14--#1 SELECT * FROM @table a WHERE (SELECT COUNT(*) FROM @table WHERE [name] = a.[name] AND age = a.age) > 1 --#2 SELECT b.* FROM ( SELECT [name], age, cnt=COUNT(*) FROM @table GROUP BY [name], age HAVING COUNT(*) > 1 ) a CROSS APPLY (SELECT * FROM @table WHERE [name] = a.[name] AND age = a.age) b
use tempdb; /* create table A ( id int not null, name nvarchar(10) not null ); insert into A(id,name) values (1,'test'), (1,'test'), (2,'admin'), (3,'abc'), (3,'abc'), (4,'admin'); */ select * from A where id not in ( select id from A group by id having COUNT(*) = 1 );
如果是一个字段重复,例如ID. select * from tb t where id in (select id from tb group by id having count(1) > 1)如果是多个字段重复,例如ID1,ID2 select * from tb t where exists (select 1 from (select id1,id2 from tb group by id1,id2 having count(1) > 1) n where t.id1 = n.id1 and t.id2 = n.id2)
--nYear 为重复字段select * from Table_Time where nYear in (select nYear from dbo.Table_Time group by nYear having(COUNT(*)>=2) )
你懂的 having count(*)>=2用>=替代>,区别自己去百度
select columns1,columns2 from tb group by columns1,columns2 having(*)>1
select * from tb group by tid having count(*)>1
group by column1,column2,column3,column4,column5....... having count(1)>=2
select file1 from tb group by file1 having count(1)>1
group by xx,xx having count(*) > 1
INSERT @table
SELECT 1, 'a', 14 UNION ALL
SELECT 2, 'b', 15 UNION ALL
SELECT 3, 'a', 14--#1
SELECT * FROM @table a
WHERE (SELECT COUNT(*) FROM @table WHERE [name] = a.[name] AND age = a.age) > 1
--#2
SELECT b.* FROM
(
SELECT [name], age, cnt=COUNT(*) FROM @table
GROUP BY [name], age
HAVING COUNT(*) > 1
) a
CROSS APPLY
(SELECT * FROM @table WHERE [name] = a.[name] AND age = a.age) b
use tempdb;
/*
create table A
(
id int not null,
name nvarchar(10) not null
);
insert into A(id,name)
values
(1,'test'),
(1,'test'),
(2,'admin'),
(3,'abc'),
(3,'abc'),
(4,'admin');
*/
select *
from A
where id not in
(
select id
from A
group by id
having COUNT(*) = 1
);
这里有个 大版的
http://topic.csdn.net/u/20080626/00/43d0d10c-28f1-418d-a05b-663880da278a.html
如果是一个字段重复,例如ID.
select * from tb t where id in (select id from tb group by id having count(1) > 1)如果是多个字段重复,例如ID1,ID2
select * from tb t where exists (select 1 from (select id1,id2 from tb group by id1,id2 having count(1) > 1) n where t.id1 = n.id1 and t.id2 = n.id2)
group by columns1,columns2 having(*)>1
select * from tb
group by tid
having count(*)>1