首页 > 代码库 > ORACLE

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