又见一条牛叉SQL语句

龙上 发布于 2012/04/06 20:51
阅读 2K+
收藏 5
SQL

【开源中国 APP 全新上线】“动弹” 回归、集成大模型对话、畅读技术报告”

我当时就震惊了
SELECT aa.*,
         bb.fee_num_y fee_num_y,
         bb.fee_num_x fee_num_x,
         bb.fee_num_xy fee_num_xy
    FROM (  SELECT m.area_name,
                   m.sub_area_name,
                   SUM (m.total_num) total_num,
                   SUM (m.fee_num) fee_num,
                   SUM (m.nofee_num) nofee_num,
                   SUM (m.total_num_m) total_num_m,
                   SUM (m.total_num_n) total_num_n,
                   SUM (TOTAL_NUM_C) TOTAL_NUM_O,
                   SUM (TOTAL_NUM_S) TOTAL_NUM_S,
                   SUM (TOTAL_NUM_X) TOTAL_NUM_X,
                   SUM (TOTAL_NUM_CH) TOTAL_NUM_CH,
                   SUM (TOTAL_NUM_YH) TOTAL_NUM_YH,
                   SUM (m.plus_num) plus_num,
                   SUM (m.plus_add_num) plus_add_num,
                   SUM (m.increase_num) increase_num,
                   SUM (m.total_num_y1) TOTAL_NUM_Y1,
                   SUM (m.TOTAL_NUM_Y2) TOTAL_NUM_Y2,
                   SUM (m.TOTAL_NUM_Y3) TOTAL_NUM_Y3,
                   SUM (m.TOTAL_NUM_X1) TOTAL_NUM_X1,
                   SUM (m.TOTAL_NUM_X2) TOTAL_NUM_X2,
                   SUM (m.TOTAL_NUM_X3) TOTAL_NUM_X3,
                   SUM (m.TOTAL_NUM_X4) TOTAL_NUM_X4,
                   SUM (m.TOTAL_NUM_X5) TOTAL_NUM_X5,
                   SUM (m.TOTAL_NUM_X6) TOTAL_NUM_X6,
                   SUM (m.Total_Num_C1) Total_Num_C1,
                   SUM (m.Total_Num_C2) Total_Num_C2,
                   SUM (m.Total_Num_C3) Total_Num_C3,
                   NVL (SUM (m.FEE_NUM_Y1), 0) FEE_NUM_Y1,
                   NVL (SUM (m.FEE_NUM_Y2), 0) FEE_NUM_Y2,
                   NVL (SUM (m.FEE_NUM_Y3), 0) FEE_NUM_Y3,
                   NVL (SUM (m.FEE_NUM_X1), 0) FEE_NUM_X1,
                   NVL (SUM (m.FEE_NUM_X2), 0) FEE_NUM_X2,
                   NVL (SUM (m.FEE_NUM_X3), 0) FEE_NUM_X3,
                   NVL (SUM (m.FEE_NUM_X4), 0) FEE_NUM_X4,
                   NVL (SUM (m.FEE_NUM_X5), 0) FEE_NUM_X5,
                   NVL (SUM (m.FEE_NUM_X6), 0) FEE_NUM_X6,
                   NVL (SUM (m.FEE_NUM_C1), 0) FEE_Num_C1,
                   NVL (SUM (m.FEE_NUM_C2), 0) FEE_Num_C2,
                   NVL (SUM (m.FEE_NUM_C3), 0) FEE_Num_C3,
                   SUM (m.tea_num) tea_num,
                   SUM (m.con_num) con_num,
                   SUM (m.teab_num) teab_num,
                   SUM (m.doub_num) doub_num,
                   SUM (m.doub_num_fee) doub_num_fee,
                   ROUND (
                      SUM (m.doub_num_fee) * 100
                      / DECODE (SIGN (SUM (m.doub_num)),
                                1, SUM (m.doub_num),
                                0, 10000000000),
                      2)
                      doub_num_rate,
                   SUM (m.tea_num + m.con_num) in_sys,
                   SUM (m.fee_num - m.tea_num - m.con_num) out_sys,
                   SUM (m.msg_num) msg_num,
                   SUM (m.minus_num) minus_num,
                   SUM (m.minus_add_num) minus_add_num,
                   (CASE
                       WHEN SUM (m.fee_num + m.minus_num) > 0
                       THEN
                          ROUND (
                               SUM (m.minus_num)
                             * 100
                             / SUM (m.fee_num + m.minus_num),
                             2)
                       ELSE
                          0
                    END)
                      lost_num_rate,
                   NVL (sm.marketer_name, '--') marketer_name,
                   NVL (sm.mobile, '--') mobile
              FROM mms_o_org_report m, subarea_marketer_list sm
             WHERE     TYPE = 2
                   AND begin_time = TO_DATE ('20120401', 'yyyymmdd')
                   AND END_TIME = TO_DATE ('20120402', 'yyyymmdd')
                   AND m.area_name = sm.area_name(+)
                   AND m.sub_area_name = sm.sub_name(+)
                   AND sm.status(+) = 0
          GROUP BY sm.area_code,
                   m.area_name,
                   sm.sub_code,
                   m.sub_area_name,
                   NVL (sm.marketer_name, '--'),
                   NVL (sm.mobile, '--')) aa,
         (SELECT ac.area_name,
                 sa.area_name sub_area_name,
                 t1.fee_num_y,
                 t1.fee_num_x,
                 t1.fee_num_xy
            FROM (  SELECT t.area,
                           t.sub_area,
                           SUM (DECODE (t.TYPE, 6, t.counter, 0)) fee_num_y,
                           SUM (DECODE (t.TYPE, 1, t.counter, 0)) fee_num_x,
                           SUM (DECODE (t.TYPE, 2, t.counter, 0)) fee_num_xy
                      FROM (SELECT b.area,
                                   b.sub_area,
                                   b.TYPE,
                                   a.counter
                              FROM person_count a, organization b
                             WHERE a.TYPE = 5
                                   AND a.create_date =
                                          TO_DATE ('20120402', 'yyyymmdd')
                                   AND a.org_id = b.id
                                   AND b.TYPE IN (1, 6, 2)
                                   AND b.fee_status = 1) t
                  GROUP BY t.area, t.sub_area) t1,
                 area_code ac,
                 sub_area sa
           WHERE     t1.area = ac.area_code
                 AND t1.sub_area = sa.area_code
                 AND ac.area_code <> 371
          UNION
            SELECT t.area_name,
                   t.sub_area_name,
                   SUM (CASE WHEN t.TYPE = 6 THEN a.counter ELSE 0 END) fee_num_y,
                   SUM (CASE WHEN t.TYPE = 1 THEN a.counter ELSE 0 END) fee_num_x,
                   SUM (CASE WHEN t.TYPE = 2 THEN a.counter ELSE 0 END)
                      fee_num_xy
              FROM (SELECT a.area,
                           e.area_name,
                           a.id org_id,
                           NVL (c.sqjt_name, b.area_name) sub_area_name,
                           b.area_code,
                           a.TYPE
                      FROM organization a,
                           sub_area b,
                           sqjt_org c,
                           area_code e
                     WHERE     a.sub_area = b.area_code(+)
                           AND a.id = c.org_id(+)
                           AND a.TYPE IN (1, 6, 2)
                           AND a.fee_status = 1
                           AND a.area = e.area_code(+)
                           AND a.area = 371) t,
                   person_count a
             WHERE     a.TYPE = 5
                   AND a.org_id = t.org_id
                   AND a.create_date = TO_DATE ('20120402', 'yyyymmdd')
          GROUP BY t.area_name, t.sub_area_name) bb
   WHERE aa.area_name = bb.area_name(+)
         AND aa.sub_Area_name = bb.sub_Area_name(+)
ORDER BY aa.area_name, aa.sub_area_name


加载中
0
Ken5233
Ken5233
写了这么复杂 给谁看呢 。。。完全不考虑后续维护与业务变更 哈哈
0
无名人士
无名人士
这是什么样的需求呢
0
归海一刀
归海一刀
该评论暂时无法显示,详情咨询 QQ 群:点此入群
0
被风遗忘
被风遗忘
的确够长的.
0
狼狼A狗
狼狼A狗
oracle的!
0
bhhzd
bhhzd
这句只是长,看上去里面的union可以改成union all,楼主估计没有看到过一句100行左右的sql里面调用了几个package,而且他查的不是表,是视图,视图里面还要套视图
0
疯人院主任
疯人院主任
悲剧的我经常要写一些类似散文小说类sql语句
詹Eko
詹Eko
说不定是个韩寒在电脑前写代码勒
0
firstrose
firstrose
跟那个好几页A4的sql比简直弱爆了
0
Z.han
Z.han
该评论暂时无法显示,详情咨询 QQ 群:点此入群
OSCHINA
登录后可查看更多优质内容
返回顶部
顶部