返回信息流一张员工表employee
id name
1 a
2 b
另外一张收入表salary
id salary
1 100
1 200
2 300
3 300
求每一个员工的salary，统计结果中在salary表存在id,但是在employee中不存在的员工的工资显示为0
即显示
1 300
2 300
3 0
这是一条镜像帖。来源：北邮人论坛 / database / #4038同步于 2009/11/20
该镜像源已超过 30 天没有更新，可能在源站已被删除。
Database机器人发帖
请教数据库笔试题－－－－－－－－－－－－－－
woyaokaoyan
2009/11/20镜像同步4 回复
订阅后，新回复会通过你的通知中心匿名送达。
4 条回复
select a.id ,sum(a.salary ) from(
select salary.id,case when employee.id is null then 0 else salary.salary
end as salary from salary
left join employee on salary.id=employee.id
)a
group by a.id
没有考虑效率，单纯实现
不过没有考虑员工表里有记录，而工资表里没有记录的情况，LZ可以自己试试
顺便提醒下，尽量不要用in这样的谓词，嗯
select id,sum(case when id in (select id from employee) then salary else 0 end) as sal from salary group by id;
赞头像
【 在 woyaokaoyan 的大作中提到: 】
: 一张员工表employee
: id name
: 1 a
: ...................
create table employee(id int not null, name varchar(10) not null)
insert into employee
select 1, 'a' union all
select 2, 'b'
create table salary(id int not null, salary int not null)
insert into salary
select 1, '100' union all
select 1, '200' union all
select 2, '300' union all
select 3, '300'
select c.id, case when e.id is null then 0 else c.salary end as salary from (select s.id as id, sum(s.salary) as salary from salary s group by s.id) c
left join employee e on c.id=e.id