求一递归查询sql语句,该怎么处理

求一递归查询sql语句
处方表mz_brcf主键为hjdh,hjdh2表示来源处方号，如果是退方生成的处方hjdh2=原方的hjdh.1条记录可以多次生成退方。即hjdh2=hjdh的记录可能有多条。且生成的退方还能再生成它的退方。我想要的结果是输入一个hjdh，会显示出与它相关的记录，以及相关的相关的记录。。。
如
A00010000052或者输入TA00010000052、TTA00010000052、TTTA00010000052
都能够得到

SQL code


--hjdh,rq,hjdh2
A00010000052    2012-05-22 11:15:47.487        
TA00010000052    2012-05-22 11:39:33.673        A00010000052
TTA00010000052    2012-05-24 10:18:01.373        A00010000052
TTTA00010000052    2012-05-24 10:18:12.747        TA00010000052[code=SQL]
CREATE TABLE [dbo].[mz_brcf](
    [hjdh] [varchar](20) NOT NULL,
    [rq] [datetime] NOT NULL,
    [hjdh2] [varchar](20) NOT NULL,
 CONSTRAINT [PK_temp_mz_brcf] PRIMARY KEY CLUSTERED 
(
    [hjdh] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
go
--测试数据
insert   mz_brcf   (hjdh,rq,hjdh2)      values   (   'A00010000051','2012-05-22 11:15:13.593','')
insert  mz_brcf   (hjdh,rq,hjdh2)      values   (   'A00010000052','2012-05-22 11:15:47.487','')
insert   mz_brcf   (hjdh,rq,hjdh2)      values   (   'A00010000053','2012-05-22 11:16:19.033','')
insert   mz_brcf   (hjdh,rq,hjdh2)      values   (   'TA00010000052','2012-05-22 11:39:33.673','A00010000052')
insert   mz_brcf   (hjdh,rq,hjdh2)      values   (   'TTA00010000052','2012-05-24 10:18:01.373','A00010000052')
insert  mz_brcf   (hjdh,rq,hjdh2)      values   (   'TTTA00010000052','2012-05-24 10:18:12.747','TA00010000052')
go
--我写的递归查询，结果肯定是不对的
with mzhjdh as 
(select hjdh,rq,hjdh2
from mz_brcf
where hjdh='A00010000052'
union ALL
select hjdh,rq,hjdh2
from mz_brcf
where hjdh2='A00010000052'
union ALL
select t1.hjdh,t1.rq,t1.hjdh2
from mz_brcf t1
join mzhjdh as t2
on t1.hjdh2=t2.hjdh
)
select * from mzhjdh

------解决方案--------------------

SQL code

;with cte as(
 select * from mz_brcf where hjdh='A00010000052'
 union all
 select b.* from cte a join mz_brcf b on a.hjdh=b.hjdh2
)
select * from cte

------解决方案--------------------
DECLARE @hjdh VARCHAR(20)
SET @hjdh='TTTA00010000052'
select hjdh,rq,hjdh2 from mz_brcf where hjdh=@hjdh UNION
select b.hjdh,b.rq,b.hjdh2 from mz_brcf a,mz_brcf b where a.hjdh=@hjdh and b.hjdh=a.hjdh2 UNION
select c.hjdh,c.rq,c.hjdh2 from mz_brcf a,mz_brcf b,mz_brcf c  where a.hjdh=@hjdh and b.hjdh=a.hjdh2 and c.hjdh=b.hjdh2

------解决方案--------------------

SQL code




----hjdh,rq,hjdh2
--A00010000052    2012-05-22 11:15:47.487        
--TA00010000052    2012-05-22 11:39:33.673        A00010000052
--TTA00010000052    2012-05-24 10:18:01.373        A00010000052
--TTTA00010000052    2012-05-24 10:18:12.747        TA00010000052


CREATE TABLE [dbo].[mz_brcf](
    [hjdh] [varchar](20) NOT NULL,
    [rq] [datetime] NOT NULL,
    [hjdh2] [varchar](20) NOT NULL,
 CONSTRAINT [PK_temp_mz_brcf] PRIMARY KEY CLUSTERED 
(
    [hjdh] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
go
--测试数据
insert   mz_brcf   (hjdh,rq,hjdh2)      values   (   'A00010000051','2012-05-22 11:15:13.593','')
insert  mz_brcf   (hjdh,rq,hjdh2)      values   (   'A00010000052','2012-05-22 11:15:47.487','')
insert   mz_brcf   (hjdh,rq,hjdh2)      values   (   'A00010000053','2012-05-22 11:16:19.033','')
insert   mz_brcf   (hjdh,rq,hjdh2)      values   (   'TA00010000052','2012-05-22 11:39:33.673','A00010000052')
insert   mz_brcf   (hjdh,rq,hjdh2)      values   (   'TTA00010000052','2012-05-24 10:18:01.373','A00010000052')
insert  mz_brcf   (hjdh,rq,hjdh2)      values   (   'TTTA00010000052','2012-05-24 10:18:12.747','TA00010000052')
go



DECLARE @hjdh NVARCHAR(20)
SET @hjdh='TTTA00010000052'



;WITH c AS(    --往上遞歸
    SELECT hjdh,rq,hjdh2 FROM mz_brcf
        WHERE hjdh=@hjdh
    UNION ALL 
    SELECT a.hjdh,a.rq,a.hjdh2 FROM mz_brcf AS a
            INNER JOIN c AS b ON a.hjdh=b.hjdh2
),c2 AS (    -- 往下遞歸
    SELECT hjdh,rq,hjdh2 FROM c AS a
        WHERE NOT EXISTS(SELECT 1 FROM mz_brcf AS x WHERE x.hjdh=a.hjdh2) --找到頂點
    UNION ALL 
    SELECT a.hjdh,a.rq,a.hjdh2 FROM mz_brcf AS a
            INNER JOIN c2 AS b ON a.hjdh2=b.hjdh

)SELECT * FROM c2

求一递归查询sql语句,该怎么处理

相关推荐