怎么提取出Upper_id字段有重复的记录？
怎么提取出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(潇洒老乌龟) (