求一条查询字段值相同的记录语句，谢谢！

一个表中，有固定的4个字段，大家看数据：

字段1 字段2 字段3 字段4
A B C D
A A C F
B C D A
A E C D
B C A F
B C C F


我要求的结果为：
字段1 字段2 字段3 字段4 字段5
A B C D 1
A E C D 1
B C A F 2
B C C F 2


这四条记录就是我想要的：前两条记录的字段1、字段3、字段4的值都相同，我取出来；后两条记录的字段1、字段2、字段4的值都相同，我取出来。
也就是说，字段1、字段2、字段3、字段4中，其中任何3个字段值都相同的记录我都取出来，并且相同的分为一组。

不晓得难度如何，觉得分不够，我再加就是哈，呵呵！


[解决办法]

SQL code

-----------------------------------  Author: HEROWANG(让你望见影子的墙)--  Date  : 2012-10-29 08:50:27--  blog  : blog.csdn.net/herowang--------------------------------- IF OBJECT_ID('[tc]') IS NOT NULL     DROP TABLE [tc]goCREATE TABLE [tc] (col1 VARCHAR(1),col2 VARCHAR(1),col3 VARCHAR(1),col4 VARCHAR(1))INSERT INTO [tc]SELECT 'A','B','C','D' UNION ALLSELECT 'A','A','C','F' UNION ALLSELECT 'B','C','D','A' UNION ALLSELECT 'A','E','C','D' UNION ALLSELECT 'B','C','A','F' UNION ALLSELECT 'B','C','C','F'select * from [tc];with wang as (select px=row_number() over (order by getdate()),* from tc),wang2 as (select px,col=col1,colid=1 from wang union all           select px,col2,2 from wang union all          select px,col3,3 from wang union all          select px,col4,4 from wang ),wang3 as(select px1=s.px,px2=t.px from wang2 s join wang2 t on s.col =t.col and s.px<t.px and s.colid=t.colidgroup by s.px,t.pxhaving count(*)>2)select s.col1,s.col2,s.col3,s.col4 from wang3 join wang s on wang3.px1=s.pxunion select t.col1,t.col2,t.col3,t.col4 from wang3 join wang t on wang3.px1=t.pxunion select s.col1,s.col2,s.col3,s.col4 from wang3 join wang s on wang3.px2=s.pxunion select t.col1,t.col2,t.col3,t.col4 from wang3 join wang t on wang3.px2=t.px A    B    C    DA    E    C    DB    C    A    FB    C    C    F
[解决办法]
借用影子哥的代码
SQL code
IF OBJECT_ID('[tc]') IS NOT NULL     DROP TABLE [tc]goCREATE TABLE [tc] (col1 VARCHAR(1),col2 VARCHAR(1),col3 VARCHAR(1),col4 VARCHAR(1))INSERT INTO [tc]SELECT 'A','B','C','D' UNION ALLSELECT 'A','A','C','F' UNION ALLSELECT 'B','C','D','A' UNION ALLSELECT 'A','E','C','D' UNION ALLSELECT 'B','C','A','F' UNION ALLSELECT 'B','C','C','F' ;with cte as (select id=row_number() over (order by getdate()),* from tc ) select  a.col1,a.col2,a.col3,a.col4  from cte a, cte b where a.id <> b.id and case when a.col1 = b.col1 then 1 else 0 end    +  case when a.col2 = b.col2 then 1 else 0 end    +  case when a.col3 = b.col3 then 1 else 0 end    +  case when a.col4 = b.col4 then 1 else 0 end    >= 3-- 结果col1    col2    col3    col4A    E    C    DA    B    C    DB    C    C    FB    C    A    F