5-11 影片详情查询实现

根据主键获取电影信息，这可能是这个模块最复杂的一个场景了。 sql语句有点复杂





返回的对象


比较麻烦的字段

接口文档里面，我们要转换成这个格式

数据库内存的是这个。我们要把这个转换成中文


演员我们应该查的是这个表



mysqk本身给我们提供了一个现成的函数。纵列转横列

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`

 

查询字典表。

SELECT * FROM mooc_cat_dict_t;

mysql里面有个单独的函数叫做find_in_set。我们现在就是要把#号替换成我们的逗号。

这样就吧#2#4#22#转换成了逗号分隔

SELECT
    REPLACE(TRIM(BOTH '#' FROM film.film_cats),"#",",")
FROM mooc_film_t film

 

 

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
))

 

最终这里是需要传递一个变量。



变量改成f

 

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 f WHERE f.`UUID`=film.`UUID`
))
    ) 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`

 

最终查询出来的效果


 

结束