declare @sql varchar(8000)
set @sql='select plevel'
select @sql=@sql+',['+dd+']=sum(case convert(varchar(10),workdate,120) when '''+dd+''' then Times*qty else 0 end)'
from (select distinct dd=convert(varchar(10),workdate,120) from tb) ss
set @sql=@sql+' from tb group by plevel'
exec(@sql)
我把上面的换成了下面的:
declare @sql varchar(8000)
set @sql='select plevel'
select @sql=@sql+',['+dd+']=sum(case convert(varchar(10),workdate,120) when '''+dd+''' then Times*qty else 0 end)'
from (select distinct dd=convert(varchar(10),workdate,120) from (Select a.* from(Select plevel,workdate,qty*times as ff from tb group by plevel,workdate,qty*times ) a where ff>500)) ss
set @sql=@sql+' from tb group by plevel'
exec(@sql)
就提示Incorrect syntax near ')'
我就是把倒数第三行的表tb换成了(Select a.* from(Select plevel,workdate,qty*times as ff from tb group by plevel,workdate,qty*times ) a where ff>500),这句语句执行没有问题的。为什么放在这里就出错了?
请高手帮忙!谢谢!
set @sql='select plevel'
select @sql=@sql+',['+dd+']=sum(case convert(varchar(10),workdate,120) when '''+dd+''' then Times*qty else 0 end)'
from (select distinct dd=convert(varchar(10),workdate,120) from tb) ss
set @sql=@sql+' from tb group by plevel'
exec(@sql)
我把上面的换成了下面的:
declare @sql varchar(8000)
set @sql='select plevel'
select @sql=@sql+',['+dd+']=sum(case convert(varchar(10),workdate,120) when '''+dd+''' then Times*qty else 0 end)'
from (select distinct dd=convert(varchar(10),workdate,120) from (Select a.* from(Select plevel,workdate,qty*times as ff from tb group by plevel,workdate,qty*times ) a where ff>500)) ss
set @sql=@sql+' from tb group by plevel'
exec(@sql)
就提示Incorrect syntax near ')'
我就是把倒数第三行的表tb换成了(Select a.* from(Select plevel,workdate,qty*times as ff from tb group by plevel,workdate,qty*times ) a where ff>500),这句语句执行没有问题的。为什么放在这里就出错了?
请高手帮忙!谢谢!
解决方案 »
- 同一个系统安装两个数据库,请教如何访问有实例的数据库?
- SQL 数据类型问题
- sql server 运算
- sqlserver2005 评估期已过 解决办法?
- 参数为空时的字段更新问题????
- 关于查询的问题~~~~~~~~~~~~~~~~~~~
- 在SqlServer中新建DTS包时如何新建一个全局变量,因为我原来在一个包中创建过一个全局变量,但是在开始执行的时候却找不到全局变量而失败.
- 导出txt文件路径选择
- 存储过程如何定义参数才能同时输入主从表数据,然后一次过插入数据库啊
- tell me the way...
- web页面向sql提交了一条命令,web页面关闭后,sql还运行吗?
- 把tb2中的marketId ,StockId不存在于tb1中的记录插入到tb1中。这样写么???
declare @sql varchar(8000)
set @sql='select plevel'
select @sql=@sql+',['+dd+']=sum(case convert(varchar(10),workdate,120) when '
+''''+dd+'''' +' then Times*qty else 0 end)'
from (select distinct dd=convert(varchar(10),workdate,120) from (Select a.* from(Select plevel,workdate,qty*times as ff from tb group by plevel,workdate,qty*times ) a where ff>500)) ss
set @sql=@sql+' from tb group by plevel'
exec(@sql)--还有错的花酒print @sql出来看看哪儿有问题
declare @sql varchar(8000)
set @sql='select plevel'
select
@sql=@sql+
',['+dd+']=sum(
case convert(varchar(10),workdate,120)
when '''+dd+'''
then Times*qty
else 0
end
)'
from (
select distinct
dd=convert(varchar(10),workdate,120)
from (
Select
a.*
from
(
Select
plevel,
workdate,
qty*times as ff
from tb
group by plevel,workdate,qty*times
) a
)tmp
where ff>500
) ss
set @sql=@sql+' from tb group by plevel'
exec(@sql)
declare @sql varchar(8000)
set @sql='select plevel'
select @sql=@sql+',['+dd+']=sum(case convert(varchar(10),workdate,120) when '''+dd+''' then Times*qty else 0 end)'
from
(
select distinct dd=convert(varchar(10),workdate,120)
from
(
Select a.*
from
(
Select plevel,workdate,qty*times as ff
from tb
group by plevel,workdate,qty*times
) a
where ff>500
) ss) aa
set @sql=@sql+' from tb group by plevel'
以下是表tb
ID qty times workdate plevel
1 99 8 2012-1-1 kive
2 98 8.6 2012-1-10 kive
3 95 8.9 2012-1-1 john
4 67 9 2012-1-2 john
5 80 8 2012-1-7 john
6 59 8.2 2012-1-1 li
7 55 9.1 2012-1-10 li
8 37 8 2012-1-14 low
9 39 8.7 2012-1-15 low
10 48 10 2012-1-20 low
1 99 8 2012-1-1 kive
2 98 8.6 2012-1-10 kive
3 95 8.9 2012-1-1 john
4 67 9 2012-1-2 john
5 80 8 2012-1-7 john
6 59 8.2 2012-1-1 li
7 55 9.1 2012-1-10 li
8 37 8 2012-1-14 low
9 39 8.7 2012-1-15 low
10 48 10 2012-1-20 low