日期:2014-05-16  浏览次数:20982 次

一个主表和两个子表汇总查询的问题
一张主表MAIN和两张子表(TEST1、TEST2)通过主表的ID和两子表的XIAOZU关联:
SQL code
create table MAIN
(
  ID   VARCHAR2(6),
  NAME VARCHAR2(6)
)
create table TEST1
(
  ID     VARCHAR2(6) ,
  MONEY  NUMBER,
  STATE  VARCHAR2(8),
  RIQI   VARCHAR2(10),
  XIAOZU VARCHAR2(6)
)
create table TEST2
(
  ID     VARCHAR2(6),
  MONEY  NUMBER,
  STATE  VARCHAR2(8),
  RIQI   VARCHAR2(10),
  XIAOZU VARCHAR2(6)
)
insert into main (ID, NAME) values ('1', '小组1');
insert into main (ID, NAME) values ('2', '小组2');
insert into main (ID, NAME) values ('3', '小组3');

insert into test1 (ID, MONEY, STATE, RIQI, XIAOZU) values ('1', 100, '已完成', '2012-02-10', '1');
insert into test1 (ID, MONEY, STATE, RIQI, XIAOZU) values ('2', 300, '处理中', '2012-03-1', '1');
insert into test1 (ID, MONEY, STATE, RIQI, XIAOZU) values ('3', 150, '已完成', '2012-02-15', '2');
insert into test1 (ID, MONEY, STATE, RIQI, XIAOZU) values ('4', 80, '已完成', '2012-02-23', '2');
insert into test1 (ID, MONEY, STATE, RIQI, XIAOZU) values ('5', 30, '已完成', '2012-03-23', '1');

insert into test2 (ID, MONEY, STATE, RIQI, XIAOZU) values ('1', 200, '已完成', '2012-02-5', '1');
insert into test2 (ID, MONEY, STATE, RIQI, XIAOZU) values ('2', 50, '处理中', '2012-05-26', '2');
insert into test2 (ID, MONEY, STATE, RIQI, XIAOZU) values ('3', 60, '已完成', '2012-03-7', '3');
insert into test2 (ID, MONEY, STATE, RIQI, XIAOZU) values ('4', 20, '处理中', '2012-02-17', '1');


现在我通过
SQL code
select t1.name 小组名称, substr(t2.riqi, 1, 7) 月份,
       count(decode(t2.state, '已完成', 1, '')) TEST1表已完成总条数,
       sum(decode(t2.state, '已完成', t2.money, 0)) TEST1表已完成总金额,
       count(decode(t2.state, '处理中', 1, '')) TEST1表处理中总条数,
       sum(decode(t2.state, '处理中', t2.money, 0)) TEST1表处理中总金额
       from main t1,test1 t2
       where t2.xiaozu = t1.id 
        group by t1.name,substr(t2.riqi, 1, 7)
        order by t1.name,substr(t2.riqi, 1, 7)

可以查询主表和一个子表的关联数据,现在的问题是主表和两个子表一起关联的数据,即下列格式的数据:
  小组名称 月份 子表1已完成总条数 子表1表已完成总金额 子表1表处理中总条数 子表1表处理中总金额 子表2表已完成总条数 子表2表已完成总金额 子表2表处理中总条数 子表2表处理中总金额
1 小组1 2012-02 1 100 0 0 1 200 1 20
2 小组1 2012-03 1 30 1 300 0 0 0 0
3 小组2 2012-02 2 230 0 0 0 0 1 50
4 小组3 2012-03 0 0 0 0 1 60 0 0


------解决方案--------------------
SQL code

--就是在原来sql的基础上,就一个子查询,同时把原来的排序问题也就解决了。
select 小组名称, 月份,
       sum(TEST1表已完成总条数),
       sum(TEST1表已完成总金额),
       sum(TEST1表处理中总条数),
       sum(TEST1表处理中总金额),
       sum(TEST2表已完成总条数),
       sum(TEST2表已完成总金额),
       sum(TEST2表处理中总条数),
       sum(TEST2表处理中总金额)
from (
select t1.name 小组名称, substr(t2.riqi, 1, 7) 月份,
       count(decode(t2.state, '已完成', 1, '')) TEST1表已完成总条数,
       sum(decode(t2.state, '已完成', t2.money, 0)) TEST1表已完成总金额,
       count(decode(t2.state, '处理中', 1, '')) TEST1表处理中总条数,
       sum(decode(t2.state, '处理中', t2.money, 0)) TEST1表处理中总金额,
       0 TEST2表已完成总条数,
       0 TEST2表已完成总金额,
       0 TEST2表处理中总条数,
       0 TEST2表处理中总金额
       from main t1,test1 t2
       where t2.xiaozu = t1.id 
        group by t1.name,substr(t2.riqi, 1, 7)

union all

select t1.name 小组名称, substr(t2.riqi, 1, 7) 月份,
       0 TEST1表已完成总条数,
       0 TEST1表已完成总金额,
       0 TEST1表处理中总条数,
       0 TEST1表处理中总金额,
       count(decode(t2.state, '已完成', 1, '')) TEST2表已完成总条数,
       sum(decode(t2.state, '已完成', t2.money, 0)) TEST2表已完成总金额,
       count(decode(t2.state, '处理中', 1, '')) TEST2表处理中总条数,
       sum(decode(t2.state, '处理中', t2.money, 0)) TEST2表处理中总金额
       from main t1,test2 t2
       where t2.xiaozu = t1.id 
        group by t1.name,substr(t2.riqi, 1, 7)
) 
group by 小组名称, 月份
order by 小组名称, 月份