有1张单据表， 我想找出单据中除了单号不同，其他信息完全相同的单。
例如以下表， 找出FID不同，但是fno完全相同，包括记录的条数也相同.查出的结果为3,5.
多谢！
create table #tt
(
fid int,
fno varchar(20)
)
go
insert into #tt
select 3, 'AA'
union all
select 3, 'BB'
union all
select 4, 'AA'
union all
select 4, 'BB'
union all
select 4, 'CC'
union all
select 5, 'AA'
union all
select 5, 'BB'
union all
select 6, 'AA'
union all
select 6, 'DD'
------解决方案--------------------
with TB as
(select fid,(select ','+fno from #tt where fid=a.fid order by fno for XML path('')) as fno from #tt as a
group by fid)
select fid
from TB
where fno in (select fno from TB group by fno having COUNT(1)>1)
------解决方案--------------------
给你百度了一条，不知道适合不适合：
查找表中多余的重复记录（多个字段）
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
------解决方案--------------------
with tb(fid,fno)
as(
select 3, 'AA'
union all
select 3, 'BB'
union all
select 4, 'AA'
union all
select 4, 'BB'
union all
select 4, 'CC'
union all
select 5, 'AA'
union all
select 5, 'BB'
union all
select 6, 'AA'
union all
select 6, 'DD'),
source as(
select fid,stuff((select ','+fno from tb tb2 where tb1.fid=tb2.fid order by tb2.fno for xml path('')),1,1,'') fno from tb tb1 group by fid)
select fid from source s1 where (select count(1) from source s2 where s1.fno=s2.fno)>1
------解决方案--------------------
declare @a table
( fid int, fno varchar(20) )
insert into @a select 3, 'AA'
union all select 3, 'BB'
union all select 4, 'AA'
union all select 4, 'BB'
union all select 4, 'CC'
union all select 5, 'AA'
union all select 5, 'BB'
union all select 6, 'AA'
union all select 6, 'DD'
select fid from (
select fid,count(fno) over(partition by fno)a from (
select distinct fid, stuff((select ','+fno from @a where fid=b.fid for xml path('') ),1,1,'')as fno from @a b)a