一个主表和两个子表汇总查询的问题

Question

一张主表MAIN和两张子表（TEST1、TEST2）通过主表的ID和两子表的XIAOZU关联：
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');现在我通过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

Answer 1

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)
        order by t1.name,substr(t2.riqi, 1, 7)union allselect 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)
        order by t1.name,substr(t2.riqi, 1, 7)

Answer 2

执行起来出现“”ORA-00933：SQL命令未正确结束

Answer 3

把union all的两个sql语句分别执行有问题吗？
如果分开执行可以的话，union all以后，把order by去掉，加到最后，
或者在每个sql的外面套一个子查询都可以

Answer 4

分开执行没问题，总体把两个order by去掉后可以执行，但结果不对，怎样写有个问题，不会行内合并，比如同个小组同一个月的数据会产生两行数据而不是前阶段有值后阶段也有值。

Answer 5

在外面加一个子查询
select 小组名称 月份 sum(子表1已完成总条数), sum(子表1表已完成总金额),
sum(子表1表处理中总条数),
...
sum(子表2表处理中总金额)
from (
sql1 union all sql2
)
group by 小组名称, 月份
order by xxxxx就可以了

Answer 6

--就是在原来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 allselect 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 小组名称, 月份