Question

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

表a：姓名，主要地址，次要地址，次次要地址，次次次要地址
姓名唯一，每个人的4个地址肯定都不同
表a
name adr1 adr2 adr3 adr4
王 北京 上海 广州 南京
李 济南 天津 上海 合肥
张 广州 厦门 南京 北京
刘 广州 武汉 沈阳 天津
吴 济南 杭州 重庆 长春
赵 无锡 上海 厦门 北京
钱 南京 上海 广州 北京
孙 合肥 济南 上海 杭州
1.求所有重复地址>=2个城市的2人组合的SQL语句，按重复城市个数从高到低排序。
结果类似下面这样：
王 钱 北京 上海 广州 南京
。。。
王 张 北京 广州 南京
。。。
李 孙 济南 上海 合肥
2.求地址两两重复地址>=3个城市的3人组合（不是要求3人地址都同，是其中两两之间有重叠城市）的SQL语句，按重复城市个数从高到低排序。
结果类似下面这样：
王 张 赵 北京 上海 南京 广州 厦门
。。。

Answer 1

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<a2.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<a2.name and a2.name<a3.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

追问

请问语句里面a是我的那张表吗，还有a1和a2是什么呢

追答

....
from a a1,a a2
....

a是你的表名，a1,a2是别名

追问

我用的是sqlite，不知道是中文支持问题还是什么，T.samevalue显示都是空的，能不能把T.samevalue拆成多个字段来显示，一个城市一个字段，谢谢，两个问题分都给你

追答

2 的话做起来很繁琐，思路上跟1一样，不过1最多只有4个值，2有12个并且还有重复，这边篇幅问题，写不下了

1
select
T.name1, T.name2, T.samevalue1, T.samevalue2, T.samevalue3, T.samevalue4 into #tempfrom( 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 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=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.name1order by T.samenum desc
update #temp
set samevalue3=samevalue4, samevalue4=''where samevalue3='' and samevalue4''
update #temp
set samevalue2=samevalue3, samevalue3=samevalue4, samevalue4=''where samevalue2='' and samevalue3''
update #temp
set samevalue1=samevalue2, samevalue2=samevalue3, samevalue3=samevalue4, samevalue4=''where samevalue1='' and samevalue2''
select * from #temp

Answer 2

真心复杂！求高手