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

2017-05-18 09:32:48 2.5k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

用STM32L552驱动AD9102：一个硬件工程师的实战避坑指南（附完整代码）本文详细介绍了如何使用STM32L552微控制器驱动AD9102任意波形发生器芯片，从硬件设计到波形生成的完整实战指南。内容包括电源架构、时钟电路设计、输出调理电路等关键硬件设计要点，以及软件驱动架构、波形存储优化和典型波形生成实例。通过实战经验分享，帮助硬件工程师避开常见设计陷阱，提升开发效率。阅读详情

1图。这个是的基础数据

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

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

想实现第三个结果，改怎么写啊。

...全文

5个必知的脑网络图论指标：从聚类系数到小世界特性（附BCT工具包使用指南）本文深度解析脑网络图论分析中五个核心指标：聚类系数、特征路径长度、全局效率、局部效率及节点效率，并详解其计算与解读。结合Brain Connectivity Toolbox工具包，提供从实战计算、常见报错解决到小世界特性统计检验的完整指南，帮助研究者跨越理论与实操鸿沟，让连接矩阵数据真正服务于神经科学研究。阅读详情

年轻人阿乐

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

顺势而为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

学海无涯-回头是岸 2017-05-18

打赏
举报

引用 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

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

相关推荐