存储过程求助

Question

create table tb_a 
(
   ph varchar(20),
   tdate  varchar(6),
   qty  numeric
)
GOinsert tb_a
select 'aaaaaaaaa','201101',100 union all
select 'aaaaaaaaa','201102',200 union all
select 'aaaaaaaaa','201103',0 union all
select 'aaaaaaaaa','201104',100 union all
select 'bbbbbbb','201101',100 union all
select 'bbbbbbb','201102',0 union all
select 'bbbbbbb','201103',200 union all
select 'bbbbbbb','201104',100 union all
select 'bbbbbbb','201105',400
GOCREATE  PROC  PROC_tb_a
@dt1 datetime , @dt2 datetime
AS
begin
declare @sql varchar(8000)
set @sql = 'select ph '
select @sql = @sql + ' ,  max(case tdate when '''  + tdate +  ''' then qty  else 0 end) [' + tdate + ']'
from (select distinct tdate  from tb_a  where tdate  between @dt1 and @dt2 ) as a
set @sql = @sql + ',cast(avg(qty*1.0) as decimal(18,2)) 平均用量  from  tb_a  where tdate  between ''' + convert(varchar(6),@dt1,112) + ''' and ''' + convert(varchar(6),@dt2,112) + ''' group by ph'
exec(@sql) 
end
GOexec PROC_tb_a '201101','201105' 得不到需要的结果
---------要求结果如下，请问要得到这样的结果应该如何修改上面的存储过程？--------    ph          201101   201102      201103    201104   201105    平均用量
aaaaaaaaa        100      200        0         100       0           80
bbbbbbb          100      0          200       100       400         160

Answer 1

create table tb_a 
(
   ph varchar(20),
   tdate  varchar(6),
   qty  numeric
)
GOinsert tb_a
select 'aaaaaaaaa','201101',100 union all
select 'aaaaaaaaa','201102',200 union all
select 'aaaaaaaaa','201103',0 union all
select 'aaaaaaaaa','201104',100 union all
select 'bbbbbbb','201101',100 union all
select 'bbbbbbb','201102',0 union all
select 'bbbbbbb','201103',200 union all
select 'bbbbbbb','201104',100 union all
select 'bbbbbbb','201105',400
GOCREATE  PROC  PROC_tb_a
@dt1 datetime , @dt2 datetime
AS
begin
declare @sql varchar(8000)
set @sql = 'select ph '
select @sql = @sql + ' ,  max(case when tdate='''  + tdate +  ''' then qty  else 0 end) [' + tdate + ']'
from (select distinct tdate  from tb_a  where tdate  between @dt1 and @dt2 ) as a
set @sql = @sql + ',cast(avg(qty*1.0) as decimal(18,2)) 平均用量  from  tb_a group by ph'
exec(@sql) 
end
GOexec PROC_tb_a '201101','201105'
/*(9 行受影响)
ph                   201101                                  201102                                  201103                                  201104                                  201105                                  平均用量
-------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
aaaaaaaaa            100                                     200                                     0                                       100                                     0                                       100.00
bbbbbbb              100                                     0                                       200                                     100                                     400                                     160.00(2 行受影响)

Answer 2

本帖最后由 josy 于 2011-05-19 13:35:35 编辑

  

Answer 3

谢谢色狼，select distinct tdate  from tb_a  where tdate  between @dt1 and @dt2 这里如果想根据tdate字段排序应该怎么写呢？

Answer 4

select distinct tdate from tb_a where tdate between @dt1 and @dt2 order by tdate

Answer 5

在存储过程中会出错的
除非同时指定了 TOP，否则 ORDER BY 子句在视图、内嵌函数、派生表和子查询中无效。

Answer 6

select tdate from tb_a where tdate between @dt1 and @dt2 
group by tdate order by tdate

Answer 7

经测试，树哥的能满足要求，色狼的再求平均值的时候又问题
树哥，我想在上面的那个存储过程中加个按tdate排序的语句，应该怎么加啊？

Answer 8

try
CREATE  PROC  PROC_tb_a
@dt1 varchar(10) , @dt2 varchar(10)
AS
begin
declare @sql varchar(8000)
set @sql = 'select ph '
select @sql = @sql + ' ,  max(case tdate when '''  + tdate +  ''' then qty  else 0 end) [' + tdate + ']'
from (select top 1000 tdate from(select distinct tdate  from tb_a  where tdate  between @dt1 and @dt2) t order by tdate) as a
set @sql = @sql + ',cast(sum(qty)*1.0/(select count(distinct tdate) from tb_a where left(tdate,6) between '''+@dt1+ ''' and '''+ @dt2+ ''') as decimal(18,2)) 平均用量  from  tb_a  where left(tdate,6)  between ''' + @dt1 + ''' and ''' +  @dt2 + ''' group by ph'
exec(@sql) 
end
GO

Answer 9

alter  PROC  PROC_tb_a
@dt1 varchar(6) , @dt2 varchar(6)
AS
begin
declare @sql varchar(8000)
set @sql = 'select ph '
select @sql = @sql + ' ,  max(case tdate when '''  + tdate +  ''' then qty  else 0 end) [' + tdate + ']'
from tb_a  where tdate  between @dt1 and @dt2 group by tdate order by tdate
set @sql = @sql + ',cast(avg(qty*1.0) as decimal(18,2)) 平均用量  from  tb_a  
      where tdate between ''' + @dt1 + ''' and ''' + @dt2 + ''' group by ph'
exec(@sql) 
end
GOexec PROC_tb_a '201101','201105'