34,575
社区成员
发帖
与我相关
我的任务
分享
Select *
From test a
Where Exists (
Select *
From (
select name,age,ad
from test group by name,age,ad
having count(*)>1
)b
where a.name=b.name and a.age=b.age and a.ad=b.ad
)
With T1(id,name,age,ad ) as
(
select 1,'张三',20,170 union all
select 2,'李四',24,172 union all
select 3,'张三',20,170 union all
select 4,'李小四',25,173 union all
select 5,'张小',21,171 union all
select 6,'李四',24,172 union all
select 7,'李四',24,110
)
select * from T1 a
where exists (select 1 from T1 where name=a.name and age=a.age and ad=a.ad group by name,age,ad having count(1)>1)
if object_id('test') is not null
drop table test
go
create table test(id int,name varchar(128),age int,ad int)
go
insert into test
select 1,'张三',20,170 union all
select 2,'李四',24,172 union all
select 3,'张三',20,170 union all
select 4,'李小四',25,173 union all
select 5,'张小',21,171 union all
select 6,'李四',24,172 union all
select 7,'李四',24,110
go
;with cte as
(
select name,age,ad
from test group by name,age,ad
having count(*)>1
)
select t.*
from cte c
left join test t
on c.name = t.name
and c.age = t.age
and c.ad = t.ad
order by id
--测试数据
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
CREATE TABLE #T1(id int,name varchar(128),age int,ad int)
go
INSERT INTO #T1
SELECT 1,'张三',20,170 union all
SELECT 2,'李四',24,172 union all
SELECT 3,'张三',20,170 union all
SELECT 4,'李小四',25,173 union all
SELECT 5,'张小',21,171 union all
SELECT 6,'李四',24,172 union all
SELECT 7,'李四',24,110
Go
--测试数据结束
SELECT #T1.*
FROM #T1
JOIN ( SELECT name ,
age ,
ad
FROM #T1
GROUP BY name ,
age ,
ad
HAVING COUNT(*) > 1
) t ON t.name = #T1.name
AND t.ad = #T1.ad
AND t.age = #T1.age
ORDER BY #T1.id