日期:2014-05-17 浏览次数:20540 次
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
GO
create table [TB]([f1] varchar(1),[f2] varchar(1))
insert [TB]
select 'A','B' union all
select 'A','F' union all
select 'A','G' union all
select 'B','C' union all
select 'B','G' union all
select 'B','H' union all
select 'B','A' union all
select 'C','B' union all
select 'C','H' union all
select 'C','I' union all
select 'C','D' union all
select 'D','C' union all
select 'D','I' union all
select 'D','J' union all
select 'E','J' union all
select 'E','D' union all
select 'F','A' union all
select 'F','G' union all
select 'G','A' union all
select 'G','B' union all
select 'G','H' union all
select 'G','F' union all
select 'H','G' union all
select 'H','B' union all
select 'H','C' union all
select 'H','I' union all
select 'I','H' union all
select 'I','C' union all
select 'I','D' union all
select 'I','F' union all
select 'F','I' union all
select 'F','D' union all
select 'F','E'
DECLARE @start VARCHAR(10),@end VARCHAR(10)
SET @start='A'
SET @end='I'
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp
CREATE TABLE #temp(
way VARCHAR(MAX),
f1 VARCHAR(10),
f2 VARCHAR(10),
flag int)
INSERT INTO #temp
SELECT ','+f1+','+f2,f1,f2,0 FROM tb WHERE f1=@start
WHILE (SELECT COUNT(1) FROM #temp WHERE flag=0)>0
BEGIN
DECLARE @i VARCHAR(10),@j VARCHAR(MAX)
SELECT TOP 1 @i=f2,@j=way FROM #temp WHERE flag=0
UPDATE #temp SET flag = 1 WHERE f2=@i
INSERT INTO #temp
SELECT @j+','+f2,f1,f2,0 FROM TB t WHERE f1=@i AND f2 NOT IN (SELECT f1 FROM #temp)
END
SELECT STUFF(way,1,1,'') FROM #temp WHERE RIGHT(way,LEN(@end))=@end
/*
A,F,I
A,B,C,I
A,B,H,I
*/
DROP TABLE #temp
drop table [TB]
USE test
GO
-->生成表t1
if object_id('t1') is not null
drop table t1
Go
Create table t1([f1] nvarchar(1),[f