各位大哥帮帮忙看看，SQL不知道怎么实现

[zmld]

这个图1


如何实现像图2这样




例如seq_id 1的最后一个sort_seq5--task_next【401】 有1标识，会把标识带给task_code=【401】，如此类推，task_next--》task_code

这个怎么写，我只会写到【406】，【406--》402】再往下实现不了

	update @rs set lx=b.lx,xs=b.xs,qe=b.qe from @rs a,               
       (select a.route_id,
               case when sum(isnull(lx,0))>0 then 1 else 0 end as lx,
               case when sum(isnull(xs,0))>0 then 1 else 0 end as xs,
               case when sum(isnull(qe,0))>0 then 1 else 0 end as qe
       from @rs a
       inner join (select distinct route_id,task_code,item_code from @rs)b on a.route_id=b.route_id and a.task_next=b.task_code
       inner join (select distinct route_id,item_code from @rs)c on a.route_id=c.route_id and b.item_code=c.item_code       
       where a.route_id=@route_id
       group by a.route_id
      )b where a.route_id=b.route_id and a.task_next='406' 

[Dear SQL(燊)]

没有明的 seq_id 的关系，猜的

with list as(
	select seq_id=1,task_code='101',task_next='102',lx=null union all
	select seq_id=1,task_code='102',task_next='103',lx=1 union all
	select seq_id=1,task_code='103',task_next='301',lx=1 union all
	select seq_id=1,task_code='302',task_next='401',lx=1 union all
	select seq_id=1,task_code='301',task_next='302',lx=1 union all
	select seq_id=2,task_code='301',task_next='302',lx=null union all
	select seq_id=2,task_code='302',task_next='401',lx=null union all
	select seq_id=2,task_code='103',task_next='301',lx=null union all
	select seq_id=2,task_code='101',task_next='103',lx=null union all
	select seq_id=3,task_code='401',task_next='406',lx=null union all
	select seq_id=3,task_code='402',task_next='407',lx=null union all
	select seq_id=3,task_code='406',task_next='402',lx=null union all
	select seq_id=3,task_code='407',task_next='',lx=null 
	
),datas as(
	select seq_id,task_code,task_next,lx
	from list
	where task_next not in(select task_code from list where seq_id=1)
		and seq_id=1 /*seq_id=1 的最后一级*/
	union all
	select a.seq_id,a.task_code,a.task_next,lx=case when b.lx=1 then 1 else a.lx end
	from list a
	inner join datas b on a.task_code=b.task_next
)
select *
from datas

seq_id      task_code task_next lx
----------- --------- --------- -----------
1           302       401       1
3           401       406       1
3           406       402       1
3           402       407       1
3           407                 1

[RINK_1]

IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T
(ID VARCHAR(10),
 SEQ_ID VARCHAR(5),
 SORT_SEQ VARCHAR(5),
 DP_CODE VARCHAR(10),
 TASK_CODE VARCHAR(10),
 TASK_NEXT VARCHAR(10),
 LX VARCHAR(5))

INSERT INTO #T
SELECT '85231','1','1','WIP1011','101','102','' UNION ALL
SELECT '85231','1','2','WIP1021','102','103','1' UNION ALL
SELECT '85231','1','3','WIP1031','103','301','1' UNION ALL
SELECT '85231','1','5','WIP3021','302','401','1' UNION ALL
SELECT '85231','1','4','WIP3011','301','302','1' UNION ALL
SELECT '85231','2','3','WIP3012','301','302','' UNION ALL
SELECT '85231','2','4','WIP3022','302','401','' UNION ALL
SELECT '85231','2','2','WIP1032','103','301','' UNION ALL
SELECT '85231','2','1','WIP1012','101','103','' UNION ALL
SELECT '85231','3','1','WIP4013','401','406','1' UNION ALL
SELECT '85231','3','3','WIP4023','402','407','' UNION ALL
SELECT '85231','3','2','WIP4063','406','402','' UNION ALL
SELECT '85231','3','4','WIP4073','407','','' 

;WITH CTE_1
AS
(SELECT *,COUNT(*) OVER(PARTITION BY SEQ_ID) AS SUB_TOTAL,
 ROW_NUMBER() OVER (ORDER BY SEQ_ID,SORT_SEQ) AS SEQ FROM #T),

CTE_2
AS
(SELECT A.*,A.LX AS TOP_LX FROM CTE_1 A
 WHERE ISNULL(LX,'')<>''
 AND NOT EXISTS (SELECT 1 FROM CTE_1 WHERE SEQ<A.SEQ AND ISNULL(LX,'')<>'')
 UNION ALL
 SELECT B.*,C.TOP_LX 
 FROM CTE_1 B
 JOIN CTE_2 C ON (C.SUB_TOTAL<>C.SORT_SEQ AND C.TASK_NEXT=B.TASK_CODE AND C.SEQ_ID=B.SEQ_ID) 
                  OR (C.SUB_TOTAL=C.SORT_SEQ AND C.TASK_NEXT=B.TASK_CODE AND C.SEQ<>B.SEQ_ID))

 SELECT * FROM CTE_2

[zmld]

谢谢大家，我用的还是SQL2000，语法和2005以上的不同

[Dear SQL(燊)]

引用 3 楼 zmld 的回复:

谢谢大家，我用的还是SQL2000，语法和2005以上的不同

出来的数据对不对？如果对可以改成while循环

[zmld]

没有2005的数据库，没得测试，现在用2000调试