初接触CTE,视为宝典,很是惊喜,心想大量的语句复用就解决了,可是实际使用起来,原来CTE不支持参数,就像函数不支持参数一样,还能成为函数吗?
举个例子吧:
with SaleData as (
select sum(amount) from SaleTab
where saledate between '20100101' and '20101231'
)
我的本意是想比较2010年和2009年的销售额,即saledate条件可以作为参数输入,可是CTE语句不接受参数,还有什么意义?
举个例子吧:
with SaleData as (
select sum(amount) from SaleTab
where saledate between '20100101' and '20101231'
)
我的本意是想比较2010年和2009年的销售额,即saledate条件可以作为参数输入,可是CTE语句不接受参数,还有什么意义?
(
select sum(amount) as amount from SaleTab
)select amount from SaleData where saledate between '20100101' and '20101231'
union all
select amount from SaleData where saledate between '20090101' and '20091231'
declare @date1 varchar(4)
declare @date2 varchar(4);with SaleData as (
select sum(amount) as cnum,ltrim(year(saledate)) as date
from SaleTab
group by year(saledate)
),t1 as
(
select cnum
from SaleData
where date = @date1
)select ..
from t1,(select cnum from SaleData where date = @date2)t
where t1... = t...--try
create table #SaleTab (saledate datetime,amount int)
insert #SaleTab
select '20100101',8 union all
select '20101231',18 declare @s as datetime
declare @e as datetime
set @s='20100101'
set @e='20101231'--注意with前要加個; ...給sum(amount)加個別名
;with SaleData as (
select sum(amount) as 總和 from #SaleTab
where saledate between @s and @e
)
select * from SaleData
我所知道的 诸如数据分层显示,数据递归最好利用CET
那你直接写存储过程或者视图都可以create view sa
as
select sum(col) as num,year(date) as date
from tb
group by year(date)查询的时候只需:select num
from sa
where date = 2010union allselect num
from sa
where date = 2011...解决方法很多,等楼下继续补充。
(
@s datetime,
@e datetime
)
as
select sum(amount) from SaleTab
where saledate between @s and @e
实际上,目前的情况下CTE可使用的范围是极为有限的,如果能够使用参数,肯定将大大拓宽其使用范围!
with t as (select InventoryID
from Material.Inventory
where InventoryID between @top and @down )
select * from t
InventoryID
51
53
72
90
93这难道不是使用参数?