选择数据，插入另一表
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') );