MSSQL 获取相同一个表中相同记录的方法

今天收到一个需求,需要找出进行多次收款的业务单号。
下面记录记录问题的解决办法:

1 建表,生成数据

   create table A(keyId int identity(1,1),billNo varchar(60),jinE money,writeDate datetime)
go
insert into A(billNo,jinE,writeDate)values('2007082101',10,GETDATE())
insert into A(billNo,jinE,writeDate)values('2007082102',85,GETDATE())
insert into A(billNo,jinE,writeDate)values('2007082103',65,GETDATE())
insert into A(billNo,jinE,writeDate)values('2007082104',75,GETDATE())
insert into A(billNo,jinE,writeDate)values('2007082105',88,GETDATE())
insert into A(billNo,jinE,writeDate)values('2007082103',92,GETDATE())
insert into A(billNo,jinE,writeDate)values('2007082104',33,GETDATE())
insert into A(billNo,jinE,writeDate)values('2007082103',37,GETDATE())
insert into A(billNo,jinE,writeDate)values('2007082106',45,GETDATE())
insert into A(billNo,jinE,writeDate)values('2007082107',58,GETDATE())
insert into A(billNo,jinE,writeDate)values('2007082102',90,GETDATE())
go

----方法1 根据单号进行分组,然后将在群组中的数据展示出来
select * from A where billNo in (
select billNo from A group by billNo having COUNT(billNo) >1
) order by billNo 

----方法2 根据表中唯一值,进行两张表对比,如果唯一值不同,并且业务单号相同,则作为重复数据展示出
select * from A as t1
where t1.billNo in (
select t2.billNo from A as t2 where t2.keyId <> t1.keyId
)
 order by billNo 

----方法3  遍历表A中的列billno的个数
select * from A as t1 where (
select COUNT(*) from A  as t2 
where t2.billNo =t1.billNo 
) >1 
order by billNo 


truncate table A 
drop table A 
go