select a.月份,a.员工编号,(select top 1 部门编号 from gztable where 员工编号=a.员工编号 and 月份=a.月份 order by 月份) as 部门编号,a.工资, (select 员工编号,sum(工资) as 工资,max(月份) as 月份 from gztable where 月份 between '2005/01' and '2005/12' group by 员工编号)a
declare @t table(m varchar(20),b varchar(10),s varchar(10),g decimal(10,2)) insert into @t select '2005/01','001','01',1243.34 union all select '2005/01','004','01',783.78 union all select '2005/02','001','01',786.34 union all select '2005/03','001','09',7824.234 select 员工编号=c.b,部门编号=c.s,工资总计=sum(t.g),工资平均=avg(t.g) from @t t,(select b,max(s) as s from @t a group by a.b) c where t.b=c.b and t.m between '2005/01' and '2005/12' group by c.b,c.s /*员工编号 部门编号 工资总计 工资平均平 ---------- ---------- ---------------------------------------- ---------------------- 001 09 9853.91 3284.636666 004 01 783.78 783.780000 */(所影响的行数为 2 行)
受楼上兄弟们的启示做了下修改我想这回是楼主想要的结果了吧 declare @t table(m varchar(20),b varchar(10),s varchar(10),g decimal(10,2)) insert into @t select '2005/01','001','01',1243.34 union all select '2005/01','004','01',783.78 union all select '2005/02','001','01',786.34 union all select '2005/03','001','09',7824.234 select t.b as 员工编号,t.s as 部门编号 ,a.aa as 总工资,a.bb as 平均工资 from @t t, ( select avg(x.g)as bb,sum(x.g)as aa,x.b as b,max(m)as c from @t x where x.m between '2005/01'and'2005/12' group by x.b )a where a.b=t.b and t.m=a.c
declare @ table (month char(20), yno char(10), depart char(10), fax money) insert into @ select '2005-01','001','09',1243.34 union select '2005-01','004','01',783.78 union select '2005-02','001','01',786.34 union select '2005-03','001','01',7824.234 select a.yno as 员工编号,a.depart 部门编号,x.z 工资总和 from @ a join (select yno,max(month) c,sum(fax) z from @ where month between'2005-01'and'2005-03' group by yno)x on x.c=a.month and x.yno=a.yno
create table #gztable ([month] datetime,[emNum] varchar(100),[depNum] varchar(100),[salary] money) insert into #gztable select '01/01/2005','001','01',1243.34 insert into #gztable select '01/01/2005','004','01',783.78 insert into #gztable select '02/01/2005','001','01',786.34 insert into #gztable select '03/01/2005','001','09',7824.234select emNum,max([month]) As [month],sum(salary) as salary,max(depnum) AS depnum,avg(salary) AS avgmoney from #gztable where month between'01/01/2004'and'01/01/2006' group by emNum
insert into @t select '2005/01','001','01',1243.34
union all select '2005/01','004','01',783.78
union all select '2005/02','001','01',786.34
union all select '2005/03','001','09',7824.234
select 员工编号=c.b,部门编号=c.s,工资总计=sum(t.g),工资平均=avg(t.g)
from @t t,(select b,max(s) as s from @t a group by a.b) c
where t.b=c.b and t.m between '2005/01' and '2005/12'
group by c.b,c.s
/*员工编号 部门编号 工资总计 工资平均平
---------- ---------- ---------------------------------------- ----------------------
001 09 9853.91 3284.636666
004 01 783.78 783.780000 */(所影响的行数为 2 行)
declare @t table(m varchar(20),b varchar(10),s varchar(10),g decimal(10,2))
insert into @t select '2005/01','001','01',1243.34
union all select '2005/01','004','01',783.78
union all select '2005/02','001','01',786.34
union all select '2005/03','001','09',7824.234
select t.b as 员工编号,t.s as 部门编号 ,a.aa as 总工资,a.bb as 平均工资
from @t t,
(
select avg(x.g)as bb,sum(x.g)as aa,x.b as b,max(m)as c from @t x
where x.m between '2005/01'and'2005/12' group by x.b
)a
where a.b=t.b and t.m=a.c
(month char(20),
yno char(10),
depart char(10),
fax money)
insert into @
select '2005-01','001','09',1243.34 union
select '2005-01','004','01',783.78 union
select '2005-02','001','01',786.34 union
select '2005-03','001','01',7824.234
select a.yno as 员工编号,a.depart 部门编号,x.z 工资总和
from
@ a join (select yno,max(month) c,sum(fax) z
from @
where month between'2005-01'and'2005-03'
group by yno)x on x.c=a.month and x.yno=a.yno
insert into #gztable select '01/01/2005','001','01',1243.34
insert into #gztable select '01/01/2005','004','01',783.78
insert into #gztable select '02/01/2005','001','01',786.34
insert into #gztable select '03/01/2005','001','09',7824.234select emNum,max([month]) As [month],sum(salary) as salary,max(depnum) AS depnum,avg(salary) AS avgmoney
from #gztable
where month between'01/01/2004'and'01/01/2006'
group by emNum