现数据库中有表A,字段1count(产量),字段2department(部门),inputDate(日期)我要统计出如下结构的报表:- ---------------------------------------------------------
| 产量
部门 总产量 |
| 23 24 25 26 27 28 29 30 31 1 2 。 22
----------------------------------------------------------
001 150 10 10 10 10 10 10 10 10 10 10 10 10
具体就是想查询上个月23 到本月22 的产量,根据月份不同,可能没有31日等,请问用sql 改怎么做,提供些思路也行,谢了
| 产量
部门 总产量 |
| 23 24 25 26 27 28 29 30 31 1 2 。 22
----------------------------------------------------------
001 150 10 10 10 10 10 10 10 10 10 10 10 10
具体就是想查询上个月23 到本月22 的产量,根据月份不同,可能没有31日等,请问用sql 改怎么做,提供些思路也行,谢了
部门 总产量 23 24 25 26 27 28 29 30 31 1 2 。 22
----------------------------------------------------------
001 150 10 10 10 10 10 10 10 10 10 10 10 . .... 10
,具体来说就是统计上个月23日到本月22日的产量,数据库中日期(天)没有的都用0填充,谢谢
部门 总产量 23 24 25 26 27 28 29 30 31 1 2 。 22
----------------------------------------------------------
001 150 10 10 10 10 10 10 10 10 10 10 10 . .... 10
,具体来说就是统计上个月23日到本月22日的产量,数据库中日期(天)没有的都用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)
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)
如下试试:
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)