各種MySQL混亂操作 ES iff with describe

選擇資料，插入另一表

INSERT INTO es_status_s (tourOperator, iff_objektlog, iff_mit_describe, iff_ohne_describe) 
VALUES ('JUM', (SELECT number FROM JUMpreview WHERE id LIKE '2'),
(SELECT number FROM JUMpreview WHERE id LIKE '7'), 
(SELECT number FROM JUMpreview WHERE id LIKE '6') );

插入另一表的統計資料

INSERT INTO JUMpreview 
SELECT '5', COUNT(DISTINCT leistungscodierung) , 'offer without describe' 
FROM JUMnoDescribe_accomCode;

##JUM
DROP TABLE IF EXISTS JUMpreview;
CREATE TABLE JUMpreview
SELECT "1" AS 'id', COUNT(*) AS 'number' , 'count Objektlog_es' AS explaination FROM Objektlog_es WHERE veranstalter='JUM' 
UNION
SELECT "2", COUNT(DISTINCT iff), 'number of IFF in Objektlog_es'  FROM Objektlog_es WHERE veranstalter='JUM' AND iff<>0 AND iff<>888888 AND iff IS NOT NULL
UNION
SELECT "3", COUNT(*) , 'count HotelInfo' FROM HotelInfoBoxVACRS WHERE tourOperator='JUM'
UNION
SELECT "4", COUNT(DISTINCT iff) , 'number of IFF in HotelInfo'  FROM HotelInfoBoxVACRS WHERE tourOperator='JUM' AND iff<>0 AND iff<>888888 AND iff IS NOT NULL;

#accomCode in objectlog not in HotelInfoBoxVACRS
DROP TABLE IF EXISTS JUMnoDescribe_accomCode;
CREATE TABLE JUMnoDescribe_accomCode SELECT DISTINCT Objektlog_es.leistungscodierung , Objektlog_es.iff
FROM Objektlog_es LEFT JOIN HotelInfoBoxVACRS 
ON HotelInfoBoxVACRS.tourOperator = Objektlog_es.veranstalter AND HotelInfoBoxVACRS.accomCode = Objektlog_es.leistungscodierung
WHERE Objektlog_es.veranstalter='JUM' AND HotelInfoBoxVACRS.accomCode IS NULL;

#iff in objectlog not in HotelInfoBoxVACRS
DROP TABLE IF EXISTS JUMnoDescribe_iff;
CREATE TABLE JUMnoDescribe_iff SELECT DISTINCT Objektlog_es.leistungscodierung , Objektlog_es.iff
FROM Objektlog_es LEFT JOIN HotelInfoBoxVACRS 
ON HotelInfoBoxVACRS.tourOperator = Objektlog_es.veranstalter AND HotelInfoBoxVACRS.IFF = Objektlog_es.iff
WHERE Objektlog_es.veranstalter='JUM' AND Objektlog_es.iff IS NOT NULL AND HotelInfoBoxVACRS.IFF IS NULL ;

#offer without describe, not send by tour oeprator
INSERT INTO JUMpreview SELECT '5', COUNT(DISTINCT leistungscodierung) , 'offer without describe' FROM JUMnoDescribe_accomCode;

#iff without describe,
INSERT INTO JUMpreview SELECT '6', COUNT(DISTINCT iff), 'iff without describe' FROM JUMnoDescribe_iff;
/*
SELECT DISTINCT Objektlog_es.iff
FROM Objektlog_es LEFT JOIN HotelInfoBoxVACRS 
ON HotelInfoBoxVACRS.tourOperator = Objektlog_es.veranstalter AND HotelInfoBoxVACRS.IFF = Objektlog_es.iff
WHERE Objektlog_es.veranstalter='JUM' AND Objektlog_es.iff IS NOT NULL AND HotelInfoBoxVACRS.IFF IS NULL ;
*/

#iff with describe
INSERT INTO JUMpreview SELECT '7', (SELECT number FROM JUMpreview WHERE id LIKE '2') - (SELECT number FROM JUMpreview WHERE id LIKE '6'), 'iff with describe'; 
/*
SELECT DISTINCT Objektlog_es.iff
FROM Objektlog_es LEFT JOIN HotelInfoBoxVACRS 
ON HotelInfoBoxVACRS.tourOperator = Objektlog_es.veranstalter AND HotelInfoBoxVACRS.IFF = Objektlog_es.iff
WHERE Objektlog_es.veranstalter='JUM' AND Objektlog_es.iff IS NOT NULL AND HotelInfoBoxVACRS.IFF IS NOT NULL ;
*/

#ADD status
INSERT INTO es_status_s (tourOperator, iff_objektlog, iff_mit_describe, iff_ohne_describe) 
VALUES ('JUM', (SELECT number FROM JUMpreview WHERE id LIKE '2'),(SELECT number FROM JUMpreview WHERE id LIKE '7'), (SELECT number FROM JUMpreview WHERE id LIKE '6') );