有这样的表:
f_no f_str0 f_str1 f_str2 f_net f_nettime
1 一期 f1 石灰 55 2010-8-1 10:04:25
2 二期 f1 石灰 55 2010-8-2 10:04:25
3 一期 f1 水泥 55 2010-8-3 10:04:25
4 二期 f1 水泥 55 2010-8-4 10:04:25
5 二期 f1 水泥 55 2010-8-5 10:04:25
6 一期 f1 石灰 55 2010-8-7 10:04:25
使用这样的语句可以:
declare @Tsql varchar(8000)set @Tsql =
'select f_nettime as 日期 'select @Tsql = @Tsql + ' , sum(case [f_str0] when ''' + [f_str0] +''' then f_net else 0 end) [' + [f_str0]+ ']'
from (select distinct (case when [f_str0] ='' then '空名' when [f_str0] is null then '空名' else [f_str0] end )as [f_str0] from t_detail where
f_nettime between '2010-1-1 00:00:00' and '2010-8-4 23:59:59' ) as 总重量 set @Tsql = @Tsql + ' from t_detail group by f_nettime'
exec(@Tsql)但是将两个字段组合以后就不行了?为什么
declare @Tsql varchar(8000)set @Tsql =
'select f_nettime as 日期 'select @Tsql = @Tsql + ' , sum(case [f_str0+F_STR2] when ''' + [f_str0+F_STR2] +''' then f_net else 0 end) [' + [f_str0+F_STR2]+ ']'
from (select distinct (case when [f_str0+F_STR2] ='' then '空名' when [f_str0+F_STR2] is null then '空名' else [f_str0+F_STR2] end )as [f_str0+F_STR2] from t_detail where
f_nettime between '2010-1-1 00:00:00' and '2010-8-4 23:59:59' ) as 总重量 set @Tsql = @Tsql + ' from t_detail group by f_nettime'
exec(@Tsql)
我的目的是将他们统计成下面的表格:
日期 一期石灰 二期石灰 一期水泥 二期水泥
2010-8-1 110 55 55 55
2010-8-2 110 55 55 55
2010-8-3 110 55 55 55
2010-8-4 110 55 55 55
f_no f_str0 f_str1 f_str2 f_net f_nettime
1 一期 f1 石灰 55 2010-8-1 10:04:25
2 二期 f1 石灰 55 2010-8-2 10:04:25
3 一期 f1 水泥 55 2010-8-3 10:04:25
4 二期 f1 水泥 55 2010-8-4 10:04:25
5 二期 f1 水泥 55 2010-8-5 10:04:25
6 一期 f1 石灰 55 2010-8-7 10:04:25
使用这样的语句可以:
declare @Tsql varchar(8000)set @Tsql =
'select f_nettime as 日期 'select @Tsql = @Tsql + ' , sum(case [f_str0] when ''' + [f_str0] +''' then f_net else 0 end) [' + [f_str0]+ ']'
from (select distinct (case when [f_str0] ='' then '空名' when [f_str0] is null then '空名' else [f_str0] end )as [f_str0] from t_detail where
f_nettime between '2010-1-1 00:00:00' and '2010-8-4 23:59:59' ) as 总重量 set @Tsql = @Tsql + ' from t_detail group by f_nettime'
exec(@Tsql)但是将两个字段组合以后就不行了?为什么
declare @Tsql varchar(8000)set @Tsql =
'select f_nettime as 日期 'select @Tsql = @Tsql + ' , sum(case [f_str0+F_STR2] when ''' + [f_str0+F_STR2] +''' then f_net else 0 end) [' + [f_str0+F_STR2]+ ']'
from (select distinct (case when [f_str0+F_STR2] ='' then '空名' when [f_str0+F_STR2] is null then '空名' else [f_str0+F_STR2] end )as [f_str0+F_STR2] from t_detail where
f_nettime between '2010-1-1 00:00:00' and '2010-8-4 23:59:59' ) as 总重量 set @Tsql = @Tsql + ' from t_detail group by f_nettime'
exec(@Tsql)
我的目的是将他们统计成下面的表格:
日期 一期石灰 二期石灰 一期水泥 二期水泥
2010-8-1 110 55 55 55
2010-8-2 110 55 55 55
2010-8-3 110 55 55 55
2010-8-4 110 55 55 55
set @Tsql = 'select f_nettime as 日期 'select @Tsql = @Tsql + ' , sum(case f_str0+F_STR2 when ''' + [ff] +''' then f_net else 0 end) [' + [ff]+ ']'
from (select distinct (case when f_str0+F_STR2 ='' then '空名' when f_str0+F_STR2 is null then '空名' else f_str0+F_STR2 end )as [ff] from t_detail where
f_nettime between '2010-1-1 00:00:00' and '2010-8-4 23:59:59' ) as 总重量 set @Tsql = @Tsql + ' from t_detail group by f_nettime' exec(@Tsql)
日期 二期石灰 二期水泥 一期石灰 一期水泥
----------------------- ----------- ----------- ----------- -----------
2010-08-01 10:04:25.000 0 0 55 0
2010-08-02 10:04:25.000 55 0 0 0
2010-08-03 10:04:25.000 0 0 0 55
2010-08-04 10:04:25.000 0 55 0 0
2010-08-05 10:04:25.000 0 55 0 0
2010-08-07 10:04:25.000 0 0 55 0(6 行受影响)
改成类似的,结果可能不是你要的。
set @Tsql = 'select f_nettime as 日期 '
select @Tsql = @Tsql + ' , sum(case f_str0+F_STR2 when ''' + [f_str0+F_STR2] +''' then f_net else 0 end) [' + [f_str0+F_STR2]+ ']'
from (select distinct (case when f_str0+F_STR2 ='' then '空名' when f_str0+F_STR2 is null then '空名' else f_str0+F_STR2 end )as [f_str0+F_STR2]
from t_detail where f_nettime between '2010-1-1 00:00:00' and '2010-8-4 23:59:59' ) as 总重量
set @Tsql = @Tsql + ' from t_detail group by f_nettime'
exec(@Tsql)不是字段名 不能加 []