SQL中CTE問題？

[shentanyi]

；with Flats as(),FT as()
select * from FT
用到兩個CTE,有哪個高手可以有用到兩個CTE的簡單例子啊？一般什麽情況下要用到兩個CTE。

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(我是小F，向高手学习)
-- Date    :2009-11-09 14:14:36
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
--    Nov 24 2008 13:01:59 
--    Copyright (c) 1988-2005 Microsoft Corporation
--    Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([number] int,[date] datetime)
insert [tb]
select 8,'2009/1/11 2:00' union all
select 7,'2009/1/11 5:00' union all
select 6,'2009/1/11 12:00' union all
select 5,'2009/1/11 18:00' union all
select 4,'2009/1/12 4:00' union all
select 3,'2009/1/12 10:00' union all
select 2,'2009/1/12 12:00' union all
select 1,'2009/1/12 17:00'
--------------开始查询--------------------------
;with f1 as
(
select 
  * 
from 
  [tb] t 
where 
  date=(select min(date) from tb where convert(varchar(10),date,120)=convert(varchar(10),t.date,120))
),
f2 as
(
select
  * 
from
 [tb] t 
where
 date=(select max(date) from tb where convert(varchar(10),date,120)=convert(varchar(10),t.date,120))
)
select f1.number-f2.number from f1,f2 where convert(varchar(10),f1.date,120)=convert(varchar(10),f2.date,120)
----------------结果----------------------------
/*
-----------
3
3

(2 行受影响) 
*/

[SQL77]

分开创建吧,

[shentanyi]

[Quote=引用 1 楼 fredrickhu 的回复:]
SQL code----------------------------------------------------------------
-- Author :fredrickhu(我是小F，向高手学习)
-- Date :2009-11-09 14:14:36
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00?-
[/Quote]
請說明一下，你的思路？

[--小F--]

如果你把CTE理解为一个临时表就容易理解了 不过CTE不是临时表

[guguda2008]

第一个CTE作为第二个CTE循环的源

[shentanyi]

小F，with EMPCTE(empid,empname,mgrid,lvl) as
(
select empid,empname,mgrid,0 from employees where
empid=3 union all select e.empid,emempname,emmgrid
m.lvl+1 from employees as e join empctejanet as m
on e.mgrid=m.empid where lvl<2
)
select empid,empname from empcte where lvl=2 這裡是遞歸層，返回字節點，而你上面返回的CTE的個數嗎？

[yesyesyes]

最简单的
with a as
(select * from t),
b as
(select * from a)
select * from b

[黄_瓜]

[Quote=引用 7 楼 yesyesyes 的回复:]
最简单的
with a as
(select * from t),
b as
(select * from a)
select * from b
[/Quote]
嗯