---------------------------------
--  Author: HEROWANG(让你望见影子的墙)
--  Date  : 2012-10-29 08:50:27
--  blog  : blog.csdn.net/herowang
---------------------------------
 
IF OBJECT_ID('[tc]') IS NOT NULL 
    DROP TABLE [tc]
go
CREATE TABLE [tc] (col1 VARCHAR(1),col2 VARCHAR(1),col3 VARCHAR(1),col4 VARCHAR(1))
INSERT INTO [tc]
SELECT 'A','B','C','D' UNION ALL
SELECT 'A','A','C','F' UNION ALL
SELECT 'B','C','D','A' UNION ALL
SELECT 'A','E','C','D' UNION ALL
SELECT 'B','C','A','F' UNION ALL
SELECT '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.colid
group by s.px,t.px
having count(*)>2)
select s.col1,s.col2,s.col3,s.col4 from wang3 join wang s on wang3.px1=s.px
union 
select t.col1,t.col2,t.col3,t.col4 from wang3 join wang t on wang3.px1=t.px
union 
select s.col1,s.col2,s.col3,s.col4 from wang3 join wang s on wang3.px2=s.px
union 
select t.col1,t.col2,t.col3,t.col4 from wang3 join wang t on wang3.px2=t.px

 A    B    C    D
A    E    C    D
B    C    A    F
B    C    C    F

------解决方案--------------------

借用影子哥的代码
SQL code
IF OBJECT_ID('[tc]') IS NOT NULL 
    DROP TABLE [tc]
go
CREATE TABLE [tc] (col1 VARCHAR(1),col2 VARCHAR(1),col3 VARCHAR(1),col4 VARCHAR(1))
INSERT INTO [tc]
SELECT 'A','B','C','D' UNION ALL
SELECT 'A','A','C','F' UNION ALL
SELECT 'B','C','D','A' UNION ALL
SELECT 'A','E','C','D' UNION ALL
SELECT 'B','C','A','F' UNION ALL
SELECT '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    col4
A    E    C    D
A    B    C    D
B    C    C    F
B    C    A    F