一道SQL题目，一张表，求两条SQL语句

mssql2008下测试通过

1

select T.name1,
T.name2,
T.samevalue
from
(
select a1.name name1,
a2.name name2,
case when a1.adr1=a2.adr1 or
a1.adr1=a2.adr2 or
a1.adr1=a2.adr3 or
a1.adr1=a2.adr4 then a1.adr1+ ' ' else '' end +
case when a1.adr2=a2.adr1 or
a1.adr2=a2.adr2 or
a1.adr2=a2.adr3 or
a1.adr2=a2.adr4 then a1.adr2+ ' ' else '' end +
case when a1.adr3=a2.adr1 or
a1.adr3=a2.adr2 or
a1.adr3=a2.adr3 or
a1.adr3=a2.adr4 then a1.adr3+ ' ' else '' end +
case when a1.adr4=a2.adr1 or
a1.adr4=a2.adr2 or
a1.adr4=a2.adr3 or
a1.adr4=a2.adr4 then a1.adr4 else '' end samevalue,
case when a1.adr1=a2.adr1 or
a1.adr1=a2.adr2 or
a1.adr1=a2.adr3 or
a1.adr1=a2.adr4 then 1 else 0 end +
case when a1.adr2=a2.adr1 or
a1.adr2=a2.adr2 or
a1.adr2=a2.adr3 or
a1.adr2=a2.adr4 then 1 else 0 end +
case when a1.adr3=a2.adr1 or
a1.adr3=a2.adr2 or
a1.adr3=a2.adr3 or
a1.adr3=a2.adr4 then 1 else 0 end +
case when a1.adr4=a2.adr1 or
a1.adr4=a2.adr2 or
a1.adr4=a2.adr3 or
a1.adr4=a2.adr4 then 1 else 0 end samenum
from a a1,a a2
where a1.name)T
where samenum>1
order by samenum desc,samevalue

2
drop table #temp
select
T.*
into #temp
from
(
select a1.name name1,
a2.name name2,
a3.name name3,
case when a1.adr1=a2.adr1 or
a1.adr1=a2.adr2 or
a1.adr1=a2.adr3 or
a1.adr1=a2.adr4 then a1.adr1 else '' end samevalue1,
case when a1.adr2=a2.adr1 or
a1.adr2=a2.adr2 or
a1.adr2=a2.adr3 or
a1.adr2=a2.adr4 then a1.adr2 else '' end samevalue2,
case when a1.adr3=a2.adr1 or
a1.adr3=a2.adr2 or
a1.adr3=a2.adr3 or
a1.adr3=a2.adr4 then a1.adr3 else '' end samevalue3,
case when a1.adr4=a2.adr1 or
a1.adr4=a2.adr2 or
a1.adr4=a2.adr3 or
a1.adr4=a2.adr4 then a1.adr4 else '' end samevalue4,

case when a1.adr1=a3.adr1 or
a1.adr1=a3.adr2 or
a1.adr1=a3.adr3 or
a1.adr1=a3.adr4 then a1.adr1 else '' end samevalue5,
case when a1.adr2=a3.adr1 or
a1.adr2=a3.adr2 or
a1.adr2=a3.adr3 or
a1.adr2=a3.adr4 then a1.adr2 else '' end samevalue6,
case when a1.adr3=a3.adr1 or
a1.adr3=a3.adr2 or
a1.adr3=a3.adr3 or
a1.adr3=a3.adr4 then a1.adr3 else '' end samevalue7,
case when a1.adr4=a3.adr1 or
a1.adr4=a3.adr2 or
a1.adr4=a3.adr3 or
a1.adr4=a3.adr4 then a1.adr4 else '' end samevalue8,

case when a3.adr1=a2.adr1 or
a3.adr1=a2.adr2 or
a3.adr1=a2.adr3 or
a3.adr1=a2.adr4 then a3.adr1 else '' end samevalue9,
case when a3.adr2=a2.adr1 or
a3.adr2=a2.adr2 or
a3.adr2=a2.adr3 or
a3.adr2=a2.adr4 then a3.adr2 else '' end samevalue10,
case when a3.adr3=a2.adr1 or
a3.adr3=a2.adr2 or
a3.adr3=a2.adr3 or
a3.adr3=a2.adr4 then a3.adr3 else '' end samevalue11,
case when a3.adr4=a2.adr1 or
a3.adr4=a2.adr2 or
a3.adr4=a2.adr3 or
a3.adr4=a2.adr4 then a3.adr4 else '' end samevalue12,

case when a1.adr1=a2.adr1 or
a1.adr1=a2.adr2 or
a1.adr1=a2.adr3 or
a1.adr1=a2.adr4 then 1 else 0 end +
case when a1.adr2=a2.adr1 or
a1.adr2=a2.adr2 or
a1.adr2=a2.adr3 or
a1.adr2=a2.adr4 then 1 else 0 end +
case when a1.adr3=a2.adr1 or
a1.adr3=a2.adr2 or
a1.adr3=a2.adr3 or
a1.adr3=a2.adr4 then 1 else 0 end +
case when a1.adr4=a2.adr1 or
a1.adr4=a2.adr2 or
a1.adr4=a2.adr3 or
a1.adr4=a2.adr4 then 1 else 0 end samenum1,

case when a1.adr1=a3.adr1 or
a1.adr1=a3.adr2 or
a1.adr1=a3.adr3 or
a1.adr1=a3.adr4 then 1 else 0 end +
case when a1.adr2=a3.adr1 or
a1.adr2=a3.adr2 or
a1.adr2=a3.adr3 or
a1.adr2=a3.adr4 then 1 else 0 end +
case when a1.adr3=a3.adr1 or
a1.adr3=a3.adr2 or
a1.adr3=a3.adr3 or
a1.adr3=a3.adr4 then 1 else 0 end +
case when a1.adr4=a3.adr1 or
a1.adr4=a3.adr2 or
a1.adr4=a3.adr3 or
a1.adr4=a3.adr4 then 1 else 0 end samenum2,

case when a3.adr1=a2.adr1 or
a3.adr1=a2.adr2 or
a3.adr1=a2.adr3 or
a3.adr1=a2.adr4 then 1 else 0 end +
case when a3.adr2=a2.adr1 or
a3.adr2=a2.adr2 or
a3.adr2=a2.adr3 or
a3.adr2=a2.adr4 then 1 else 0 end +
case when a3.adr3=a2.adr1 or
a3.adr3=a2.adr2 or
a3.adr3=a2.adr3 or
a3.adr3=a2.adr4 then 1 else 0 end +
case when a3.adr4=a2.adr1 or
a3.adr4=a2.adr2 or
a3.adr4=a2.adr3 or
a3.adr4=a2.adr4 then 1 else 0 end samenum3

from a a1,a a2,a a3
where a1.name)T
where samenum1>0 and samenum2>0 and samenum3>0

drop table #temp1
select * into #temp1
from
(
select name1,name2,name3,samevalue1 from #temp union
select name1,name2,name3,samevalue2 from #temp union
select name1,name2,name3,samevalue3 from #temp union
select name1,name2,name3,samevalue4 from #temp union
select name1,name2,name3,samevalue5 from #temp union
select name1,name2,name3,samevalue6 from #temp union
select name1,name2,name3,samevalue7 from #temp union
select name1,name2,name3,samevalue8 from #temp union
select name1,name2,name3,samevalue9 from #temp union
select name1,name2,name3,samevalue10 from #temp union
select name1,name2,name3,samevalue11 from #temp union
select name1,name2,name3,samevalue12 from #temp
)T
where samevalue1 <> ''

select *
from
(
select name1,name2,name3,
samevalue1=STUFF((select ' '+samevalue1 from #temp1 t where name1+name2+name3=#temp1.name1+#temp1.name2+#temp1.name3 for xml path('')), 1, 1, '')
from #temp1
where name1+name2+name3
in(
select name1+name2+name3
from #temp1
group by name1,name2,name3
having count(samevalue1)>2
)
group by name1,name2,name3
)T
order by LEN(T.samevalue1) desc

真心复杂！求高手