一种思路:
select * from zd_base a inner join zd_base b on a.xianlu=b.xianlu and a.uid!=b.uid
这样两个pbqd就在一行里了
再用substring把pbqd按逗号分成多列
然后查询用where条件排列组合pbqd,如where a.pbqd1=b.pbqd1 or a.pbqd1=b.pbqd2 or a.pbqd2=b.pbqd1........
WITH cte1
AS
(
SELECT 1 uid,'C1' xianlu,'寰宇机械厂站,西直门站,' pbqd
UNION ALL
SELECT 2 uid,'C1' xianlu,'寰宇机械厂站,北京站,' pbqd
UNION ALL
SELECT 3 uid,'C1' xianlu,'苏州街站,望京站,' pbqd
UNION ALL
SELECT 3 uid,'C2' xianlu,'苏州街站,望京站,' pbqd
),
cte2
AS
(
SELECT cte1.uid,pbqd,xianlu,CHARINDEX(',',cte1.pbqd) STA,CHARINDEX(',',cte1.pbqd)-1 LENS FROM cte1
UNION ALL
SELECT uid,pbqd,xianlu,CHARINDEX(',',pbqd,STA+1) STA,CHARINDEX(',',pbqd,STA+1)-STA-1 LENS FROM cte2 WHERE STA<>0
),
cte3
AS
(
SELECT uid,xianlu,SUBSTRING(cte2.pbqd,STA-LENS,LENS) as pbqdnew,pbqd FROM cte2 WHERE STA<>0
)
--SELECT * FROM testtb2
SELECT DISTINCT cte3A.* FROM cte3 cte3A
INNER JOIN cte3 cte3B ON cte3A.uid<>cte3B.uid AND cte3B.xianlu = cte3A.xianlu AND cte3B.pbqdnew = cte3A.pbqdnew
他应该大致是这个意思