求一SQL语句，高手们帮帮忙！！

[landy_shasha]

工资发放
员工姓名id 应发工资 已领工资 年 月
1 2000 4000 2006 10
2 2000 2000 2006 10
3 2000 0 2006 10
4 2000 4000 2006 10

1 2000 4000 2006 11
2 2000 2000 2006 11
3 2000 0 2006 11
4 2000 4000 2006 11

1 2000 2000 2006 12
2 2000 0 2006 12
3 2000 0 2006 12
4 2000 0 2006 12

1，4 在2006-12-10 领的工资是2006-10到2006-11的工资总和

领用记录
员工id 领取日期 领取金额 结算年月
2 2006-11-10 2000 2006.10--2006.10
4 2006-12-10 4000 2006.10--2006.11
2 2006-12-10 2000 2006.11--2006.11
1 2006-12-10 4000 2006.10--2006.11
1 2007-01-10 2000 2006.12--2006.12

现在我想统计200610----2006-12 的工资
得到这样的结果集
员工id 应发工资，已发工资，未发工资
1 6000 6000 0
2 6000 4000 2000
3 6000 0 6000
4 6000 4000 2000

[pao1uo]

seelct 员工姓名id,sum(应发工资) as 应发工资,sum(已发工资) as 已发工资,sum(应发工资)-sum(已发工资) as 未发工资
from (
select 员工姓名id,应发工资,0 as 已发工资
from 工资发放
where 年=2006
and 月 between 10 and 12
union all
select 员工姓名id,0 as 应发工资,领取金额 as 已发工资
from 领用记录
where 领取日期 between '2006-10-1' and '2006-12-31'
) as t
group by 员工姓名id

[冷箫轻笛]

select 员工id,sum(应发工资) as 应发工资,sum(已发工资) as 已发工资,sum(应发工资) - sum(已发工资) as 未发工资
from 工资发放表
where 年 = 2006 and 月 between 10 and 12
group by 员工id

PS: 楼主的名字好耳熟，以前见过吗？

[marco08]

create table A(员工姓名ID int, 应发工资 int, 已领工资 int, 年 int, 月 int )
insert A select 1, 2000, 4000, 2006, 10
union all select 2, 2000, 2000, 2006, 10
union all select 3, 2000, 0, 2006, 10
union all select 4, 2000, 4000, 2006, 10

union all select 1, 2000, 4000, 2006, 11
union all select 2, 2000, 2000, 2006, 11
union all select 3, 2000, 0, 2006, 11
union all select 4, 2000, 4000, 2006, 11

union all select 1, 2000, 2000, 2006, 12
union all select 2, 2000, 0, 2006, 12
union all select 3, 2000, 0, 2006, 12
union all select 4, 2000, 0, 2006, 12

create table B(员工ID int, 领取日期 datetime, 领取金额 int, 结算年月 varchar(20))
insert B select 2, '2006-11-10', 2000, '2006.10--2006.10'
union all select 4, '2006-12-10', 4000, '2006.10--2006.11'
union all select 2, '2006-12-10', 2000, '2006.11--2006.11'
union all select 1, '2006-12-10', 4000, '2006.10--2006.11'
union all select 1, '2007-01-10', 2000, '2006.12--2006.12'

select A.*, isnull(B.领取金额, 0) as 领取金额, A.应发工资-isnull(B.领取金额, 0) as 未发工资 from
(
select 员工姓名ID, sum(应发工资) as 应发工资
from A
where 年='2006' and 月 in (10,11,12)
group by 员工姓名ID
) A left join
(
select 员工ID, sum(领取金额) as 领取金额 from B
group by 员工ID
)B on A.员工姓名ID=B.员工ID

--result
员工姓名ID 应发工资 领取金额 未发工资
----------- ----------- ----------- -----------
1 6000 6000 0
2 6000 4000 2000
3 6000 0 6000
4 6000 4000 2000

(4 row(s) affected)

[xiaoku]

狼兄 又在套近乎？

[marco08]

--樓主, "结算年月"這個字段有什麽規則嗎？

[冷箫轻笛]

没有啊！

小手冰凉~~~~好像真的听过!

POP???

[冷箫轻笛]

我觉得楼主的查询跟 领用记录表 没有关系阿？？

谁解释一下阿？

[jackeyabc]

借用一下marco08(天道酬勤)的数据
create table A(员工姓名ID int, 应发工资 int, 已领工资 int, 年 int, 月 int )
insert A select 1, 2000, 4000, 2006, 10
union all select 2, 2000, 2000, 2006, 10
union all select 3, 2000, 0, 2006, 10
union all select 4, 2000, 4000, 2006, 10

union all select 1, 2000, 4000, 2006, 11
union all select 2, 2000, 2000, 2006, 11
union all select 3, 2000, 0, 2006, 11
union all select 4, 2000, 4000, 2006, 11

union all select 1, 2000, 2000, 2006, 12
union all select 2, 2000, 0, 2006, 12
union all select 3, 2000, 0, 2006, 12
union all select 4, 2000, 0, 2006, 12

create table B(员工ID int, 领取日期 datetime, 领取金额 int, 结算年月 varchar(20))
insert B select 2, '2006-11-10', 2000, '2006.10--2006.10'
union all select 4, '2006-12-10', 4000, '2006.10--2006.11'
union all select 2, '2006-12-10', 2000, '2006.11--2006.11'
union all select 1, '2006-12-10', 4000, '2006.10--2006.11'
union all select 1, '2007-01-10', 2000, '2006.12--2006.12'

select a.员工姓名id,a.应发工资,isnull(a.实发工资,0) as 实发工资,应发工资-isnull(a.实发工资,0) as 未发工资 from (
select * from(
select a.员工姓名ID,sum(应发工资) as 应发工资 from a group by a.员工姓名id) a
left join
(
select b.员工ID,sum(领取金额) as 实发工资 from b group by b.员工ID) b
on b.员工ID=a.员工姓名ID
)a

drop table a
drop table b

-------------------------------------

1 6000 6000 0
2 6000 4000 2000
3 6000 0 6000
4 6000 4000 2000

[landy_shasha]

各位大哥的都可以实现
谢谢
各位大哥
呵呵

[landy_shasha]

select 员工id,sum(应发工资) as 应发工资,sum(已发工资) as 已发工资,sum(应发工资) - sum(已发工资) as 未发工资
from 工资发放表
where 年 = 2006 and 月 between 10 and 12
group by 员工id

~~~~~~~~~~~~~~~~~~~~~~~~~~~
我原来也是这样写的，数额就超出了

[marco08]

解決問題了就好