将一组数据的合并和数据中特殊字符的替换
目标结果返回数据
"filmId":"1", "filmName":"我不是药神", "filmLength":"132", "filmCats":"喜剧,剧情", "actors":"程勇,曹斌,吕受益,刘思慧", "imgAddress":"films/238e2dc36beae55a71cabfc14069fe78236351.jpg", "subAddress":"films/001.jpg,films/002.jpg,films/003.jpg,films/004.jpg,films/005.jpg"
涉及到的表
表一 主要电影信息表 mooc_film_t
表二:次要电影信息表 mooc_film_info_t
表三 :演员表 电影id对应的演员 mooc_film_actor_t
表四 电影id对应的电影类型 mooc_cat_dict_t
第一步,使用表一表二凑出部分数据
select film.UUID as filmId, film.film_name as filmName, info.film_length as filmLength, film.film_cats as film_cats, film.img_address as imgAddress, info.film_imgs as subAddress from mooc_film_t film LEFT JOIN mooc_film_info_t info on film.UUID = info.film_id
运行结果
第二步 :查询出演员
select * from mooc_film_actor_t
#将多个字段合成一个
select GROUP_CONCAT(role_name SEPARATOR ',') from mooc_film_actor_t where film_id = 2
则第一步的结果改为
select film.UUID as filmId, film.film_name as filmName, info.film_length as filmLength, film.film_cats as film_cats, (select GROUP_CONCAT(role_name SEPARATOR ',') from mooc_film_actor_t actor where actor.film_id = film.UUID) as actors, film.img_address as imgAddress, info.film_imgs as subAddress from mooc_film_t film LEFT JOIN mooc_film_info_t info on film.UUID = info.film_id
第三步将电影类型转换为对应数据
3.1 将#替换为,
select REPLACE(TRIM(BOTH '#' FROM film.film_cats),"#",",") from mooc_film_t film
3.2将数字替换为关联表中的数据 mooc_film_t ,mooc_cat_dict_t
#获取到id 所对应的值 select * from mooc_cat_dict_t cat where FIND_IN_SET(cat.UUID,(select REPLACE(TRIM(BOTH '#' FROM film.film_cats),"#",",") FROM mooc_film_t film ) )
把文字合并
#把值连接起来 select GROUP_CONCAT(show_name SEPARATOR ',') from mooc_cat_dict_t cat where FIND_IN_SET(cat.UUID,(select REPLACE(TRIM(BOTH '#' FROM film.film_cats),"#",",") FROM mooc_film_t film ) )
最终
select film.UUID as filmId, film.film_name as filmName, info.film_length as filmLength, ( select GROUP_CONCAT(show_name SEPARATOR ',') from mooc_cat_dict_t cat where FIND_IN_SET(cat.UUID,(select REPLACE(TRIM(BOTH '#' FROM film.film_cats),"#",",") FROM mooc_film_t film ) )) as filmCats, (select GROUP_CONCAT(role_name SEPARATOR ',') from mooc_film_actor_t actor where actor.film_id = film.UUID) as actors, film.img_address as imgAddress, info.film_imgs as subAddress from mooc_film_t film LEFT JOIN mooc_film_info_t info on film.UUID = info.film_id