hello-python/spider_test/querys.sql

SELECT COUNT(1) from hotels;
SELECT COUNT(1) from hst_hotel;
SELECT COUNT(1) from saas_hotel; 

SELECT MAX(LENGTH(en_name)),MAX(LENGTH(phone)),MAX(LENGTH(en_address)) from saas_hotel;
SELECT MAX(LENGTH(hotel_name)),MAX(LENGTH(phone)),MAX(LENGTH(address)) from hotels;

SELECT city FROM hotels group by city;
SELECT province FROM hotels group by province;

SELECT * FROM hotels WHERE country<>'China' or city='Hong Kong' or city='Macau' or province='Taiwan' ORDER BY country,province,city;

SELECT * from saas_hotel limit 10;
SELECT * from saas_hotel WHERE country<>'中国' and country<>'其他' limit 10;
SELECT * from hotels  WHERE province='Taiwan' limit 10;
SELECT * from hotels  WHERE country<>'China' limit 10;
SELECT phone, lon, lat from hotels WHERE hotel_id=118;
UPDATE hotels SET lon=null,lat=null WHERE hotel_id=118;
SELECT * FROM hotels WHERE lon is null;

SELECT hotel_id, COUNT(1) from hotels group by hotel_id HAVING count(1)>1; 

INSERT INTO hotels_temp
SELECT h.hotel_id, h.hotel_name, h.link, h.slug, h.country, h.province, h.city, h.district, h.address, h.phone, h.site_url, h.rooms, h.hotel_presentation, h.meeting_facilities,
h.images, h.total_rooms, h.hotel_free_wifi, h.total_meeting_rooms, h.meeting_room_max_sqm, h.meeting_room_max_height, h.projector_resolution, h.total_restaurants,
h.room_quantity, h.meeting_room_quantity, h.meeting_room_facilities, h.restaurant_remark, h.distances, h.brand, h.attachments, h.lon, h.lat, 
sh.country as `s_country`, sh.province as `s_province`, sh.city as `s_city`, sh.en_name as `s_name`, sh.en_address as `s_address`, sh.phone as `s_phone`, 
sh.hop_id, sh.id as `s_id`, sh.lon as `s_lon`, sh.lat as `s_lat`, sh.loc_type as `s_loc_type`
FROM hotels h 
inner join saas_hotel sh on lower(trim(sh.en_name)) = lower(trim(h.hotel_name)) 
WHERE h.s_id is null and h.hotel_id in 
(
	SELECT h.hotel_id FROM hotels h 
	inner join saas_hotel sh on lower(trim(sh.en_name)) = lower(trim(h.hotel_name)) 
	WHERE h.s_id is null AND h.hotel_id not in (11930, 16280, 24797, 24329)
	GROUP BY h.hotel_id HAVING COUNT(1)=1
) ORDER BY h.country, h.province, h.city, h.district
;

INSERT INTO hotels_temp
SELECT h.hotel_id, h.hotel_name, h.link, h.slug, h.country, h.province, h.city, h.district, h.address, h.phone, h.site_url, h.rooms, h.hotel_presentation, h.meeting_facilities,
h.images, h.total_rooms, h.hotel_free_wifi, h.total_meeting_rooms, h.meeting_room_max_sqm, h.meeting_room_max_height, h.projector_resolution, h.total_restaurants,
h.room_quantity, h.meeting_room_quantity, h.meeting_room_facilities, h.restaurant_remark, h.distances, h.brand, h.attachments, h.lon, h.lat, 
sh.country as `s_country`, sh.province as `s_province`, sh.city as `s_city`, sh.en_name as `s_name`, sh.en_address as `s_address`, sh.phone as `s_phone`, 
sh.hop_id, sh.id as `s_id`, sh.lon as `s_lon`, sh.lat as `s_lat`, sh.loc_type as `s_loc_type`
FROM hotels h 
inner join saas_hotel sh on lower(trim(sh.en_address)) = lower(trim(h.address)) 
WHERE h.s_id is null and h.hotel_id in 
(
	SELECT h.hotel_id FROM hotels h 
	inner join saas_hotel sh on lower(trim(sh.en_address)) = lower(trim(h.address)) 
	WHERE h.s_id is null
	GROUP BY h.hotel_id HAVING COUNT(1)=1
) ORDER BY h.country, h.province, h.city, h.district
;

INSERT INTO hotels_temp
SELECT h.hotel_id, h.hotel_name, h.link, h.slug, h.country, h.province, h.city, h.district, h.address, h.phone, h.site_url, h.rooms, h.hotel_presentation, h.meeting_facilities,
h.images, h.total_rooms, h.hotel_free_wifi, h.total_meeting_rooms, h.meeting_room_max_sqm, h.meeting_room_max_height, h.projector_resolution, h.total_restaurants,
h.room_quantity, h.meeting_room_quantity, h.meeting_room_facilities, h.restaurant_remark, h.distances, h.brand, h.attachments, h.lon, h.lat, 
sh.country as `s_country`, sh.province as `s_province`, sh.city as `s_city`, sh.en_name as `s_name`, sh.en_address as `s_address`, sh.phone as `s_phone`, 
sh.hop_id, sh.id as `s_id`, sh.lon as `s_lon`, sh.lat as `s_lat`, sh.loc_type as `s_loc_type`
FROM hotels h 
inner join saas_hotel sh on REPLACE(trim(BOTH '+' FROM sh.phone), '-', '') = REPLACE(trim(BOTH '+' FROM h.phone), '-', '')
WHERE h.s_id is null and h.hotel_id in 
(
	SELECT h.hotel_id FROM hotels h 
	inner join saas_hotel sh on REPLACE(trim(BOTH '+' FROM sh.phone), '-', '') = REPLACE(trim(BOTH '+' FROM h.phone), '-', '')
	WHERE h.s_id is null AND h.hotel_id not in (29842, 16632)
	GROUP BY h.hotel_id HAVING COUNT(1)=1
) ORDER BY h.country, h.province, h.city, h.district
;

SELECT h.hotel_id, h.hotel_name, h.link, h.slug, h.country, h.province, h.city, h.district, h.address, h.phone, h.site_url, h.rooms, h.hotel_presentation, h.meeting_facilities,
h.images, h.total_rooms, h.hotel_free_wifi, h.total_meeting_rooms, h.meeting_room_max_sqm, h.meeting_room_max_height, h.projector_resolution, h.total_restaurants,
h.room_quantity, h.meeting_room_quantity, h.meeting_room_facilities, h.restaurant_remark, h.distances, h.brand, h.attachments, h.lon, h.lat, 
sh.country as `s_country`, sh.province as `s_province`, sh.city as `s_city`, sh.en_name as `s_name`, sh.en_address as `s_address`, sh.phone as `s_phone`, 
sh.hop_id, sh.id as `s_id`, sh.lon as `s_lon`, sh.lat as `s_lat`, sh.loc_type as `s_loc_type`
FROM hotels h 
inner join saas_hotel sh on ROUND(6378.138 * 2 * ASIN(SQRT(
                POW(SIN((h.lat * PI() / 180 - sh.lat * PI() / 180) / 2), 2) + 
                COS(h.lat * PI() / 180) * COS(sh.lat * PI() / 180) * POW(SIN((h.lon * PI() / 180 - sh.lon * PI() / 180) / 2), 2)
                )) * 1000) <= 200
WHERE h.s_id is null and h.hotel_id in 
(
	SELECT h.hotel_id FROM hotels h 
	inner join saas_hotel sh on ROUND(6378.138 * 2 * ASIN(SQRT(
                POW(SIN((h.lat * PI() / 180 - sh.lat * PI() / 180) / 2), 2) + 
                COS(h.lat * PI() / 180) * COS(sh.lat * PI() / 180) * POW(SIN((h.lon * PI() / 180 - sh.lon * PI() / 180) / 2), 2)
                )) * 1000) <= 200
	WHERE h.s_id is null and h.lon is not null
	GROUP BY h.hotel_id HAVING COUNT(1)=1
) ORDER BY h.country, h.province, h.city, h.district
;

INSERT INTO hotels_temp
SELECT h.hotel_id, h.hotel_name, h.link, h.slug, h.country, h.province, h.city, h.district, h.address, h.phone, h.site_url, h.rooms, h.hotel_presentation, h.meeting_facilities,
h.images, h.total_rooms, h.hotel_free_wifi, h.total_meeting_rooms, h.meeting_room_max_sqm, h.meeting_room_max_height, h.projector_resolution, h.total_restaurants,
h.room_quantity, h.meeting_room_quantity, h.meeting_room_facilities, h.restaurant_remark, h.distances, h.brand, h.attachments, h.lon, h.lat, 
sh.country as `s_country`, sh.province as `s_province`, sh.city as `s_city`, sh.en_name as `s_name`, sh.en_address as `s_address`, sh.phone as `s_phone`, 
sh.hop_id, sh.id as `s_id`, sh.lon as `s_lon`, sh.lat as `s_lat`, sh.loc_type as `s_loc_type`
FROM hotels h 
inner join saas_hotel sh on sh.hop_id=h.hop_id 
WHERE h.s_id is null and h.hop_id is not null and h.hotel_id in 
(
	SELECT h.hotel_id FROM hotels h 
	inner join saas_hotel sh on sh.hop_id=h.hop_id 
	WHERE h.s_id is null and h.hop_id is not null
	GROUP BY h.hotel_id HAVING COUNT(1)=1
) ORDER BY h.country, h.province, h.city, h.district
;


UPDATE hotels h 
inner join hotels_temp a on a.hotel_id=h.hotel_id 
SET h.s_country=a.s_country, h.s_province=a.s_province, h.s_city=a.s_city, h.s_name=a.s_name, h.s_address=a.s_address, h.s_phone=a.s_phone, 
h.hop_id=a.hop_id, h.s_id=a.s_id, h.s_lon=a.s_lon, h.s_lat=a.s_lat, h.s_loc_type=a.s_loc_type
;

SELECT 
ROUND(6378.138 * 2 * ASIN(SQRT(
                POW(SIN((lat * PI() / 180 - s_lat * PI() / 180) / 2), 2) + 
                COS(lat * PI() / 180) * COS(s_lat * PI() / 180) * POW(SIN((lon * PI() / 180 - s_lon * PI() / 180) / 2), 2)
                )) * 1000) AS distance, h.*
FROM hotels h WHERE lon is not null and s_lon is not null order by distance desc; 



SELECT SUM(CASE WHEN s_id IS NULL THEN 0 ELSE 1 END) as `mapped`, COUNT(1) as `total` FROM hotels;
SELECT SUM(CASE WHEN s_id IS NULL THEN 0 ELSE 1 END) as `mapped`, COUNT(1) as `total` FROM hotels WHERE country<>'China' or city='Hong Kong' or city='Macau' or province='Taiwan';
SELECT SUM(CASE WHEN s_id IS NULL THEN 0 ELSE 1 END) as `mapped`, COUNT(1) as `total` FROM hotels WHERE country<>'China';
SELECT * FROM hotels WHERE s_id IS NULL and country<>'China' ORDER BY country,province,city;

SELECT s_id, COUNT(1) FROM hotels group by s_id HAVING count(1)>1;

SELECT * FROM hotels h;

SELECT
h.hotel_id as `out_id`, h.hop_id, h.hotel_name, h.country, h.province, h.city, h.district, h.address, h.phone, h.site_url
FROM hotels h WHERE s_id IS NULL ORDER BY country, province, city, district;

SELECT ROUND(6378.138 * 2 * ASIN(SQRT(
                POW(SIN((lat * PI() / 180 - s_lat * PI() / 180) / 2), 2) + 
                COS(lat * PI() / 180) * COS(s_lat * PI() / 180) * POW(SIN((lon * PI() / 180 - s_lon * PI() / 180) / 2), 2)
                )) * 1000) AS distance, h.* FROM hotels h WHERE s_id in (976695731874210468,976710868580475183,976710868672750357,976728804883997105)
ORDER BY s_id
;

UPDATE hotels h 
SET h.s_country=NULL, h.s_province=NULL, h.s_city=NULL, h.s_name=NULL, h.s_address=NULL, h.s_phone=NULL, 
h.hop_id=NULL, h.s_id=NULL, h.s_lon=NULL, h.s_lat=NULL, h.s_loc_type=NULL
WHERE h.hotel_id in(16666, 18710, 18962, 32776, 31214, 30610, 30638, 24437, 16826)
;