两个字段中，“熟人”连线问题，我觉得好难

Question

if object_id('[t4]') is not null drop table [t4] 
 go 
create table [t4]([x] varchar(10),[y] varchar(10))
insert [t4] select 'A','B'
union all select 'E','B'
union all select 'C','F'
union all select 'E','C'
union all select 'A','G'
union all select 'B','C'
union all select 'G','F'
union all select 'F','E'
go
;with t as
(
    select x,y,p=cast(x+'-'+y as varchar(8000)) 
    from t4 
    where [x]='A'
    union all
    select a.x,b.y,p=a.p+'-'+b.y 
    from t a,t4 b 
    where a.y=b.x 
      and a.y<>'C'
      and charindex(b.y,a.p)=0
)
select* from t where y='C' order by 3
/*
x          y          p
---------- ---------- ---------------
A          C          A-B-C
A          C          A-G-F-E-B-C
A          C          A-G-F-E-C(3 行受影响)
*/好像只能这样认识吧

Answer 1

if object_id('[t4]') is not null drop table [t4] 
 go 
create table [t4]([x] varchar(10),[y] varchar(10))
insert [t4] select 'A','B'
union all select 'E','B'
union all select 'C','F'
union all select 'E','C'
union all select 'A','G'
union all select 'B','C'
union all select 'G','F'
union all select 'F','E'
go
;with t as
(
    select x,y,p=cast(x+'-'+y as varchar(8000)) 
    from t4 
    where [x]='A'
    union all
    select a.x,b.y,p=a.p+'-'+b.y 
    from t a,t4 b 
    where (a.y=b.x or a.x=b.x)
      and a.y<>'C'
      and charindex(b.y,a.p)=0
)
select distinct * from t where y='C'
/*
x          y          p
---------- ---------- --------------------
A          C          A-B-C
A          C          A-B-G-F-E-C
A          C          A-G-B-C
A          C          A-G-F-B-C
A          C          A-G-F-E-B-C
A          C          A-G-F-E-C(6 行受影响)
*/modify

Answer 2

if object_id('[t4]') is not null drop table [t4] 
 go 
create table [t4]([x] varchar(10),[y] varchar(10))
insert [t4] select 'A','B'
union all select 'E','B'
union all select 'C','F'
union all select 'E','C'
union all select 'A','G'
union all select 'B','C'
union all select 'G','F'
union all select 'F','E'
go
;with t as
(
  select x,y,p=cast(x+'-'+y as varchar(8000)) from t4 where [x]='A'
  union all
  select a.x,b.y,p=a.p+'-'+b.y from t a,t4 b where charindex(b.y,a.p)=0
)
select distinct * from t where y='C'
/*
x          y          p
---------- ---------- -------------------
A          C          A-B-C
A          C          A-B-E-C
A          C          A-B-E-F-C
A          C          A-B-E-F-G-C
A          C          A-B-E-G-C
A          C          A-B-E-G-F-C
A          C          A-B-F-C
A          C          A-B-F-E-C
A          C          A-B-F-E-G-C
A          C          A-B-F-G-C
A          C          A-B-F-G-E-C
A          C          A-B-G-C
A          C          A-B-G-E-C
A          C          A-B-G-E-F-C
A          C          A-B-G-F-C
A          C          A-B-G-F-E-C
A          C          A-G-B-C
A          C          A-G-B-E-C
A          C          A-G-B-E-F-C
A          C          A-G-B-F-C
A          C          A-G-B-F-E-C
A          C          A-G-C
A          C          A-G-E-B-C
A          C          A-G-E-B-F-C
A          C          A-G-E-C
A          C          A-G-E-F-B-C
A          C          A-G-E-F-C
A          C          A-G-F-B-C
A          C          A-G-F-B-E-C
A          C          A-G-F-C
A          C          A-G-F-E-B-Ca
A          C          A-G-F-E-C(32 行受影响)
*/

Answer 3

DECLARE @TB TABLE([x] VARCHAR(1), [y] VARCHAR(1), [id] INT IDENTITY(1,1))
INSERT @TB 
SELECT 'A', 'B' UNION ALL 
SELECT 'E', 'B' UNION ALL 
SELECT 'C', 'F' UNION ALL 
SELECT 'E', 'C' UNION ALL 
SELECT 'A', 'G' UNION ALL 
SELECT 'B', 'C' UNION ALL 
SELECT 'G', 'F' UNION ALL 
SELECT 'F', 'E';WITH CTE AS
(
SELECT x,
   y,
   CAST(x+'-'+y AS VARCHAR(8000)) AS COMP,
   CAST(RTRIM(ID) AS VARCHAR(8000)) AS PATH
FROM @TB 
WHERE x='A'
UNION ALL
SELECT CASE WHEN A.x=C.y THEN A.x ELSE A.y END AS x,
   CASE WHEN A.x=C.y THEN A.y ELSE A.x END AS y,
   COMP+'-'+CASE WHEN A.x=C.y THEN A.y ELSE A.x END, 
   PATH+'-'+RTRIM(A.ID)
FROM @TB AS A,CTE AS C 
WHERE (A.x=C.y OR A.y=C.y) AND CHARINDEX('C', COMP)=0 AND CHARINDEX('-'+RTRIM(ID)+'-', '-'+PATH+'-')=0
)SELECT COMP 
FROM CTE 
WHERE RIGHT(COMP,1)='C' 
ORDER BY LEN(COMP)
/*
COMP
--------------------------------------------------------------------------------------
A-B-C
A-B-E-C
A-G-F-C
A-G-F-E-C
A-B-E-F-C
A-G-F-E-B-C(6 row(s) affected)
*/

Answer 4

谢谢 htl258 和 csdyyr，with 这样的语法我还是第一次接触，呵呵，但我在运行语句时为什么会提示“在关键字 'WITH' 附近有语法错误。”呢？

Answer 5

是的，我用的是 ms sql 2000，不支持这个语法吗？

Answer 6

DECLARE @TB TABLE([x] VARCHAR(1), [y] VARCHAR(1), [id] INT IDENTITY(1,1))
INSERT @TB 
SELECT 'A', 'B' UNION ALL 
SELECT 'E', 'B' UNION ALL 
SELECT 'C', 'F' UNION ALL 
SELECT 'E', 'C' UNION ALL 
SELECT 'A', 'G' UNION ALL 
SELECT 'B', 'C' UNION ALL 
SELECT 'G', 'F' UNION ALL 
SELECT 'F', 'E'--SQL 2000
DECLARE @TC TABLE([x] VARCHAR(1), [y] VARCHAR(1), COMP VARCHAR(8000), PATH VARCHAR(8000), LVL INT)
DECLARE @LVL INTSET @LVL=1
INSERT @TC
SELECT x,y,x+'-'+y AS COMP,CAST(RTRIM(ID) AS VARCHAR(8000)) AS PATH,@LVL FROM @TB WHERE x='A'WHILE @@ROWCOUNT>0
BEGIN
  SET @LVL=@LVL+1
  INSERT @TC
  SELECT CASE WHEN A.x=C.y THEN A.x ELSE A.y END AS x,
   CASE WHEN A.x=C.y THEN A.y ELSE A.x END AS y,
   COMP+'-'+CASE WHEN A.x=C.y THEN A.y ELSE A.x END,
   PATH+'-'+RTRIM(A.ID),
       @LVL
  FROM @TB AS A,@TC AS C 
  WHERE (A.x=C.y OR A.y=C.y) AND CHARINDEX('C', COMP)=0 AND CHARINDEX('-'+RTRIM(A.ID)+'-', '-'+PATH+'-')=0
    AND LVL=@LVL-1
END
SELECT COMP 
FROM @TC
WHERE RIGHT(COMP,1)='C' 
ORDER BY LEN(COMP)
/*
--SQL 2005
;WITH CTE AS
(
SELECT x,
   y,
   CAST(x+'-'+y AS VARCHAR(8000)) AS COMP,
   CAST(RTRIM(ID) AS VARCHAR(8000)) AS PATH
FROM @TB 
WHERE x='A'
UNION ALL
SELECT CASE WHEN A.x=C.y THEN A.x ELSE A.y END AS x,
   CASE WHEN A.x=C.y THEN A.y ELSE A.x END AS y,
   COMP+'-'+CASE WHEN A.x=C.y THEN A.y ELSE A.x END, 
   PATH+'-'+RTRIM(A.ID)
FROM @TB AS A,CTE AS C 
WHERE (A.x=C.y OR A.y=C.y) AND CHARINDEX('C', COMP)=0 AND CHARINDEX('-'+RTRIM(ID)+'-', '-'+PATH+'-')=0
)SELECT COMP 
FROM CTE 
WHERE RIGHT(COMP,1)='C' 
ORDER BY LEN(COMP)
*/
/*
COMP
--------------------------------------------------------------------------------------
A-B-C
A-B-E-C
A-G-F-C
A-G-F-E-C
A-B-E-F-C
A-G-F-E-B-C(6 row(s) affected)
*/