我一直在研究用于查看电影的数据库,并尝试完成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/