处方表mz_brcf主键为hjdh,hjdh2表示来源处方号，如果是退方生成的处方hjdh2=原方的hjdh.1条记录可以多次生成退方。即hjdh2=hjdh的记录可能有多条。且生成的退方还能再生成它的退方。我想要的结果是输入一个hjdh，会显示出与它相关的记录，以及相关的相关的记录。。。
如
A00010000052或者输入TA00010000052、TTA00010000052、TTTA00010000052
都能够得到
- SQL code
--hjdh,rq,hjdh2A00010000052 2012-05-22 11:15:47.487 TA00010000052 2012-05-22 11:39:33.673 A00010000052TTA00010000052 2012-05-24 10:18:01.373 A00010000052TTTA00010000052 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,hjdh2from mz_brcfwhere hjdh='A00010000052'union ALLselect hjdh,rq,hjdh2from mz_brcfwhere hjdh2='A00010000052'union ALLselect t1.hjdh,t1.rq,t1.hjdh2from mz_brcf t1join mzhjdh as t2on 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 [email protected] UNION
select b.hjdh,b.rq,b.hjdh2 from mz_brcf a,mz_brcf b where [email protected] and b.hjdh=a.hjdh2 UNION
select c.hjdh,c.rq,c.hjdh2 from mz_brcf a,mz_brcf b,mz_brcf c where [email protected] 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 TA00010000052CREATE 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')goDECLARE @hjdh NVARCHAR(20)SET @hjdh='TTTA00010000052';WITH c AS( --往上遞歸 SELECT hjdh,rq,hjdh2 FROM mz_brcf WHERE [email protected] 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