SQL puzzles and answers读书笔记——预算执行问题
背景描述： www.2cto.com
假设有
数据库中有两张表，记录了预算花费与实际花费。
表一：
create table Budgeted
(
task int not null primary key,
category int not null,
est_cost decimal(8,2) not null
);
task: 项目编号
category: 项目类别
est_cost: 估计费用
样例数据： www.2cto.com
Budgeted
task category est_cost
======================
1 9100 100.00
2 9100 15.00
3 9100 6.00
4 9200 8.00
5 9200 11.00
表二：
create table Actual
(
voucher int not null primary key,
task int not null references Budgeted(task),
act_cost decimal(8,2) not null
);
voucher: 凭证编号
task: 项目编号
act_cost: 实际费用
样例数据：
Actual
voucher task act_cost
======================
1 1 10.00
2 1 20.00
3 1 15.00
4 2 32.00
5 4 8.00
6 5 3.00
7 5 4.00
查询目标：
展示每一category的预算总额与实际总费用
样例数据结果：
category estimated spent
==========================
9100 121.00 77.00
9200 19.00 15.00
你能写出几种不同的SQL查询来满足上述需求？
www.2cto.com
解决方案：
所有结果在SQL Server 2008中测试通过
方案1
with estimate as (
select
category,
SUM(est_cost) as estimated
from
Budgeted
group by
category
),
spent as (
select
b.category,
SUM(a.act_cost) as spent
from
Actual a
join
Budgeted b
on
a.task = b.task
group by
b.category
)
select
e.category,
e.estimated,
s.spent
from
estimate e
join
spent s
on
e.category = s.category;
方案2
select
b1.category,
SUM(b1.est_cost) as estimated,
(select
SUM(act_cost)
from
Actual a
where
a.task in (select b2.task from Budgeted b2 where b2.category = b1.category)) as spent
from
Budgeted b1
group by
b1.category;
方案3
with union_cost as(
select
category,
est_cost,
0.0 as act_cost
from
Budgeted
union all
select
b.category,
0.0 as est_cost,
a.act_cost
from
Actual a
join
Budgeted b
on
a.task = b.task
)
select
category,
SUM(est_cost) as estimated,
SUM(act_cost) as spent
from
union_cost
group by
category;
方案4
With TaskSum as
(
select
task,
SUM(act_cost) act_cost
from
Actual
group by
task
)
select
Budgeted.category,
SUM(Budgeted.est_cost) est_cost,
SUM(TaskSum.act_cost) act_cost
from
Budgeted
inner join
TaskSum
on
Budgeted.task = TaskSum.task
group by
Budgeted.category;
方案5 www.2cto.com
select
B.category,
(select SUM(est_cost) from Budgeted where category = B.category) est_cost,
SUM(act_cost) act_cost
from
Budgeted B