导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

请问: 如何在数据库里显示重复的字段数据

ahuiok 重庆科技学院 2003-12-16 01:16:12
如: 一张表结构和数据如下:


id num
1 3
2 3
3 4
4 6
5 7
6 8
7 9
8 8
9 8

显示的结果是

id num
1 3
2 3
6 8
8 8
9 8
...全文
7 点赞 收藏 13
写回复
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
xmchw 2003-12-16
up
回复
cloudchen 2003-12-16
select * from #temp where num in (select num from #temp group by num having count(num) > 1)
回复
--测试
declare @t table(id int,num int)
insert into @t
select 1,3
union all select 2,3
union all select 3,4
union all select 4,6
union all select 5,7
union all select 6,8
union all select 7,9
union all select 8,8
union all select 9,8

--查询
select * from @t a
where exists(select 1 from @t where num=a.num and id<>a.id)

/*--结果
id num
----------- -----------
1 3
2 3
6 8
8 8
9 8

(所影响的行数为 5 行)

--*/
回复
select * from table1 a
where exists(select 1 from table1 where num=a.num and id<>a.id)
回复
goodluck001 2003-12-16
select * from table1
where num in
(select num from table1 group by num having sum(1)>1)
回复
victorycyz 2003-12-16
不明白。
回复
dlpseeyou 2003-12-16
select id=max(id),num from table1 group by num having sum(1)>1
回复
wzh1215 2003-12-16
select * from table1 where num in(select num from table group by num having count(num)>1)
回复
1ssp 2003-12-16
select * from table1 where num in(select num from table group by num having count(num)>1)
回复
ivy_ou 2003-12-16
delete
from tablename temp1
where id <> (select max(id) from tablename temp2 where temp1.num = temp2.num)

删除表中的重复数据,保留重复数据id最大的记录
回复
ahuiok 2003-12-16
能不能自动删除一行num相同的数据??

回复
ahuiok 2003-12-16
看不太懂, 能再解释一下么?
回复
cysh 2003-12-16
select a.id,x.num
from table1 as a left outer join (select num
from table1
group by num
having count(*)<>1) as x
on a.num=x.num
where x.num is not null
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告