ORACLE

WITH date_info

     AS (SELECT TO_DATE (‘2017-03-07‘, ‘yyyy-mm-dd‘) AS start_date,                   TO_DATE (‘2017-03-14‘, ‘yyyy-mm-dd‘)                 - TO_DATE (‘2017-03-07‘, ‘yyyy-mm-dd‘)                 + 1                    AS dt            FROM DUAL),      date_group      AS (    SELECT start_date + (LEVEL - 1) AS start_date,                     start_date + LEVEL AS end_date                FROM date_info          CONNECT BY LEVEL <= date_info.dt),      data_info      AS (SELECT *            FROM (SELECT t3.factory,                         t3.client,                         t3.real_value,                         NVL (t4.target_value, 0) target_value,                         t3.create_time                    FROM (  SELECT t.factory,                                   t.client,                                   SUM (t.real_value) real_value,                                   t.create_time                              FROM (SELECT factory,                                           client,                                           ROUND (                                              DECODE (person,                                                      0, 0,                                                      product_qty / person),                                              2)                                              real_value,                                           reamark,                                           create_time                                      FROM D9DATA_TABLE_DAY_UPPD) t                                   LEFT JOIN D9DATA_TABLE_MONTH_TARGET t2                                      ON     t.factory = t2.factory                                         AND t.client = t2.client                                         AND TO_CHAR (t.create_time, ‘yyyy-mm‘) =                                                TO_CHAR (                                                   TO_DATE (                                                         t2.year_info                                                      || ‘-‘                                                      || t2.month_info,                                                      ‘yyyy-mm‘),                                                   ‘yyyy-mm‘)                          GROUP BY t.factory,                                   t.client,                                   real_value,                                   t.create_time                          ORDER BY t.factory, t.client, t.create_time) t3                         LEFT JOIN D9DATA_TABLE_MONTH_TARGET t4                            ON     t4.factory = t3.factory                               AND t4.client = t3.client                               AND TO_CHAR (                                      TO_DATE (                                         t4.year_info || ‘-‘ || t4.month_info,                                         ‘yyyy-mm‘),                                      ‘yyyy-mm‘) =                                      TO_CHAR (t3.create_time, ‘yyyy-mm‘)) t5)   SELECT t5.start_date,          DECODE (t5.factory,                  1, ‘比亚迪第九事业部‘,                  6, ‘第六工厂‘,                  3, ‘第一工厂‘,                  5, ‘第五工厂‘,                  4, ‘第二工厂‘,                  49, ‘第七工厂‘,                  48, ‘第三工厂‘)             factory,          t5.client,          NVL (t5.target_value, 0),          NVL (t5.target_value1, 0),          NVL (t5.real_value, 0),          NVL (t5.balance, 0),          t5.rate     FROM (SELECT t.start_date,                  NVL (t1.factory, t.factory) factory,                  NVL (t1.client, t.client) client,                  t1.target_value,                  t1.target_value target_value1,                  t1.real_value,                  t1.real_value - t1.target_value balance,                  DECODE (                     NVL (t2.real_value, 0),                     0, 0,                     TO_CHAR (                        ROUND (                             (NVL (t1.real_value, 0) - NVL (t2.real_value, 0))                           / NVL (t2.real_value, 0)                           * 100,                           2),                        ‘fm99999999990.00‘))                     rate             FROM (SELECT t3.start_date, t4.factory, t4.client                     FROM date_group t3,                          (SELECT DISTINCT factory, client FROM data_info) t4) t                  LEFT JOIN data_info t1                     ON     t.start_date = t1.create_time                        AND t.factory = t1.factory                        AND t.client = t1.client                  LEFT JOIN data_info t2                     ON     t.start_date - 1 = t2.create_time                        AND t1.factory = t2.factory                        AND t1.client = t2.client) t5    WHERE 1 = 1 ORDER BY t5.factory, t5.client, t5.start_date  

ORACLE

标签：table   end   tin   balance   rac   tab   acl   creat   level