我一直在研究用于查看电影的数据库,并尝试完成LEFT OUTER JOIN,查询结果以NULL值结尾,而某些评论家未对某些电影进行回顾。我必须使用交叉联接才能使行彼此相乘,但是,我应该从LEFT OUTER JOIN本身获取结果。

mysql> select *
-> from F;
+------------+--------------------------------+-------------+----------------    -----+-----------+
| FilmNumber | FilmName                       | OpeningDate | TopBilledActor      | Genre     |
+------------+--------------------------------+-------------+----------------    -----+-----------+
| F1         | Transformers: The Last Knight  | 6/21/2017   | Mark Wahlberg       | Action    |
| F2         | Cars 3                         | 6/16/2017   | Owen Wilson         | Animation |
| F3         | Wonder Woman                   | 6/2/2017    | Gal Gadot           | Action    |
| F4         | All Eyez on Me                 | 6/16/2017   | Demetrius Shipp Jr. | Biography |
| F5         | The Mummy                      | 6/9/2017    | Tom Cruise          | Action    |
| F6         | Rough Night                    | 6/16/2017   | Scarlett Johansson  | Comedy    |
| F7         | Guardians of the Galaxy Vol. 2 | 5/5/2017    | Chris Pratt         | Action    |
| F8         | Men Tell No Tales              | 5/26/2017   | Johnny Depp         | Action    |
| F9         | 47 Meters Down                 | 6/16/2017   | Mandy Moore         | Adventure |
| F10        | Captain Underpants             | 6/2/2017    | Kevin Hart          | Animation |
+------------+--------------------------------+-------------+----------------    -----+-----------+
10 rows in set (0.00 sec)

mysql> select *
-> from C;
+------------------+----------------------+-----------------+
| CriticSiteNumber | CriticSiteName       | CriticName      |
+------------------+----------------------+-----------------+
| C1               | The New York Times   | Neil Genzlinger |
| C2               | Variety              | Owen Gleiberman |
| C3               | Variety              | Andrew Barker   |
| C4               | IndieWire            | Judy Dry        |
| C5               | IndieWire            | Eric Kohn       |
| C6               | Paste Magazine       | Tim Grierson    |
| C7               | We Got This Covered  | Matt Donato     |
| C8               | Entertainment Weekly | Chris Nashawty  |
+------------------+----------------------+-----------------+
8 rows in set (0.00 sec)
mysql> select *
    -> from FC;
+------------+------------------+--------------+
| FilmNumber | CriticSiteNumber | OverallScore |
+------------+------------------+--------------+
| F1         | C1               |           50 |
| F1         | C2               |           60 |
| F1         | C5               |           50 |
| F1         | C6               |           20 |
| F1         | C7               |           40 |
| F1         | C8               |           58 |
| F2         | C1               |           80 |
| F2         | C2               |           70 |
| F2         | C5               |           83 |
| F2         | C6               |           50 |
| F2         | C7               |           60 |
| F3         | C1               |           80 |
| F3         | C2               |           82 |
| F3         | C3               |           78 |
| F3         | C4               |           72 |
| F3         | C6               |           81 |
| F3         | C8               |           85 |
| F4         | C1               |           20 |
| F4         | C2               |           60 |
| F4         | C4               |           58 |
| F4         | C6               |           42 |
| F5         | C2               |           50 |
| F5         | C4               |           16 |
| F5         | C6               |           52 |
| F5         | C7               |           30 |
| F5         | C8               |           67 |
| F6         | C1               |           40 |
| F6         | C2               |           70 |
| F6         | C3               |           65 |
| F6         | C5               |           83 |
| F6         | C6               |           43 |
| F6         | C7               |           70 |
| F6         | C8               |           58 |
| F7         | C2               |           70 |
| F7         | C5               |           67 |
| F7         | C6               |           80 |
| F7         | C7               |           80 |
| F7         | C8               |           67 |
| F8         | C3               |           38 |
| F8         | C4               |           51 |
| F8         | C6               |           22 |
| F8         | C7               |           58 |
| F8         | C8               |           45 |
| F9         | C1               |           55 |
| F9         | C2               |           44 |
| F9         | C4               |           50 |
| F9         | C6               |           70 |
| F9         | C7               |           60 |
| F9         | C8               |           67 |
| F10        | C1               |           69 |
| F10        | C2               |           60 |
| F10        | C4               |           55 |
| F10        | C7               |           70 |
| F10        | C8               |           83 |
+------------+------------------+--------------+
54 rows in set (0.00 sec)

mysql>


既然这些是我的桌子,您会注意到,在很多情况下,某些评论家都没有给电影打分,但是当我进行左前角的连接时(如我的教授所示),这些是我的结果:

我试图获得的结果是在TotalScore列中,对于每个实例都将为NULL值的情况,没有某个评论家进行过审查。换句话说,我想查看总共80行(10部电影,8位评论家),而不必在查询中使用CROSS JOIN或NULL。

    mysql> select F.FilmName, FC.CriticSiteNumber, FC.OverallScore
    -> from F LEFT OUTER JOIN FC
    -> on F.FilmNumber=FC.FilmNumber
    -> order by FilmName;
+--------------------------------+------------------+--------------+
| FilmName                       | CriticSiteNumber | OverallScore |
+--------------------------------+------------------+--------------+
| 47 Meters Down                 | C1               |           55 |
| 47 Meters Down                 | C2               |           44 |
| 47 Meters Down                 | C4               |           50 |
| 47 Meters Down                 | C6               |           70 |
| 47 Meters Down                 | C7               |           60 |
| 47 Meters Down                 | C8               |           67 |
| All Eyez on Me                 | C1               |           20 |
| All Eyez on Me                 | C2               |           60 |
| All Eyez on Me                 | C4               |           58 |
| All Eyez on Me                 | C6               |           42 |
| Captain Underpants             | C1               |           69 |
| Captain Underpants             | C2               |           60 |
| Captain Underpants             | C4               |           55 |
| Captain Underpants             | C7               |           70 |
| Captain Underpants             | C8               |           83 |
| Cars 3                         | C1               |           80 |
| Cars 3                         | C2               |           70 |
| Cars 3                         | C5               |           83 |
| Cars 3                         | C6               |           50 |
| Cars 3                         | C7               |           60 |
| Guardians of the Galaxy Vol. 2 | C2               |           70 |
| Guardians of the Galaxy Vol. 2 | C5               |           67 |
| Guardians of the Galaxy Vol. 2 | C6               |           80 |
| Guardians of the Galaxy Vol. 2 | C7               |           80 |
| Guardians of the Galaxy Vol. 2 | C8               |           67 |
| Men Tell No Tales              | C3               |           38 |
| Men Tell No Tales              | C4               |           51 |
| Men Tell No Tales              | C6               |           22 |
| Men Tell No Tales              | C7               |           58 |
| Men Tell No Tales              | C8               |           45 |
| Rough Night                    | C1               |           40 |
| Rough Night                    | C2               |           70 |
| Rough Night                    | C3               |           65 |
| Rough Night                    | C5               |           83 |
| Rough Night                    | C6               |           43 |
| Rough Night                    | C7               |           70 |
| Rough Night                    | C8               |           58 |
| The Mummy                      | C2               |           50 |
| The Mummy                      | C4               |           16 |
| The Mummy                      | C6               |           52 |
| The Mummy                      | C7               |           30 |
| The Mummy                      | C8               |           67 |
| Transformers: The Last Knight  | C1               |           50 |
| Transformers: The Last Knight  | C2               |           60 |
| Transformers: The Last Knight  | C5               |           50 |
| Transformers: The Last Knight  | C6               |           20 |
| Transformers: The Last Knight  | C7               |           40 |
| Transformers: The Last Knight  | C8               |           58 |
| Wonder Woman                   | C1               |           80 |
| Wonder Woman                   | C2               |           82 |
| Wonder Woman                   | C3               |           78 |
| Wonder Woman                   | C4               |           72 |
| Wonder Woman                   | C6               |           81 |
| Wonder Woman                   | C8               |           85 |
+--------------------------------+------------------+--------------+
54 rows in set (0.00 sec)

最佳答案

问题似乎是您没有在查询中包括C表。因此,该查询不会提取任何NULL值,因为您只是在FC表上加入F表。引擎基本上不知道您是否要在结果中包括所有评论者。尝试这个:

mysql> select F.FilmName, C.CriticSiteNumber, FC.OverallScore
-> from F LEFT OUTER JOIN FC
-> on F.FilmNumber=FC.FilmNumber
-> LEFT OUTER JOIN C
-> ON C.CriticSiteNumber = FC.CriticSiteNumber
-> order by FilmName;

关于mysql - ER模型无法在MySQL中使用LEFT OUTER JOIN产生NULL值,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/46959694/

10-12 12:52
查看更多