SQL puzzles and answers读书笔记——预算执行问题

背景描述：

假设有数据库中有两张表，记录了预算花费与实际花费。

表一：

create table Budgeted

(

task int not null primary key,

category int not null,

est_cost decimal(8,2) not null

);

task: 项目编号

category: 项目类别

est_cost: 估计费用

样例数据：

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查询来满足上述需求？

 

解决方案：

所有结果在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

select 
    B.category,
    (select SUM(est_cost) from Budgeted where category = B.category) est_cost,
    SUM(act_cost) act_cost
from 
    Budgeted B
    inner join
    Actual
    on
        B.task = Actual.task
group by
    B.category;