两个查询结果集,连接问题!!

[ivanho523314]

这是第一个结果集:

select l.类型, avg(l.tt) as 平均历经部门
  from (select w.name as 类型,
               nvl(o.test5, '0') as tt
          from Wechat_content_type w
          left outer join 
          (select content_type, 
                  count(department) as test5
                            from ordersview
                           where STATUS_ORDER = '00'
                             and ILLEGALITEMSTIME >=
                                 to_date('2015-09-01 00:00:00',
                                         'yyyy-MM-dd HH24:mi:ss')
                             and ILLEGALITEMSTIME <=
                                 to_date('2015-10-31 23:59:59',
                                         'yyyy-MM-dd HH24:mi:ss')
                           group by orderid, content_type) o 
                           on w.value =o.content_type
                           order by w.sort 
       ) l
 group by l.类型

结果:

规划不合理 2.91666666666667
违停 0
闯红灯 3
僵尸车 2.12844036697248
其他举报类 2.61538461538462
其他咨询类 2
不按车道行驶 0
设施维护（红绿灯） 2

select w.name as 类型,
         nvl(o.test0, '0') as 归档总量,
         nvl(o.test1, '0') as 最长历时,
         nvl(o.test2, '0') as 最短历时,
         nvl(o.test3, '0') as 平均历时
    from Wechat_content_type w
    left outer join (select content_type,
                           count(1) as test0,
                           max(to_date(op_time, 'yyyy-MM-dd HH24:mi:ss') -
                               illegalitemstime) as test1,
                           min(to_date(op_time, 'yyyy-MM-dd HH24:mi:ss') -
                               illegalitemstime) as test2,
                           avg(to_date(op_time, 'yyyy-MM-dd HH24:mi:ss') -
                               illegalitemstime) as test3
                    
                      from ordersview o
                     where STATUS_ORDER = '00'
                       and ILLEGALITEMSTIME >=
                           to_date('2015-09-01 00:00:00',
                                   'yyyy-MM-dd HH24:mi:ss')
                       and ILLEGALITEMSTIME <=
                           to_date('2015-10-31 23:59:59',
                                   'yyyy-MM-dd HH24:mi:ss')
                     group by content_type) o on w.value = o.content_type
   order by w.sort

结果:
设施维护（红绿灯） 4 20.4949305555556 0.939386574074074 10.7180700231481
道路规划不合理 35 13.0098148148148 0.0303240740740741 4.03755952380952
其他咨询类 4 8.84751157407407 0.204710648148148 4.38991319444444
僵尸车 232 27.8287847222222 0.00337962962962963 6.57314520474138
违停 0 0 0 0
不按车道行驶 0 0 0 0
闯红灯 6 13.9010763888889 0.00475694444444444 6.69865162037037
其他举报类 34 13.9512152777778 0.00356481481481481 3.59071010348584



现在想将两个集合为一个集,说白了就是把上边以类型相等为条件,把数值加列到第二个数据集里,但小弟不懂,望各位帮帮忙.

[beyon2008]

就是把两个结果集join： select zz.类型, zz.平均历经部门, yy.归档总量, yy.最长历时, yy.最短历时, yy.平均历时 from (select l.类型, avg(l.tt) as 平均历经部门 from (select w.name as 类型, nvl(o.test5, '0') as tt from Wechat_content_type w left outer join (select content_type, count(department) as test5 from ordersview where STATUS_ORDER = '00' and ILLEGALITEMSTIME >= to_date('2015-09-01 00:00:00', 'yyyy-MM-dd HH24:mi:ss') and ILLEGALITEMSTIME <= to_date('2015-10-31 23:59:59', 'yyyy-MM-dd HH24:mi:ss') group by orderid, content_type) o on w.value = o.content_type order by w.sort) l group by l.类型) zz inner join (select w.name as 类型, nvl(o.test0, '0') as 归档总量, nvl(o.test1, '0') as 最长历时, nvl(o.test2, '0') as 最短历时, nvl(o.test3, '0') as 平均历时, w.sort from Wechat_content_type w left outer join (select content_type, count(1) as test0, max(to_date(op_time, 'yyyy-MM-dd HH24:mi:ss') - illegalitemstime) as test1, min(to_date(op_time, 'yyyy-MM-dd HH24:mi:ss') - illegalitemstime) as test2, avg(to_date(op_time, 'yyyy-MM-dd HH24:mi:ss') - illegalitemstime) as test3 from ordersview o where STATUS_ORDER = '00' and ILLEGALITEMSTIME >= to_date('2015-09-01 00:00:00', 'yyyy-MM-dd HH24:mi:ss') and ILLEGALITEMSTIME <= to_date('2015-10-31 23:59:59', 'yyyy-MM-dd HH24:mi:ss') group by content_type) o on w.value = o.content_type) yy on zz.类型 = yy.类型 order by yy.sort

[Rotel-刘志东]

思路：union all 然后再sum() group by 相同分组求和

[Rotel-刘志东]

数据列不相同不能union all