需要同时取多行数据，把ireport填报数据抽到MYSQL数据库里

#将《L1_HOTEL_ROOM_INFO酒店房间基础信息》按日志表更新历史数据失效
UPDATE 
  DISNEY_EDW.L1_HOTEL_ROOM_INFO C,
  (SELECT DISTINCT 
    A.BBQ_ 
  FROM
    IRPT.ES10_LOG A 
  WHERE A.OPERNAME_ = '上报数据' 
    AND A.USERID_ = 'DISNEY' 
    AND TASKCAPTION_ = '酒店房间基础信息' 
    AND A.LOGDATE_ > (SELECT MAX(MAX_LOG_DATE) FROM disney_edw.L1_IRPT_MAXTIME_LOG WHERE REPORT_NAME = '酒店基础信息')) B 
SET
  C.IS_VALID = 0,
  C.LAST_UPDATE_TIME = NOW() 
WHERE C.BBQ = LEFT(B.BBQ_,4) AND C.`IS_VALID`=1;  








#将《L1_HOTEL_ROOM_INFO酒店房间基础信息》按日志表插入新的数据
INSERT INTO  DISNEY_EDW.L1_HOTEL_ROOM_INFO
SELECT   NULL AS A,BBQ_ AS B,
         '花园景观房' AS C,B3 AS D,C3 AS E,'H1/迪士尼乐园酒店' AS F,
         1 AS G,'ETL' AS H,NOW() AS I,'ETL' AS J,NOW() AS K
FROM irpt.IJDXX_HOTEL_ROOM WHERE BBQ_ IN 
(SELECT DISTINCT 
  BBQ_ 
FROM
  irpt.es10_log a 
WHERE a.OPERNAME_ = '上报数据' 
  AND a.USERID_ = 'disney' 
  AND TASKCAPTION_ = '酒店房间基础信息' 
  AND a.LOGDATE_ > (SELECT MAX(MAX_LOG_DATE) FROM disney_edw.L1_IRPT_MAXTIME_LOG WHERE REPORT_NAME = '酒店房间基础信息')) 
    
 UNION ALL
 
SELECT   NULL AS A,BBQ_ AS B,
         '湖景房' AS C,B4 AS D,C4 AS E,'H1/迪士尼乐园酒店' AS F,
         1 AS G,'ETL' AS H,NOW() AS I,'ETL' AS J,NOW() AS K
FROM irpt.IJDXX_HOTEL_ROOM WHERE BBQ_ IN 
(SELECT DISTINCT 
  BBQ_ 
FROM
  irpt.es10_log a 
WHERE a.OPERNAME_ = '上报数据' 
  AND a.USERID_ = 'disney' 
  AND TASKCAPTION_ = '酒店房间基础信息' 
  AND a.LOGDATE_ > (SELECT MAX(MAX_LOG_DATE) FROM disney_edw.L1_IRPT_MAXTIME_LOG WHERE REPORT_NAME = '酒店房间基础信息')) 
  
 UNION ALL
 
SELECT   NULL AS A,BBQ_ AS B,
         '主题乐园景观房' AS C,B5 AS D,C5 AS E,'H1/迪士尼乐园酒店' AS F,
         1 AS G,'ETL' AS H,NOW() AS I,'ETL' AS J,NOW() AS K
FROM irpt.IJDXX_HOTEL_ROOM WHERE BBQ_ IN 
(SELECT DISTINCT 
  BBQ_ 
FROM
  irpt.es10_log a 
WHERE a.OPERNAME_ = '上报数据' 
  AND a.USERID_ = 'disney' 
  AND TASKCAPTION_ = '酒店房间基础信息' 
  AND a.LOGDATE_ > (SELECT MAX(MAX_LOG_DATE) FROM disney_edw.L1_IRPT_MAXTIME_LOG WHERE REPORT_NAME = '酒店房间基础信息')) 
  
   UNION ALL
 
SELECT   NULL AS A,BBQ_ AS B,
         '行政层房' AS C,B6 AS D,C6 AS E,'H1/迪士尼乐园酒店' AS F,
         1 AS G,'ETL' AS H,NOW() AS I,'ETL' AS J,NOW() AS K
FROM irpt.IJDXX_HOTEL_ROOM WHERE BBQ_ IN 
(SELECT DISTINCT 
  BBQ_ 
FROM
  irpt.es10_log a 
WHERE a.OPERNAME_ = '上报数据' 
  AND a.USERID_ = 'disney' 
  AND TASKCAPTION_ = '酒店房间基础信息' 
  AND a.LOGDATE_ > (SELECT MAX(MAX_LOG_DATE) FROM disney_edw.L1_IRPT_MAXTIME_LOG WHERE REPORT_NAME = '酒店房间基础信息')) 
 
  UNION ALL
 
SELECT   NULL AS A,BBQ_ AS B,
         '神奇王国套房' AS C,B7 AS D,C7 AS E,'H1/迪士尼乐园酒店' AS F,
         1 AS G,'ETL' AS H,NOW() AS I,'ETL' AS J,NOW() AS K
FROM irpt.IJDXX_HOTEL_ROOM WHERE BBQ_ IN 
(SELECT DISTINCT 
  BBQ_ 
FROM
  irpt.es10_log a 
WHERE a.OPERNAME_ = '上报数据' 
  AND a.USERID_ = 'disney' 
  AND TASKCAPTION_ = '酒店房间基础信息' 
  AND a.LOGDATE_ > (SELECT MAX(MAX_LOG_DATE) FROM disney_edw.L1_IRPT_MAXTIME_LOG WHERE REPORT_NAME = '酒店房间基础信息')) 


  UNION ALL
 
SELECT   NULL AS A,BBQ_ AS B,
         '幻想曲套房' AS C,B8 AS D,C8 AS E,'H1/迪士尼乐园酒店' AS F,
         1 AS G,'ETL' AS H,NOW() AS I,'ETL' AS J,NOW() AS K
FROM irpt.IJDXX_HOTEL_ROOM WHERE BBQ_ IN 
(SELECT DISTINCT 
  BBQ_ 
FROM
  irpt.es10_log a 
WHERE a.OPERNAME_ = '上报数据' 
  AND a.USERID_ = 'disney' 
  AND TASKCAPTION_ = '酒店房间基础信息' 
  AND a.LOGDATE_ > (SELECT MAX(MAX_LOG_DATE) FROM disney_edw.L1_IRPT_MAXTIME_LOG WHERE REPORT_NAME = '酒店房间基础信息')) 
 
  UNION ALL
 
SELECT   NULL AS A,BBQ_ AS B,
         '魔法师套房' AS C,B9 AS D,C9 AS E,'H1/迪士尼乐园酒店' AS F,
         1 AS G,'ETL' AS H,NOW() AS I,'ETL' AS J,NOW() AS K
FROM irpt.IJDXX_HOTEL_ROOM WHERE BBQ_ IN 
(SELECT DISTINCT 
  BBQ_ 
FROM
  irpt.es10_log a 
WHERE a.OPERNAME_ = '上报数据' 
  AND a.USERID_ = 'disney' 
  AND TASKCAPTION_ = '酒店房间基础信息' 
  AND a.LOGDATE_ > (SELECT MAX(MAX_LOG_DATE) FROM disney_edw.L1_IRPT_MAXTIME_LOG WHERE REPORT_NAME = '酒店房间基础信息')) 
 
 UNION ALL
 
SELECT   NULL AS A,BBQ_ AS B,
         '花园景观房' AS C,B10 AS D,C10 AS E,'H2/玩具总动员酒店' AS F,
         1 AS G,'ETL' AS H,NOW() AS I,'ETL' AS J,NOW() AS K
FROM irpt.IJDXX_HOTEL_ROOM WHERE BBQ_ IN 
(SELECT DISTINCT 
  BBQ_ 
FROM
  irpt.es10_log a 
WHERE a.OPERNAME_ = '上报数据' 
  AND a.USERID_ = 'disney' 
  AND TASKCAPTION_ = '酒店房间基础信息' 
  AND a.LOGDATE_ > (SELECT MAX(MAX_LOG_DATE) FROM disney_edw.L1_IRPT_MAXTIME_LOG WHERE REPORT_NAME = '酒店房间基础信息')) 
   
  UNION ALL
 
SELECT   NULL AS A,BBQ_ AS B,
         '庭院景观房(胡迪庭院)' AS C,B11 AS D,C11 AS E,'H2/玩具总动员酒店' AS F,
         1 AS G,'ETL' AS H,NOW() AS I,'ETL' AS J,NOW() AS K
FROM irpt.IJDXX_HOTEL_ROOM WHERE BBQ_ IN 
(SELECT DISTINCT 
  BBQ_ 
FROM
  irpt.es10_log a 
WHERE a.OPERNAME_ = '上报数据' 
  AND a.USERID_ = 'disney' 
  AND TASKCAPTION_ = '酒店房间基础信息' 
  AND a.LOGDATE_ > (SELECT MAX(MAX_LOG_DATE) FROM disney_edw.L1_IRPT_MAXTIME_LOG WHERE REPORT_NAME = '酒店房间基础信息')) 
   
  UNION ALL
 
SELECT   NULL AS A,BBQ_ AS B,
         '庭院景观房(巴斯庭院)' AS C,B12 AS D,C12 AS E,'H2/玩具总动员酒店' AS F,
         1 AS G,'ETL' AS H,NOW() AS I,'ETL' AS J,NOW() AS K
FROM irpt.IJDXX_HOTEL_ROOM WHERE BBQ_ IN 
(SELECT DISTINCT 
  BBQ_ 
FROM
  irpt.es10_log a 
WHERE a.OPERNAME_ = '上报数据' 
  AND a.USERID_ = 'disney' 
  AND TASKCAPTION_ = '酒店房间基础信息' 
  AND a.LOGDATE_ > (SELECT MAX(MAX_LOG_DATE) FROM disney_edw.L1_IRPT_MAXTIME_LOG WHERE REPORT_NAME = '酒店房间基础信息')) 
       
  UNION ALL
 
SELECT   NULL AS A,BBQ_ AS B,
         '主题乐园景观房' AS C,B13 AS D,C13 AS E,'H2/玩具总动员酒店' AS F,
         1 AS G,'ETL' AS H,NOW() AS I,'ETL' AS J,NOW() AS K
FROM irpt.IJDXX_HOTEL_ROOM WHERE BBQ_ IN 
(SELECT DISTINCT 
  BBQ_ 
FROM
  irpt.es10_log a 
WHERE a.OPERNAME_ = '上报数据' 
  AND a.USERID_ = 'disney' 
  AND TASKCAPTION_ = '酒店房间基础信息' 
  AND a.LOGDATE_ > (SELECT MAX(MAX_LOG_DATE) FROM disney_edw.L1_IRPT_MAXTIME_LOG WHERE REPORT_NAME = '酒店房间基础信息')) ;
    








 


  #《L1_HOTEL_ROOM_INFO酒店房间基础信息》插入抽取最大时间日志表   
INSERT INTO  DISNEY_EDW.L1_IRPT_MAXTIME_LOG
  SELECT  NULL,
        '酒店房间基础信息' AS REPORT_NAME,
        'IJDXX_HOTEL_ROOM' AS TABLE_NAME,
        IFNULL(MAX(A.`LOGDATE_`),'2017-01-01 00:00:01')AS  max_log_date,
        'ETL' AS CREATE_USER,
         NOW() AS CREATE_TIME,
        'ETL' AS LAST_UPDATE_USER,
         NOW() AS LAST_UPDATE_TIME
FROM
  irpt.`es10_log` a 
WHERE a.`OPERNAME_` = '上报数据' 
  AND a.`USERID_` = 'disney' 
  AND TASKCAPTION_ = '酒店房间基础信息' ;
  
  #《L1_HOTEL_ROOM_INFO酒店房间基础信息》清除历史脏数据，日报,保留3天的历史数据
DELETE FROM disney_edw.`L1_HOTEL_ROOM_INFO` WHERE is_valid =0 AND last_update_time<= SUBDATE(NOW(),4) ;