现数据库中有表A,有列1 department(部门),列2 amount(产量),inputDate(日期)请问如何统计出以下数据:
department totalAmount(总产量) 22 23 24 25 26 。。1 2 3 。23
------------------------------------------------------------------------------------
001 600 10 10 10 10 10 。。10 10 10 10
002 1500 10 20 10 20 10 。。30 10 40 20具体就是统计上个月22日到本月23日的产量,出现日期产量为空时,用0填充
department totalAmount(总产量) 22 23 24 25 26 。。1 2 3 。23
------------------------------------------------------------------------------------
001 600 10 10 10 10 10 。。10 10 10 10
002 1500 10 20 10 20 10 。。30 10 40 20具体就是统计上个月22日到本月23日的产量,出现日期产量为空时,用0填充
declare @sql varchar(8000)
set @sql = 'select department,sum(amount) totalAmount '
select @sql = @sql + ' , sum(case inputDate when ''' + inputDate + ''' then amount else 0 end) [' + datename(dd,inputDate) + ']'
from (select distinct convert(varchar(10),inputDate,120) inputDate from tb where inputDate between '2009-12-22' and '2010-01-23') as a
set @sql = @sql + ' from tb group by department'
exec(@sql) --不固定写法,用getdate()
declare @sql varchar(8000)
set @sql = 'select department,sum(amount) totalAmount '
select @sql = @sql + ' , sum(case inputDate when ''' + inputDate + ''' then amount else 0 end) [' + datename(dd,inputDate) + ']'
from (select distinct convert(varchar(10),inputDate,120) inputDate from tb where inputDate between convert(varchar(8),dateadd(mm,-1,getdate()),120) + '22' and convert(varchar(8),getdate(),120) + '23') as a
set @sql = @sql + ' from tb group by department'
exec(@sql)
from A
group by department
where inputDate >=**** and inputDate<=***
set @sql = 'select department,sum(amount) totalAmount '
select
@sql = @sql + ' ,
sum(case inputDate when ''' + inputDate + ''' then amount else 0 end) [' + datename(dd,inputDate) + ']'
from
(select distinct convert(varchar(10),inputDate,120) inputDate from tb where inputDate between '2009-12-22' and '2010-01-23') as a
set @sql = @sql + ' from tb group by department'
exec(@sql)
if object_id('[tb]') is not null drop table [tb]
create table [tb]([department] varchar(3),[amount] int,[inputDate] datetime)
declare @i int
select @i = 1
while @i <= 30
begin
insert [tb]
select '001',10*(cast(rand()*4 as int)+1),dateadd(day,@i,'2009-11-25')
insert [tb]
select '002',10*(cast(rand()*4 as int)+1),dateadd(day,@i,'2009-11-25')
select @i=@i+1
enddeclare @begin datetime,@end datetime
select @begin='2009-12-22' ,@end='2010-01-23'
declare @sql varchar(8000)
select @sql = isnull(@sql+',
','')+'sum(case convert(varchar(10),inputDate,120) when '''+date+''' then amount else 0 end) as ['+date+']'
from
(
select convert(varchar(10),dateadd(day,number,@begin),120) as [date]
from master..spt_values
where type = 'P' and dateadd(day,number,@begin)<=@end
) tselect @sql = 'select department,sum(amount) as totalAmount,'+
@sql+'
from [tb] where inputDate between '''+ltrim(@begin)+''' and '''+ltrim(@end)+''' group by department'exec(@sql)
--------------------------------
001 120 40 10 40 30 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
002 80 10 20 10 40 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
如下试试:
declare @sdate datetime
declare @edate datetime
set @sdate = convert(varchar(8),dateadd(mm,-1,getdate()),120) + '22'
set @edate = convert(varchar(8),getdate(),120) + '23'declare @sql varchar(8000)
set @sql = 'select department,sum(amount) totalAmount '
select @sql = @sql + ' , sum(case inputDate when ''' + inputDate + ''' then amount else 0 end) [' + datename(dd,inputDate) + ']'
from (select convert(varchar(10),dateadd(dd,num,@sdate),120) inputDate from (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a where dateadd(dd,num,@sdate)<=@edate) as a
set @sql = @sql + ' from tb group by department'
exec(@sql)
where= 日期=Between(当前日期-1月) AND 当前日期23