左联接的行为与MySQL中的空值不同

问题描述

我正在从HackerRank完成this challenge。

它会问：

它涉及以下数据表：

Name       Occupation     
Ashley      Professor 
Samantha Actor 
Julia         Doctor 
Britney     Professor 
Maria        Professor 
Meera       Professor 
Priya         Doctor 
Priyanka    Professor 
Jennifer     Actor 
Ketty         Actor 
Belvet Professor 
Naomi Professor 
Jane Singer 
Jenny Singer 
Kristeen Singer 
Christeen Singer 
Eve Actor 
Aamina Doctor

我们希望按Occupation透视此表，以便每个名称按字母顺序排序并显示在其对应的职业下。输出列标题(实际上不是)应该分别是Doctor、Offer、Singer和Actor。

但是，当我运行此MySQL代码时：

SELECT d.name, p.name, s.name, a.name 
FROM (
    SELECT @row_number:=@row_number+1 AS row_number, Name
    FROM OCCUPATIONS, (SELECT @row_number:=0) AS t
    WHERE Occupation = 'Professor'
    ORDER BY Name
    ) p
LEFT JOIN (
    SELECT @row_number:=@row_number+1 AS row_number, Name
    FROM OCCUPATIONS, (SELECT @row_number:=0) AS t
    WHERE Occupation = 'Doctor'
    ORDER BY Name
    ) d ON d.row_number =p.row_number
LEFT JOIN (
    SELECT @row_number:=@row_number+1 AS row_number, Name
    FROM OCCUPATIONS, (SELECT @row_number:=0) AS t
    WHERE Occupation = 'Singer'
    ORDER BY Name
    ) s ON p.row_number =s.row_number
LEFT JOIN (
    SELECT @row_number:=@row_number+1 AS row_number, Name
    FROM OCCUPATIONS, (SELECT @row_number:=0) AS t
    WHERE Occupation = 'Actor'
    ORDER BY Name
    ) a ON p.row_number =a.row_number

LEFT JOIN%s的行为不符合预期，我收到：

NULL Ashley NULL NULL 
NULL Belvet NULL NULL 
NULL Britney NULL NULL 
NULL Maria NULL NULL 
NULL Meera NULL NULL 
NULL Naomi NULL NULL 
NULL Priyanka NULL NULL

这对我来说没有意义--为什么连接会生成这么多空值？MySQL的行为方式是否使您不能对多个表进行编号？我不清楚。

推荐答案

我认为它没有以您认为的方式匹配的原因是@row_number没有为每个子查询重置为1。

我测试了它，只是加入了前两个(教授和医生)，但使用了交叉连接，这样我就可以看到所有的ROW_NUMBER值。

+------------+--------+------------+----------+
| row_number | name   | row_number | name     |
+------------+--------+------------+----------+
|          8 | Aamina |          1 | Ashley   |
|          8 | Aamina |          2 | Belvet   |
|          8 | Aamina |          3 | Britney  |
|          8 | Aamina |          4 | Maria    |
|          8 | Aamina |          5 | Meera    |
|          8 | Aamina |          6 | Naomi    |
|          8 | Aamina |          7 | Priyanka |
|          9 | Julia  |          1 | Ashley   |
|          9 | Julia  |          2 | Belvet   |
|          9 | Julia  |          3 | Britney  |
|          9 | Julia  |          4 | Maria    |
|          9 | Julia  |          5 | Meera    |
|          9 | Julia  |          6 | Naomi    |
|          9 | Julia  |          7 | Priyanka |
|         10 | Priya  |          1 | Ashley   |
|         10 | Priya  |          2 | Belvet   |
|         10 | Priya  |          3 | Britney  |
|         10 | Priya  |          4 | Maria    |
|         10 | Priya  |          5 | Meera    |
|         10 | Priya  |          6 | Naomi    |
|         10 | Priya  |          7 | Priyanka |
+------------+--------+------------+----------+

您可以看到，行号显然是递增的，并且在对行进行编号时，两个子查询中的初始值1已经完成。

您或许能够通过在每个子查询中使用不同的用户变量来修复此问题。

但此查询无论如何都不会以您希望的方式工作，例如，如果您的教授比其他职业的成员少。

老实说，我不会在SQL中进行这种列格式设置。只需执行四个独立的查询，将所有结果提取到您的应用程序中，并在输出时将其格式化为列。这样会简单得多，简单的代码更容易编写、更容易调试、更容易维护。

请回复您的评论：

很公平，只要您(和其他读者)知道在实际项目中，编写过于巧妙的SQL并不总是最好的主意，那么将其作为编码挑战是可以接受的。

因为您正在进行编码挑战，所以您应该自己解决它，所以我不能给您提供产生以下输出的解决方案。但这证明了这是可能的(我保证我没有模拟输出，我真的从我的终端窗口复制和粘贴了它)。祝你好运！

+------------+-----------+--------+-----------+----------+
| row_number | Professor | Doctor | Singer    | Actor    |
+------------+-----------+--------+-----------+----------+
|          1 | Ashley    | Aamina | Christeen | Eve      |
|          2 | Belvet    | Julia  | Jane      | Jennifer |
|          3 | Britney   | Priya  | Jenny     | Ketty    |
|          4 | Maria     | NULL   | Kristeen  | Samantha |
|          5 | Meera     | NULL   | NULL      | NULL     |
|          6 | Naomi     | NULL   | NULL      | NULL     |
|          7 | Priyanka  | NULL   | NULL      | NULL     |
+------------+-----------+--------+-----------+----------+