如何写sql的递归查询语句?

例子:

cpcbid    cpcbbh          type  parentid  parentbh            cbze
70      2009120001      11      NULL        NULL           1536.0200
72      2009120002      11      NULL        NULL           706.0500
77      2009120003      11      NULL        NULL           528.5000
82      2009120006      12      70      2009120001     1614.5600
83      2009120007      11      NULL        NULL              NULL
84      2009120008      13      82      2009120006     1626.5600
86      2010010001      14      84      2009120008     1628.5600
87      2010010002      12      70      2009120001     1545.0200
88      2010010003      13      87      2010010002     1565.0200
89      2010010004      13      87      2010010002     1595.0200
90      2010010005      14      89      2010010004     1624.0200

上面一张表,现在要求显示成:
要求:type=11或=12的时候cbze放到jj列;type=13放到bj;type=14放到dj
cpcbid    cpcbbh                    jj                 bj               dj
  70     2009120001      1536.0200
  72     2009120002      706.0500
  77     2009120003      528.5000
  82     2009120006      1614.5600
  83     2009120007
  84     2009120008      1614.5600   1626.5600
  86     2010010001      1614.5600   1626.5600  1628.5600
  87     2010010002      1545.0200
  88     2010010003      1545.0200   1565.0200
  89     2010010004      1545.0200   1595.0200
  90     2010010005      1545.0200   1595.0200  1624.0200

1. 这是SQL2005的方法,其中用到WITH AS :

------------------------------------------------------------------

if object_id('tempdb.dbo.#TB') is not null
drop table #TB;
go
create table #TB
([cpcbid] int,[cpcbbh] int,[type] int,[parentid] int,[cbze] numeric(8,4));
insert #TB
select 70,2009120001,11,null,1536.0200 union all
select 72,2009120002,11,null,706.0500 union all
select 77,2009120003,11,null,528.5000 union all
select 82,2009120006,12,70,1614.5600 union all
select 83,2009120007,11,null,null union all
select 84,2009120008,13,82,1626.5600 union all
select 86,2010010001,14,84,1628.5600 union all
select 87,2010010002,12,70,1545.0200 union all
select 88,2010010003,13,87,1565.0200 union all
select 89,2010010004,13,87,1595.0200 union all
select 90,2010010005,14,89,1624.0200

--truncate table #tb;
select * from #tb;

;with cte as (
select *,lvl=0,pid=cpcbid from #tb
union all
select t.*,lvl=cte.lvl+1,cte.pid from cte,#tb t where cte.parentid=t.cpcbid
)
--select * from cte order by pid;
select pid,
max(case when type in (11,12) then cbze end) jj,
max(case when type=13 then cbze end) bj,
max(case when type=14 then cbze end) dj
from cte group by pid
/*
70    1536.0200    NULL    NULL
72    706.0500    NULL    NULL
77    528.5000    NULL    NULL
82    1614.5600    NULL    NULL
83    NULL    NULL    NULL
84    1614.5600    1626.5600    NULL
86    1614.5600    1626.5600    1628.5600
87    1545.0200    NULL    NULL
88    1545.0200    1565.0200    NULL
89    1545.0200    1595.0200    NULL
90    1545.0200    1595.0200    1624.0200
*/

 

在程序中，只要直接执行上面的SQL语句就行了。

此方法来自CSDN：http://topic.csdn.net/u/20100119/15/11b6fdca-b835-4a10-b4b9-dbd0eb3d8de6.html
此方法的关键是得到pid的值，pid是0层的cpcbid的值，1，2，3层的pid的值就是上级0层pid的值，这样就能通过对pid分组取得每一条纪录的JJ，BJ，DJ

 

2. 下面是SQL 2000的方法,我是用存储过程来实现的(黄色的是重点),只是语法不一样而已:

存储过程:

CREATE PROCEDURE [dbo].[pcms_jiagehuizong] AS
DECLARE @t_Level TABLE(Level int,cpcbid int,cpcbbh varchar(50),type int,parentid int,cbze decimal(9,4), pid int,qrrq datetime)
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT @Level, c.cpcbid, c.cpcbbh, c.type, c.jjid, c.cbze, c.cpcbid,c.qrrq
FROM ...... c
WHILE @@ROWCOUNT>0
BEGIN
    SET @Level=@Level+1
    INSERT @t_Level SELECT @Level, c.cpcbid, c.cpcbbh, c.type, c.jjid, c.cbze, b.pid, c.qrrq
    FROM ...... c,@t_Level b
     where c.cpcbid=b.parentid  AND b.Level=@Level-1
END

select case b.type when 11 then '基准表' when 12 then '调整表' when 13 then '报价表' when 14 then '定价表' end as type, a.pid as cpcbid, b.cpcbbh, b.cInvCode, b.cInvName, b.cInvStd, b.unit,b.netweight, b.grossweight,b.khbm,b.khmc, a.jj, a.bj, a.dj, a.jjqrrq, a.bjqrrq, a.djqrrq  from
(
 select pid,
 max(case when type in (11,12) then cbze end) jj,
 max(case when type=13 then cbze end) bj,
 max(case when type=14  then cbze end) dj,

 max(case when type in (11,12) then Convert(varchar(10),qrrq,120) end) jjqrrq,
 max(case when type=13 then Convert(varchar(10),qrrq,120) end) bjqrrq,
 max(case when type=14  then Convert(varchar(10),qrrq,120) end) djqrrq

 from @t_Level group by pid
 --取出已经确认的纪录
)  a inner join PCMS_CPCB b on a.pid = b.cpcbid where qrzt = 1 order by b.cInvCode
GO

 

在程序中可以写这么一条语句，通过执行这条语句来调用这个存储过程，并且返回结果集。

先建立一张临时表，在将存储过程返回的值放到临时表里，这样就可以对临时表进行筛选,求和等一些操作了；

"if object_id('tempdb.dbo.#temtable') is not null drop table #temtable; " +
                   "create table #temtable(" +
                   "type varchar(20) null," +
                   "cpcbid int," +
                   "cpcbbh varchar(50) null," +
                   "cinvcode varchar(20) null," +
                   "cinvname varchar(50) null," +
                   "cinvstd varchar(50) null," +
                   "unit varchar(10) null," +
                   "netweight numeric(14,4)," +
                   "grossweight numeric(14,4)," +
                   "khbm varchar(20) null," +
                   "khmc varchar(100) null," +
                   "jj numeric(14,4) null," +
                   "bj numeric(14,4) null," +
                   "dj numeric(14,4) null," +
                   "jjqrrq datetime null," +
                   "bjqrrq datetime null," +
                   "djqrrq datetime null);" +
                   "insert into #temtable exec pcms_jiagehuizong;";

 

 

---