create table 员工档案表 ( id int ,名称 varchar(100) );
insert into 员工档案表
select 1,'张三' union all
select 2,'李四' union all
select 3,'王麻子'
go
create table 员工工资表( id int, riqi datetime , gz money)
insert into 员工工资表
select 1,'2008-05-01',$500求,这个员工2008年6分月,7月份12月份的累计收入,2009年5月份这个员工的同期收入。这是虚拟的测试数据,我们各个零售、批发行业的商务智能的
insert into 员工档案表
select 1,'张三' union all
select 2,'李四' union all
select 3,'王麻子'
go
create table 员工工资表( id int, riqi datetime , gz money)
insert into 员工工资表
select 1,'2008-05-01',$500求,这个员工2008年6分月,7月份12月份的累计收入,2009年5月份这个员工的同期收入。这是虚拟的测试数据,我们各个零售、批发行业的商务智能的
create table months(m varchar(7))
insert into months select '2007-01' unoin all select '2007-02' union all select '2007-03' union all --......一直到'2008-12'
go
select c.员工id,a.m as 月份,
isnull(b.工资,0) as 同期,
(select sum(isnull(工资,0)) from 员工工资表 where 月份<=a.m and 月份>='2007-01' and 员工id=c.员工id) as 同期累计,
(select isnull(工资,0) from 员工工资表 where 月份='2008-' + right(a.m,2) and 员工id=c.员工id) as 当期,
(select sum(isnull(b.工资),0) from 员工工资表 where 月份<='2008-' + right(a.m,2) and 月份>='2008-01' and 员工id=c.员工id) as 当期累计,
from months a left join 员工工资表 on a.m=b.月份 right join 员工档案表 c on b.员工id=c.员工id
where a.m like '2007-%' and b.月份
楼主试试这样是否还要运行十个小时.
--求每个员工每个月的同期累计和当期累计
create table months(m varchar(7))
insert into months select '2007-01' unoin all select '2007-02' union all select '2007-03' union all --......一直到'2008-12'
go
select a.m,c.员工id,
isnull(b.工资,0) as 同期,
(select sum(isnull(工资,0)) from 员工工资表 where 月份<=a.m and 月份>='2007-01' and 员工id=c.员工id) as 同期累计,
(select isnull(工资,0) from 员工工资表 where 月份='2008-' + right(a.m,2) and 员工id=c.员工id) as 当期,
(select sum(isnull(工资,0)) from 员工工资表 where 月份<='2008-' + right(a.m,2) and 月份>='2008-01' and 员工id=c.员工id) as 当期累计,
from months a left join 员工工资表 on a.m=b.月份 right join 员工档案表 c on b.员工id=c.员工id
where a.m like '2007-%' and b.月份
[code=SQL--求2008年每个员工每个月的同期累计和当期累计
create table months(m varchar(7),m1 varchar(7))
insert into months select '2008-01','2007-01' union all select '2008-02','2007-02' union all select '2008-03','2007-03' union all select '2008-04','2007-04' --......一直到'2008-12'
go
select c.员工id,c.员工名称,a.m as 月份,
(select isnull(工资,0) from 员工工资表 where 月份=a.m1 and 员工id=c.员工id) as 同期,
(select sum(isnull(工资,0)) from 员工工资表 where 月份<=a.m1 and 月份>='2007-01' and 员工id=c.员工id) as 同期累计,
isnull(b.工资,0) as 当期,
(select sum(isnull(工资,0)) from 员工工资表 where 月份<=a.m and 月份>='2008-01' and 员工id=c.员工id) as 当期累计
from months a left join 员工工资表 b on a.m=b.月份 right join 员工档案表 c on b.员工id=c.员工id
where a.m > '2007-12'
[/code]