SQL查询多列重复数据显示。切像单条重复数据一样全部输出出来。

年轻人阿乐 2017-05-18 09:32:48
1图。这个是的基础数据

2图。是我查询的重复数据(但是不是我想要的)select name,age,ad from aa.dbo.ccc group by name,age,ad having count(*)>1

3图。是我想要的【所有的重复项都显示出来。而不是结果去重】

想实现第三个结果,改怎么写啊。
...全文
2422 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
顺势而为1 2017-05-18
  • 打赏
  • 举报
回复



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
			  )

0与1之间 2017-05-18
  • 打赏
  • 举报
回复
试试这个

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)
逍遥清风_Frank 2017-05-18
  • 打赏
  • 举报
回复
引用 2 楼 JiSuWangLuoSheJi 的回复:
[quote=引用 1 楼 qq_28581179 的回复:] 字段多的情况下考虑使用动态sql:
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
多谢哥哥。非常棒。对于初学者 哥哥有什么好的建议么[/quote] 我也是个初学者,看看书籍,泡泡CSDN进步挺快的
年轻人阿乐 2017-05-18
  • 打赏
  • 举报
回复
引用 1 楼 qq_28581179 的回复:
字段多的情况下考虑使用动态sql:
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
多谢哥哥。非常棒。对于初学者 哥哥有什么好的建议么
逍遥清风_Frank 2017-05-18
  • 打赏
  • 举报
回复
字段多的情况下考虑使用动态sql:

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

  • 打赏
  • 举报
回复
引用 4 楼 Merry0101 的回复:
试试这个

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)
这个很巧妙啊
RINK_1 2017-05-18
  • 打赏
  • 举报
回复
SELECT * FROM TABLE A WHERE EXISTS (SELECT 1 FROM TABLE WHERE ID<>A.ID AND NAME=A.NAME AND AGE=A.AGE AND AD=A.AD)
二月十六 版主 2017-05-18
  • 打赏
  • 举报
回复
楼主已经写完一大半了,做一个关联就可以查询出来了
--测试数据
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


34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧