如果我有表
id name score
1 tom 3
2 cake 23
3 mulk 3
4 sherry 9
5 cake 56
如果我要列出这个表里面凡是名字有重复出现的记录,把明细给列出来。例如上表的结果是列出2 cake 23
5 cake 56
id name score
1 tom 3
2 cake 23
3 mulk 3
4 sherry 9
5 cake 56
如果我要列出这个表里面凡是名字有重复出现的记录,把明细给列出来。例如上表的结果是列出2 cake 23
5 cake 56
from tb t
where (select count(1) from tb where name=t.name)>1
go
create table [tb]([id] int,[name] varchar(6),[score] int)
insert [tb]
select 1,'tom',3 union all
select 2,'cake',23 union all
select 3,'mulk',3 union all
select 4,'sherry',9 union all
select 5,'cake',56
---查询
select *
from tb t
where (select count(1) from tb where name=t.name)>1/**
id name score
----------- ------ -----------
2 cake 23
5 cake 56(2 行受影响)
**/
SELECT * FROM TABLENAME ON NAME=CAKE;
*
from
tb
where
name in(select name from tb group by name having count(1)>1)
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(6),[score] int)
insert [tb]
select 1,'tom',3 union all
select 2,'cake',23 union all
select 3,'mulk',3 union all
select 4,'sherry',9 union all
select 5,'cake',56
select * from tb t where exists(select 1 from tb where name=t.name and id<>t.id)
/*
id name score
----------- ------ -----------
2 cake 23
5 cake 56(2 行受影响)
*/借四方城的表数据,避免重复建设
WHERE [name] IN
(
SELECT [name] FROM tb GROUP BY [name] HAVING COUNT(*) > 1
)