各种MySQL混乱操作

插入INSERT

-- 多数据插入表
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;

-- 选择数据插入其他的表
INSERT INTO `ctrip_master_hotel`
(`id`,`name`,`nameEn`,`city`,`cityEn`,`country`,`countryEn`,`address`,`addressEn`,`fr_hotelId`,`description`)
SELECT `masterhotelId_ctrip`,`hotelName_zhcn`,`hotelName`,`city_zhcn`,`city`,`country_zhcn`,`country`,`address_zhcn`,`address`,`hotelId_fr`,`description_zhcn`
FROM `fr_hotel_zhcn`

 

新建表 CREATE

 

DROP TABLE IF EXISTS JUMpreview;

-- 使用 Union 得到不同表的数据，建立新表
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') );