php - 如何通过多个表按顺序获取 MySQL 查询

Question

如果不使用示例很难解释。这就是我的 MySQL 数据库的样子:

表名称:general_info

Movie_ID    Movie_Name
1           Iron Man
2           Superman
3           Batman

表名称:cast

Movie_ID     Cast_Name              Cast_Rating
1            Robert Downey Jr.      90
1            Gwyneth Paltrow        45

2            Henry Cavill           75
2            Amy Adams              65

3            Christian Bale         90
3            Heath Ledger           95

表名称:Production_Companies

Movie_ID       Production_name        Production_rating
1              Marvel                 95
1              Paramount              80

2              Legendary Pictures     65
2              DC Entertainment       75

3              Legendary Pictures     65

现在，我想获取如下电影:

首先添加钢铁侠的顶级 Actor 评分+钢铁侠的顶级制作公司的评分，并对每部电影执行此操作。

喜欢:

Iron man = 90 + 95 = 190 (Robert + Marvel)
SUperman = 75 + 75 = 150 (Henry + DC)
Batman = 95 + 65 = 160 (Christian + Legendary Pictures)

并按以下顺序获取电影:

Ironman
Batman
Superman

现在，我可以使用纯 MySQL 来完成此操作还是还需要使用 PHP？我的数据库结构正确吗？

Answer 1

set @rt:=0;
select movie_id, movie, cast_name as 'actor', cast_rating as 'rating', Production_name as 'company', Production_rating as 'prodrating', @rt:= ( cast_rating + Production_rating ) as 'sum' from(    
    select 
        c.`movie_id`,
        i.`movie_name` as `movie`,
        c.`Cast_Name`,
        c.`Cast_Rating`,
        p.`Production_name`,
        p.`Production_rating`
    from `cast` c
        left outer join `general_info` i on i.`movie_id`=c.`movie_id`
        join `production_companies` p on p.`movie_id`=c.`movie_id`
    order by `cast_rating` desc
) `sub`
group by `movie_id`
order by `sum` desc;

此输出

+----------+----------+-------------------+--------+--------------------+------------+-----+
| movie_id | movie    | actor             | rating | company            | prodrating | sum |
+----------+----------+-------------------+--------+--------------------+------------+-----+
|        1 | Iron Man | Robert Downey Jr. |     95 | Marvel             |  95        | 190 |
|        3 | Batman   | Heath Ledger      |     95 | Legendary Pictures |  65        | 160 |
|        2 | Superman | Henry Cavill      |     75 | DC Entertainment   |  75        | 150 |
+----------+----------+-------------------+--------+--------------------+------------+-----+

或者，您可以像这样内联执行，而不是使用 set 声明变量

select 
    @rt:=0 as 'x', 
    `movie_id`, 
    `movie`, 
    `cast_name` as 'actor', 
    `cast_rating` as 'rating', 
    `production_name` as 'company', 
    `production_rating` as 'prodrating', 
    @rt:= ( `cast_rating` + `production_rating` ) as 'overall-rating' 
    from (  
        select 
            c.`movie_id`,
            i.`movie_name` as `movie`,
            c.`cast_name`,
            c.`cast_rating`,
            p.`production_name`,
            p.`production_rating`
        from `cast` c
            left outer join `general_info` i on i.`movie_id`=c.`movie_id`
            join `production_companies` p on p.`movie_id`=c.`movie_id`
        order by `cast_rating` desc
    ) `sub`
group by `movie_id`
order by `overall-rating` desc;