怎么提取出Upper_id字段有重复的记录？
例如
id Upper_id Name
1 1 a
2 1 b
3 2 c
4 2 d
5 3 e
6 4 f
7 2 g
用SQL语句来查询出来的
结果就是
1 1 a
2 1 b
3 2 c
4 2 d
7 2 g
------解决方案--------------------
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb
(
id varchar(10),
Upper_id varchar(10),
Name varchar(10)
)
insert into tb(id,Upper_id,Name) values( '1 ', '1 ', 'a ')
insert into tb(id,Upper_id,Name) values( '2 ', '1 ', 'b ')
insert into tb(id,Upper_id,Name) values( '3 ', '2 ', 'c ')
insert into tb(id,Upper_id,Name) values( '4 ', '2 ', 'd ')
insert into tb(id,Upper_id,Name) values( '5 ', '3 ', 'e ')
insert into tb(id,Upper_id,Name) values( '6 ', '4 ', 'f ')
insert into tb(id,Upper_id,Name) values( '7 ', '2 ', 'g ')
select * from tb where upper_id in
(select Upper_id from tb group by Upper_id having count(*) > 1)
drop table tb
id Upper_id Name
---------- ---------- ----------
1 1 a
2 1 b
3 2 c
4 2 d
7 2 g
（所影响的行数为 5 行）
------解决方案--------------------
--借用楼上数据，呵呵
if object_id( 'tb ') is not null
drop table tb
go
create table tb
(
id varchar(10),
Upper_id varchar(10),
Name varchar(10)
)
insert into tb(id,Upper_id,Name) values( '1 ', '1 ', 'a ')
insert into tb(id,Upper_id,Name) values( '2 ', '1 ', 'b ')
insert into tb(id,Upper_id,Name) values( '3 ', '2 ', 'c ')
insert into tb(id,Upper_id,Name) values( '4 ', '2 ', 'd ')
insert into tb(id,Upper_id,Name) values( '5 ', '3 ', 'e ')
insert into tb(id,Upper_id,Name) values( '6 ', '4 ', 'f ')
insert into tb(id,Upper_id,Name) values( '7 ', '2 ', 'g ')
select * from tb t where (select count(1) from tb where Upper_id=t.Upper_id)> 1
drop table tb
--
1 1 a
2 1 b
3 2 c
4 2 d
7 2 g
------解决方案--------------------
借dawugui(潇洒老乌龟) ( ) 信誉：100 ：）