背景描述：

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

表一：

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.spentfrom    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 spentfrom    Budgeted b1group 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 spentfrom    union_costgroup 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_costfrom    Budgeted    inner join    TaskSum    on        Budgeted.task = TaskSum.taskgroup by    Budgeted.category;

方案5

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