新手求教，语句中的变量不起作用？
declare @date1 datetime
declare @date2 datetime
declare @busrun varchar(50)
set @date1='2013-09-10 00:00:00'
set @date2='2013-09-10 23:59:59'
set @busrun='t_bus_run20130910';
with t1 as
(
select bus_id,COUNT(*) as geshu from @busrun where runtime>@date1 and runtime<=@date2
group by bus_id ),
t2 as (select a.bus_id,MAX(runtime) as maxtime from @busrun a join t1 b on a.bus_id=b.bus_id and a.runtime>@date1 and a.runtime<=@date2
group by a.bus_id ),
t3 as (select aa.bus_id,min(runtime) as mintime from @busrun aa join t1 bb on aa.bus_id=bb.bus_id and aa.runtime>@date1 and aa.runtime<=@date2
group by aa.bus_id )
select a.bus_id ,a.geshu,datediff(s,mintime,maxtime) as runtime,(a.geshu*10.0)/(datediff(s,mintime,maxtime)) as xiaoshu,mintime,maxtime,
SUBSTRING (convert(varchar (20),(a.geshu*10.0/datediff(s,mintime,maxtime)*100)),1,4)+'%' as per
from t1 a,t2,t3
where a.bus_id=t2.bus_id and t2.bus_id=t3.bus_id
order by a.bus_id
执行完以后提示
消息 1087，级别 15，状态 2，第 10 行
必须声明表变量 "@busrun"。
消息 1087，级别 15，状态 2，第 12 行
必须声明表变量 "@busrun"。
消息 1087，级别 15，状态 2，第 14 行
必须声明表变量 "@busrun"。
我已经声明了@busrun了，难道语句用的不对，还是表名不能作为变量，我把@busrun变量的引号去掉也不行，求高手指点
[解决办法]
declare @date1 datetime
declare @date2 datetime
declare @busrun varchar(50),@cmd NVARCHAR(MAX)
SELECT @date1='2013-09-10 00:00:00',@date2='2013-09-10 23:59:59',@busrun='t_bus_run20130910';
SET @cmd=
';with t1 as
(
select bus_id,COUNT(*) as geshu
from '+@busrun+' where runtime>@date1 and runtime<=@date2
group by bus_id ),
t2 as
(select a.bus_id,MAX(runtime) as maxtime
from '+@busrun+' a join t1 b on a.bus_id=b.bus_id and a.runtime>@date1 and a.runtime<=@date2
group by a.bus_id ),
t3 as
(select aa.bus_id,min(runtime) as mintime
from '+@busrun+' aa join t1 bb on aa.bus_id=bb.bus_id and aa.runtime>@date1 and aa.runtime<=@date2
group by aa.bus_id )
select a.bus_id ,a.geshu,datediff(s,mintime,maxtime) as runtime,(a.geshu*10.0)/(datediff(s,mintime,maxtime)) as xiaoshu,mintime,maxtime,
SUBSTRING (convert(varchar (20),(a.geshu*10.0/datediff(s,mintime,maxtime)*100)),1,4)+''%'' as per
from t1 a,t2,t3
where a.bus_id=t2.bus_id and t2.bus_id=t3.bus_id
order by a.bus_id'
EXEC(@cmd)
[解决办法]
按你的需求，你的语句不需要写得那么复杂，基本上可以一步到位
declare @date1 datetime
declare @date2 datetime
declare @busrun varchar(50)
set @date1='2013-09-10 00:00:00'
set @date2='2013-09-10 23:59:59'
/*
select bus_id,COUNT(*) as geshu,MAX(runtime) as maxtime,min(runtime) as mintime
from t_bus_run20130910
where runtime>@date1 and runtime<=@date2
group by bus_id
*/
select a.bus_id ,a.geshu,datediff(s,mintime,maxtime) as runtime,(a.geshu*10.0)/(datediff(s,mintime,maxtime)) as xiaoshu,mintime,maxtime,
SUBSTRING (convert(varchar (20),(a.geshu*10.0/datediff(s,mintime,maxtime)*100)),1,4)+'%' as per
from
(
select bus_id,COUNT(*) as geshu,MAX(runtime) as maxtime,min(runtime) as mintime
from t_bus_run20130910
where runtime>@date1 and runtime<=@date2
group by bus_id
)a
order by a.bus_id
declare @date1 datetime
declare @date2 datetime
declare @busrun varchar(50)
set @date1='2013-09-10 00:00:00'
set @date2='2013-09-10 23:59:59'
--set @busrun='t_bus_run20130910';
;with t1 as
(
select bus_id,COUNT(*) as geshu from t_bus_run20130910 where runtime>@date1 and runtime<=@date2
group by bus_id ),
t2 as (select a.bus_id,MAX(runtime) as maxtime from t_bus_run20130910 a join t1 b on a.bus_id=b.bus_id and a.runtime>@date1 and a.runtime<=@date2
group by a.bus_id ),
t3 as (select aa.bus_id,min(runtime) as mintime from t_bus_run20130910 aa join t1 bb on aa.bus_id=bb.bus_id and aa.runtime>@date1 and aa.runtime<=@date2
group by aa.bus_id )
select a.bus_id ,a.geshu,datediff(s,mintime,maxtime) as runtime,(a.geshu*10.0)/(datediff(s,mintime,maxtime)) as xiaoshu,mintime,maxtime,
SUBSTRING (convert(varchar (20),(a.geshu*10.0/datediff(s,mintime,maxtime)*100)),1,4)+'%' as per
from t1 a,t2,t3
where a.bus_id=t2.bus_id and t2.bus_id=t3.bus_id
order by a.bus_id
