SQL 关键字 With

Create TABLE tab (CurNum VARCHAR(10),PreNum VARCHAR(10))
INSERT INTO tab
SELECT '1', Null  UNION ALL
SELECT '2', '1' UNION ALL 
SELECT '3', '2'  UNION ALL
SELECT '4', '1'  UNION ALL
SELECT '5', Null

select * from tab
 SELECT CurNum,PreNum,path=CAST(CurNum AS VARCHAR(100))  FROM tab WHERE PreNum IS NULL
 
;WITH cte AS
(
 SELECT CurNum,PreNum,path=CAST(CurNum AS VARCHAR(100))  FROM tab WHERE PreNum IS NULL
 UNION ALL
 SELECT t.CurNum,t.PreNum,path=CAST(t.CurNum+','+c.path AS VARCHAR(100)) FROM tab t INNER JOIN cte c ON t.PreNum=c.CurNum
)
SELECT CurNum,path FROM cte ORDER BY CurNum
/*
CurNum     path
---------- ----------
1          1
2          2,1
3          3,2,1
4          4,1
5          5

(5 行受影响)
*/

SELECT CurNum,PreNum,path=CAST(CurNum AS VARCHAR(100))  FROM tab WHERE PreNum IS NULL

 SELECT t.CurNum,t.PreNum,path=CAST(t.CurNum+','+c.path AS VARCHAR(100)) FROM tab t INNER JOIN #Temp c ON t.PreNum=c.CurNum

 

 

 

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

 

if object_id('AlbeeTest.dbo.A') is not null drop table A
-- 创建数据表
create table A
(
id int,
name char(5),
cqye int
)
go
--插入测试数据
insert into A select 1,'张三',10
union all select 2,'李四',55
go

if object_id('AlbeeTest.dbo.B') is not null drop table B
-- 创建数据表
create table B
(
id int,
name char(5),
zje int,
jse int,
time char(10)
)
go
--插入测试数据
insert into B select 1,'张三',10,3,'2010-8-10'
union all select 2,'张三',20,7,'2010-8-11'
union all select 3,'张三',8,2,'2010-8-12'
union all select 4,'张三',6,15,'2010-8-13'
union all select 5,'李四',9,20,'2010-8-10'
union all select 6,'李四',15,9,'2010-8-11'
go
--代码实现
select * from A
select * from B
select b.id,b.name,cqye,zje,jse,cqye+zje-jse as hj,b.time
  from A a,B b,(select name,min(time) as time from B group by name)ttt
  where a.name=b.name and b.name=ttt.name and b.time=ttt.time

;with t as(
 select b.id,b.name,cqye,zje,jse,cqye+zje-jse as hj,b.time
  from A a,B b,(select name,min(time) as time from B group by name)ttt
  where a.name=b.name and b.name=ttt.name and b.time=ttt.time
 union all
 select b.id,b.name,t.hj as cqye,b.zje,b.jse,t.hj+b.zje-b.jse as hj,b.time
  from t join B b on t.name=b.name and t.id=b.id-1
)
select * from t order by id

/*测试结果

id name cqye zje jse hj time
--------------------------------------------------------------
1 张三  10 10 3 17 2010-8-10
2 张三  17 20 7 30 2010-8-11
3 张三  30 8 2 36 2010-8-12
4 张三  36 6 15 27 2010-8-13
5 李四  55 9 20 44 2010-8-10
6 李四  44 15 9 50 2010-8-11

(6行受影响)
*/