SQL 獲取數據

SELECT

         COMPLAIN_CODE '投訴單號',

         ORDER_NO '訂單號',

         RESPONSIBILITY '責任單位名稱',

         CREATE_TIME '創建時間',

         COMPLAIN_TYPE '發起工單類型',

  DUTY_COMPLAIN_TYPE '定責投訴類型',

         COMPLAIN_SOUND_TYPE '聲音歸類',

         COMPLAIN_DATE '投訴日期',

         MEMO '情況說明',

  HANDLER1 "發起人工號",

  HANDLER2 "結束人工號",

  HANDLER_DATE1 "投訴開始時間",

  HANDLER_DATE2 "投訴結束時間",

         HANDLER_TYPE '處理類型',

         EOOR_SHIPPER_CONTACT '收貨方聯系人',

         EOOR_SHIPPER_CITY_NAME '發貨地址城市',

         EOOR_CONSIGNEE_CITY_NAME '收貨地址城市',

         EOOR_ORDER_TYPE '訂單類型',

         SEND_DATE '發貨時間',

         EOOR_SERVICE_MODE '服務類型',

         EOOS_SUPPLIER_FULL_NAME1'供應商全名',

         EOTS_LINK_TYPE '落地配'

FROM

         (

                   SELECT

                            t.COMPLAIN_CODE,

                            t.ORDER_NO,

                            t.RESPONSIBILITY,

                            t.CREATE_TIME,

                            CASE t.COMPLAIN_TYPE

                   WHEN 'SAFETY' THEN

                            '安全類'

                   WHEN 'CUSTOMER' THEN

                            '客戶類'

                   WHEN 'AGEING' THEN

                            '時效類'

                   WHEN 'ACTION' THEN

                            '客戶體驗類'

                   WHEN 'DELIVERY' THEN

                            '配送類'

                   ELSE

                            '貨代業務線上化'

                   END 'COMPLAIN_TYPE',

                   t.COMPLAIN_SOUND_TYPE,

                   t.COMPLAIN_DATE,

                   t.MEMO,

                   t.DUTY_COMPLAIN_TYPE,

                   b. HANDLER 'HANDLER1',

                   b.HANDLER_TYPE,

                   c.EOOR_SHIPPER_CONTACT,

                   c.EOOR_SHIPPER_CITY_NAME,

                   c.EOOR_CONSIGNEE_CITY_NAME,

                   c.EOOR_ORDER_TYPE,

                   c.SEND_DATE,

                   c.EOOR_SERVICE_MODE,

                   d.EOOS_SUPPLIER_FULL_NAME 'EOOS_SUPPLIER_FULL_NAME1',

    CASE e.EOTS_LINK_TYPE

    when '21' THEN '收貨'

                   when '22' THEN '配單'

                   when '23' THEN '發貨'

                   when '24' THEN '裝機'

                   when '51' THEN '提貨主單'

                   when '61' THEN '提貨訂單'

                   when '62' THEN '貨代簽收'

                   when '71' THEN '落地配交接'

                   when '72' THEN '落地配分配'

    ELSE

         '落地配簽收'

    END 'EOTS_LINK_TYPE',

                   min(b.HANDLER_DATE) 'HANDLER_DATE1'

         FROM

                   cs_complained_info t

         LEFT JOIN cs_complained_handle_record b ON b. CODE = t.COMPLAIN_CODE

         LEFT JOIN eo_order c ON c.EOOR_ORDER_NO = t.ORDER_NO

         LEFT JOIN eo_order_supplier d ON d.EOOS_ORDER_NO = t.ORDER_NO

         LEFT JOIN eo_order_task_status e ON e.EOOR_ORDER_NO = t.ORDER_NO

         WHERE

                   t.HANDLE_STATUS = "DONE"

         AND T.ORDER_NO != " "  

  AND t.CREATE_TIME > "2017-1-1"

         GROUP BY

                   t.COMPLAIN_CODE

         ) AS A

LEFT JOIN (

         SELECT

  t.COMPLAIN_CODE 'COMPLAIN_CODE2',

         b. HANDLER 'HANDLER2',

         max(b.HANDLER_DATE) 'HANDLER_DATE2'

FROM

         cs_complained_info t

LEFT JOIN cs_complained_handle_record b ON b. CODE = t.COMPLAIN_CODE

LEFT JOIN eo_order c ON c.EOOR_ORDER_NO = t.ORDER_NO

LEFT JOIN eo_order_supplier d ON d.EOOS_ORDER_NO = t.ORDER_NO

LEFT JOIN eo_order_task_status e ON e.EOOR_ORDER_NO = t.ORDER_NO

WHERE

         t.HANDLE_STATUS = "DONE"

AND b.HANDLER_RESULT = "FINISH"

AND T.ORDER_NO != " "

AND t.CREATE_TIME > "2017-1-1"

GROUP BY

         t.COMPLAIN_CODE

) AS B ON A.COMPLAIN_CODE = B.COMPLAIN_CODE2

ORDER BY COMPLAIN_CODE DESC