又是个统计的SQL语句问题!~~

[yinrongg]

有这样的表:
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

[yinrongg]

自己顶起先

[华夏小卒]

declare @Tsql varchar(8000)
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 行受影响)

改成类似的，结果可能不是你要的。

[yinrongg]

[Quote=引用 2 楼 js_szy 的回复:]
SQL code
declare @Tsql varchar(8000)
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 (sel……
[/Quote] 呵呵,我忙乎了半天也没找到错误原因,,

[华夏小卒]

速度结贴

[hao1hao2hao3]

可以用两个嵌套的case when 加上上次问题中生成的的那个临时的时间表来进行查询。

[永生天地]

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)

不是字段名 不能加 []