请教1个 SQL 的写法！

[Vicar2]

有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
)a where a>1

[Vicar2]

多谢了，知道思路了，把除FID之外的其他字段GROUP BY FID到1个字段中。 不过单据中有几十个字段。得慢慢写了，多谢了！等下再来给分。