CREATE TABLE [dbo].[t1](
[id] [int] NULL,
[code] [varchar](100) NULL,
[mobile] [varchar](100) NULL,
[email] [varchar](100) NULL,
[uniqueid] [uniqueidentifier] NULL
) ON [PRIMARY]
GO
INSERT INTO dbo.t1
( id, code, mobile, email )
VALUES (1,'a','138','126')
,(2,'b','139','qq')
,(3,'c','181','qq')
,(4,'d','138','163')
,(5,'e','139','126')
,(6,'f','137','163')
,(7,'g','161','126')
,(8,'h','139','qq')
,(9,'i','181','163')
,(10,'j','137','gmail')
,(11,'k','181','163')
,(12,'c','181','163')
,(13,'d','138','126')
,(14,'e','138','qq')
,(15,'f','181','163')
,(16,'g','138','163')
,(17,'h','139','126')
,(18,'a','188','hotmail')
以上是测试数据，希望可以根据手机号和email遍历，如 id=1 为起始 需要先遍历手机号或email相同的，然后根据匹配结果集 如此循环下去。
------解决思路----------------------
这个只能用临时表不能用CTE递归。表名t1我用了#t1。
SELECT *
INTO #t2
FROM #t1
WHERE id = 1
WHILE @@ROWCOUNT >0
BEGIN
INSERT INTO #t2
SELECT DISTINCT #t1.*
FROM #t2
JOIN #t1
ON #t1.id <> #t2.id
AND ( #t1.mobile = #t2.mobile
OR #t1.email = #t2.email
)
WHERE NOT EXISTS (SELECT *
FROM #t2 t
WHERE t.id = #t1.id)
END
SELECT *
FROM #t2
ORDER BY id
id code mobile email
----------- ---------- ---------- ----------
1 a 138 126
2 b 139 qq
3 c 181 qq
4 d 138 163
5 e 139 126
6 f 137 163
7 g 161 126
8 h 139 qq
9 i 181 163
10 j 137 gmail
11 k 181 163
12 c 181 163
13 d 138 126
14 e 138 qq
15 f 181 163
16 g 138 163
17 h 139 126