动态sql问题，错误的标号？

[zhujinqiang]

DECLARE @s nvarchar(1000) 
DECLARE @df1 nvarchar(100) 
set @df1='hourdate_20080122'
set @s='select M.ti as 节点,M.rhour as 时间,max(M.ss) as 次数,max(M.cc) as 故障次数 from
(
select ti,datepart(hour,ttime) as rhour,count(*) as ss from '
set @s=@s+CONVERT(varchar(10), @df1)
set @s=@s+' where datepart(hour,ttime) in (1,2,3) AND type = '''0'''
group by ti,datepart(hour,ttime)
union all
select ti,datepart(hour,ttime) as rhour,count(*) as cc from '
set @s=@s+CONVERT(varchar(10), @df1)
set @s=@s+' where datepart(hour,ttime) in (1,2,3) AND type = '''0''' and no_fail='''0'''
group by substring(ti,1,7),datepart(hour,ttime)

) M
group by M.ti,M.rhour'
EXEC sp_executesql @s

---------------
服务器: 消息 170，级别 15，状态 1，行 8
Line 8: Incorrect syntax near '0'.
服务器: 消息 170，级别 15，状态 1，行 13
Line 13: Incorrect syntax near '0'.

[青锋-SS]

DECLARE @s nvarchar(1000) 
DECLARE @df1 nvarchar(100) 
set @df1='hourdate_20080122'
set @s='select M.ti as 节点,M.rhour as 时间,max(M.ss) as 次数,max(M.cc) as 故障次数 from
(
select ti,datepart(hour,ttime) as rhour,count(*) as ss from '
set @s=@s+CONVERT(varchar(10), @df1)
set @s=@s+' where datepart(hour,ttime) in (1,2,3) AND type = ''0'''
group by ti,datepart(hour,ttime)
union all
select ti,datepart(hour,ttime) as rhour,count(*) as cc from '
set @s=@s+CONVERT(varchar(10), @df1)
set @s=@s+' where datepart(hour,ttime) in (1,2,3) AND type = ''0'' and no_fail=''0'''
group by substring(ti,1,7),datepart(hour,ttime)

) M
group by M.ti,M.rhour'
EXEC sp_executesql @s

[pt1314917]

try:


DECLARE @s nvarchar(1000) 
DECLARE @df1 nvarchar(100) 
set @df1='hourdate_20080122'
set @s='select M.ti as 节点,M.rhour as 时间,max(M.ss) as 次数,max(M.cc) as 故障次数 from
(
select ti,datepart(hour,ttime) as rhour,count(*) as ss from '
set @s=@s+CONVERT(varchar(10), @df1)
set @s=@s+' where datepart(hour,ttime) in (1,2,3) AND type = ''0''
group by ti,datepart(hour,ttime)
union all
select ti,datepart(hour,ttime) as rhour,count(*) as cc from '
set @s=@s+CONVERT(varchar(10), @df1)
set @s=@s+' where datepart(hour,ttime) in (1,2,3) AND type = ''0'' and no_fail=''0''
group by substring(ti,1,7),datepart(hour,ttime)
) M
group by M.ti,M.rhour'
EXEC(@s)

[青锋-SS]

楼主拼的语句也太长了吧

[青锋-SS]

这样应该没有问题了,楼主执行一下看看结果是否符合:

DECLARE @s nvarchar(1000) 
DECLARE @df1 nvarchar(100) 
set @df1='hourdate_20080122'
set @s='select M.ti as 节点,M.rhour as 时间,max(M.ss) as 次数,max(M.cc) as 故障次数 from(select ti,datepart(hour,ttime) as rhour,count(*) as ss from '
set @s=@s+CONVERT(varchar(10), @df1)
set @s=@s+' where datepart(hour,ttime) in (1,2,3) AND type = ''0'' group by ti,datepart(hour,ttime) union all select ti,datepart(hour,ttime) as rhour,count(*) as cc from '
set @s=@s+CONVERT(varchar(10), @df1)
set @s=@s+' where datepart(hour,ttime) in (1,2,3) AND type = ''0'' and no_fail=''0'' group by substring(ti,1,7),datepart(hour,ttime)) M group by M.ti,M.rhour'
EXEC sp_executesql @s

[zhujinqiang]

谢谢。