导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

动态sql问题,错误的标号?

zhujinqiang 2008-01-24 10:02:12
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'.
...全文
44 点赞 收藏 5
写回复
5 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
zhujinqiang 2008-01-24
谢谢。
回复
青锋-SS 2008-01-24
这样应该没有问题了,楼主执行一下看看结果是否符合:
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
回复
青锋-SS 2008-01-24
楼主拼的语句也太长了吧
回复
pt1314917 2008-01-24

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 2008-01-24
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
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告