from (select distinct CONVERT(varchar(10),datepart(week,billdate)) as billdate from kaicheng_TBusRetailDT
where style in (select style from [@temp])
--关键是这句话错误,忘记了使用引号作字符串拼接了,那么,SQL解析器就把@temp当成一个对象,所以报错
where style in (select style from [@temp])
--关键是这句话错误,忘记了使用引号作字符串拼接了,那么,SQL解析器就把@temp当成一个对象,所以报错
解决方案 »
- 还是同样的问题
- 第一次提问,SQL SERVER2000和SQL SERVER2005 能装在同一台电脑上,而相互不影响使用吗?
- 还是全文索引的问题,怎么时时保证population到全文目录?
- 求教:不大明白建数据源在连接数据库中的作用
- 数据库的安全性
- 求这样的查询
- 请大家帮忙看看这个sql语句怎么写!
- 数据库表的问题
- 在access中 cobName 为组合框变量.但为什么没有cobName.AddItem属性
- 如何做一选四的单选
- SQL SEVER 2008安装后出现的2个实例的区别,高分求解
- insert into select 语法错误。“违反了UNIQUE KEY 约束'***',不能在对象'dbo.T1'中插入重复
+' sum(case CONVERT(varchar(10),datepart(week,billdate)) when ''' + billdate + ''' then Fmount else 0 end) [' + dbo.GetWeekRange(billdate) + '周成交金额]'
+', sum(case CONVERT(varchar(10),datepart(week,billdate)) when ''' + billdate + ''' then Fmount-qty*fob else 0 end) [' + dbo.GetWeekRange(billdate) + '周毛利]'
from (select distinct CONVERT(varchar(10),datepart(week,billdate)) as billdate from kaicheng_TBusRetailDT
where style in (select style from [@temp])
这个一整算的
如果加上引号,上面就无法执行
对了 你的这个 @temp 是个表变量,还是只是一个 变量呀
SET @a='#t'
declare @sql varchar(max)
SELECT @sql='select * into '+@a+' from sys.sysobjects'
EXEC (@sql)
这样试试,不过EXEC完了之后临时表就会消失,所以如果你后续还需要用的话,就不要用临时表
对了 你的这个 @temp 是个表变量,还是只是一个 变量呀表变量,,,
对了 你的这个 @temp 是个表变量,还是只是一个 变量呀表变量,,,哦 那上面就不能写成 : [@temp] 而是只能写成:@temp另外,还有一个我问题是,在一个普通语句中,表的名称是不能用变量来表示的,也就是不能用@temp来表示,除非是在动态拼接的语句中,也就说你只能把下面的语句,都变成动态语句才行:
set @temp = '##ddd'
set @sql = 'select style as ' + '款号'
select @sql = @sql + ' , sum(case CONVERT(varchar(10),datepart(week,billdate)) when ''' + billdate + ''' then qty else 0 end) [' + dbo.GetWeekRange(billdate) + '周零售数量],'
+' sum(case CONVERT(varchar(10),datepart(week,billdate)) when ''' + billdate + ''' then Fmount else 0 end) [' + dbo.GetWeekRange(billdate) + '周成交金额]'
+', sum(case CONVERT(varchar(10),datepart(week,billdate)) when ''' + billdate + ''' then Fmount-qty*fob else 0 end) [' + dbo.GetWeekRange(billdate) + '周毛利]'
from (select distinct CONVERT(varchar(10),datepart(week,billdate)) as billdate from kaicheng_TBusRetailDT
where style in (select style from [@temp])--select @sql = @sql +@temp )
) as a order by billdate
set @sql = @sql + ' from kaicheng_TBusRetailDT where style in (select style from '+ @temp+') group by style '
select @sql
exec(@sql)
哦,呵呵,没事。对了,这样的话,也有问题,下面这句就错了:where style in (select style from [@temp])
改了一下,你试试:set @temp = '##ddd'-------------------增加
declare @sql_t nvarchar(1000)set @sql_t = 'select style from ['+@temp+']'
if OBJECT_ID('tempdb..#temp_123') is not null
drop table #temp_123
create table #temp_123(style varchar(100))insert into #temp_123
exec(@sql)
----------------------
set @sql = 'select style as ' + '款号'
select @sql = @sql + ' , sum(case CONVERT(varchar(10),datepart(week,billdate)) when ''' + billdate + ''' then qty else 0 end) [' + dbo.GetWeekRange(billdate) + '周零售数量],'
+' sum(case CONVERT(varchar(10),datepart(week,billdate)) when ''' + billdate + ''' then Fmount else 0 end) [' + dbo.GetWeekRange(billdate) + '周成交金额]'
+', sum(case CONVERT(varchar(10),datepart(week,billdate)) when ''' + billdate + ''' then Fmount-qty*fob else 0 end) [' + dbo.GetWeekRange(billdate) + '周毛利]'
from (select distinct CONVERT(varchar(10),datepart(week,billdate)) as billdate from kaicheng_TBusRetailDT
where style in (select style from #temp_123)
insert into #temp_123这个固定值,等下2个同时使用,我怕冲突,,我才使用变量,,
改一下,用表变量:
set @temp = '##ddd'-------------------增加
declare @sql_t nvarchar(1000)set @sql_t = 'select style from ['+@temp+']'
declare @temp_123 table(style varchar(100))insert into @temp_123
exec(@sql)
----------------------
set @sql = 'select style as ' + '款号'
select @sql = @sql + ' , sum(case CONVERT(varchar(10),datepart(week,billdate)) when ''' + billdate + ''' then qty else 0 end) [' + dbo.GetWeekRange(billdate) + '周零售数量],'
+' sum(case CONVERT(varchar(10),datepart(week,billdate)) when ''' + billdate + ''' then Fmount else 0 end) [' + dbo.GetWeekRange(billdate) + '周成交金额]'
+', sum(case CONVERT(varchar(10),datepart(week,billdate)) when ''' + billdate + ''' then Fmount-qty*fob else 0 end) [' + dbo.GetWeekRange(billdate) + '周毛利]'
from (select distinct CONVERT(varchar(10),datepart(week,billdate)) as billdate from kaicheng_TBusRetailDT
where style in (select style from @temp_123)