select b.part , (select sum(amount) from tablename a where a.part =b.part and a.date between .. and .. ) as amount1 (select sum(amount) from tablename a where a.part =b.part and a.date between .. and .. ) as amount2 (select sum(amount) from tablename a where a.part =b.part and a.date between .. and .. ) as amount3 (select sum(amount) from tablename a where a.part =b.part and a.date between .. and .. ) as amount4from tablename bgroup by b.part
select 工程单位,sum(金额),to_char(日期,'yyyy/mm') as 月份 from table where 日期 between to_date('2002/03/01','yyyy/mm/dd') and to_date('2002/07/31','yyyy/mm/dd') group by 工程单位,to_char(日期,'yyyy/mm')------------------------------ 这样应该差不多了,只是Between之间的日期你可以用变量替代,不同的数据库,日期格式和函数可能稍微变动一下,我用的是Oracle。
---create table IF OBJECT_ID('dbo.project') IS NULL create table project (工程 char(30) not null, 日期 datetime not null, 金额 money) ---append data by yourselfdeclare @dstart datetime set @dstart='2002-3-1' declare @dend datetime set @dend='2002-7-31' declare @sqlt1 varchar(300) declare @sqlt2 varchar(300) declare @sql varchar(800) declare @sql1 varchar(120) declare @sql2 varchar(550) declare @mth intset @mth=month(@dstart) set @sql1='' set @sql2=''WHILE @mth<=month(@dend) BEGIN --- delete temporary table set @sqlt1='IF OBJECT_ID('''+'dbo.prj'+ltrim(str(@mth))+''') IS NOT NULL DROP TABLE dbo.prj'+ltrim(str(@mth)) exec(@sqlt1) --- create temporary table set @sqlt2='select 工程 ,sum(金额) as m'+ltrim(str(@mth))+' into prj'+ltrim(str(@mth))+' from project where 日期 between '''+rtrim(convert(char(30),@dstart))+''' AND '''+rtrim(convert(char(30),@dend))+''' and (month(日期)='+ltrim(str(@mth))+') group by 工程' exec(@sqlt2) set @sql1=@sql1+',prj'+ltrim(str(@mth))+'.m'+ltrim(str(@mth)) set @sql2=@sql2+' left outer join prj'+ltrim(str(@mth))+' on project.工程=prj'+ltrim(str(@mth))+'.工程' set @mth=@mth+1 ENDset @sql='select distinct project.工程'+@sql1+' from project'+@sql2 exec(@sql) ---以上程序在SQL2K下调试通过.
(select sum(amount) from tablename a where a.part =b.part and a.date between .. and .. ) as amount1
(select sum(amount) from tablename a where a.part =b.part and a.date between .. and .. ) as amount2
(select sum(amount) from tablename a where a.part =b.part and a.date between .. and .. ) as amount3
(select sum(amount) from tablename a where a.part =b.part and a.date between .. and .. ) as amount4from tablename bgroup by b.part
首先根据输入的上下限,算出中间有多少个月,然后你只用根据单位名和当月的上下限来算出工资总额,依次全部处理!!!!!!!!!
好比是2月的,最大不会超过31,不会小于1号,这样你可以根据单位名算出当月的工资呀,再在程序中作一些判断,加上SQL语句看行不行,这只是我的想法,说得不对,千万别见怪!!!!!!!!!!
from table
where 日期 between to_date('2002/03/01','yyyy/mm/dd')
and to_date('2002/07/31','yyyy/mm/dd')
group by 工程单位,to_char(日期,'yyyy/mm')------------------------------
这样应该差不多了,只是Between之间的日期你可以用变量替代,不同的数据库,日期格式和函数可能稍微变动一下,我用的是Oracle。
IF OBJECT_ID('dbo.project') IS NULL
create table project (工程 char(30) not null, 日期 datetime not null, 金额 money)
---append data by yourselfdeclare @dstart datetime
set @dstart='2002-3-1'
declare @dend datetime
set @dend='2002-7-31' declare @sqlt1 varchar(300)
declare @sqlt2 varchar(300)
declare @sql varchar(800)
declare @sql1 varchar(120)
declare @sql2 varchar(550)
declare @mth intset @mth=month(@dstart)
set @sql1=''
set @sql2=''WHILE @mth<=month(@dend)
BEGIN
--- delete temporary table
set @sqlt1='IF OBJECT_ID('''+'dbo.prj'+ltrim(str(@mth))+''') IS NOT NULL DROP TABLE dbo.prj'+ltrim(str(@mth))
exec(@sqlt1)
--- create temporary table
set @sqlt2='select 工程 ,sum(金额) as m'+ltrim(str(@mth))+' into prj'+ltrim(str(@mth))+' from project where 日期 between '''+rtrim(convert(char(30),@dstart))+''' AND '''+rtrim(convert(char(30),@dend))+''' and (month(日期)='+ltrim(str(@mth))+') group by 工程'
exec(@sqlt2)
set @sql1=@sql1+',prj'+ltrim(str(@mth))+'.m'+ltrim(str(@mth))
set @sql2=@sql2+' left outer join prj'+ltrim(str(@mth))+' on project.工程=prj'+ltrim(str(@mth))+'.工程'
set @mth=@mth+1
ENDset @sql='select distinct project.工程'+@sql1+' from project'+@sql2
exec(@sql)
---以上程序在SQL2K下调试通过.