SELECT
trp.rest_id as 商户id,
trp.rest_name as 商户名称,
trp.longitude as 经度,
trp.latitude as 纬度,
trp.is_premium as 是否品牌,
trp.is_new as 是否新店,
ters.overall_score as 店铺评价（商品）,
ters.service_score as 店铺评价（服务态度）,
tcl.category_name as 所属品类（一级）,
(SELECT GROUP_CONCAT(terl.category_name_level2 ORDER BY terl.category_name_level2 asc SEPARATOR '|')
FROM t_e_rest_category_city terl
where
terl.`month` = trp.`month` and terl.city_code = trp.city_code AND terl.rest_id = trp.rest_id) as 所属品类（二级）,
(SELECT GROUP_CONCAT(concat('[',teot.start_time,'-',teot.end_time,']') ORDER BY teot.start_time asc SEPARATOR '|')
FROM t_e_rest_open_time_city teot
where
teot.`month` = trp.`month` and teot.city_code = trp.city_code AND teot.rest_id = trp.rest_id) as 营业时长,
trp.order_month_sales as 近30天单量,
(SELECT GROUP_CONCAT(tera.description ORDER BY tera.description asc SEPARATOR '|')
FROM t_e_rest_active_info tera
where
tera.`month` = trp.`month` and tera.city_code = trp.city_code AND tera.rest_id = trp.rest_id and tera.active_type in ('减','满')) as 满减,
(SELECT GROUP_CONCAT(tera.description ORDER BY tera.description asc SEPARATOR '|')
FROM t_e_rest_active_info tera
where
tera.`month` = trp.`month` and tera.city_code = trp.city_code AND tera.rest_id = trp.rest_id and tera.active_type not in ('减')) as 活动
FROM
t_rest_pandect_info trp
LEFT JOIN t_rest_category_mapping trcp
ON
trcp.`month` = trp.`month`
AND
trcp.city_code = trp.city_code
AND
trcp.rest_id = trp.rest_id
LEFT JOIN
t_category_level1 tcl
ON
tcl.category_id = trcp.category
LEFT JOIN
t_e_rest_score_city ters
ON
ters.`month` = trp.`month`
AND
ters.city_code = trp.city_code
AND
ters.rest_id = trp.rest_id
WHERE
trp.`month` = '2018-06'
AND trp.city_code = 17
LIMIT 100;