存储过程求助

[wsxcdx]

create table tb_a 
(
   ph varchar(20),
   tdate  varchar(6),
   qty  numeric
)
GO

insert 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
GO

CREATE  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
GO

exec 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

[快溜]

create table tb_a 
(
   ph varchar(20),
   tdate  varchar(6),
   qty  numeric
)
GO

insert 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
GO

CREATE  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
GO

exec 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 行受影响)

[百年树人]

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 distinct tdate  from tb_a  where tdate  between @dt1 and @dt2 ) 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

exec PROC_tb_a '201101','201105' 

/**
ph                   201101                                  201102                                  201103                                  201104                                  201105                                  平均用量
-------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
aaaaaaaaa            100                                     200                                     0                                       100                                     0                                       80.00
bbbbbbb              100                                     0                                       200                                     100                                     400                                     160.00

(2 行受影响)
**/

[wsxcdx]

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

[快溜]

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

[wsxcdx]

[Quote=引用 4 楼 ssp2009 的回复:]

SQL code
select distinct tdate from tb_a where tdate between @dt1 and @dt2 order by tdate
[/Quote]
在存储过程中会出错的
除非同时指定了 TOP，否则 ORDER BY 子句在视图、内嵌函数、派生表和子查询中无效。

[快溜]

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

[wsxcdx]

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

[百年树人]

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

[wsxcdx]

OK
谢谢树哥，色狼，结帖了