mysql 欄位值拼接，同一欄位迴圈拼接

標籤：not   商品   cti   ice   tor   category   HERE   rem   esc   

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;

mysql 欄位值拼接，同一欄位迴圈拼接