LEFT JOIN的行为不像预期的那样在MySQL中给出NULL [英] LEFT JOIN doesn't behave as expected as gives NULLs in MySQL
问题描述
我正在完成HackerRank的这项挑战.
I am completing this challenge from HackerRank.
它询问:
枢转职业"中的职业"列，以便每个姓名"按字母顺序排序并显示在其相应的职业下方.输出列标题分别为Doctor，Professor，Singer和Actor.
它包含以下数据表:
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，Professor，Singer和Actor.
We want to pivot this table by Occupation such that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. The output column headers (which don't actually) should be Doctor, Professor, Singer, and Actor, respectively.
但是，当我运行以下MySQL代码时:
However, when I run this MySQL code:
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的行为与预期不符，我得到了:
The LEFT JOINs don't behave as expected and I get:
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的行为是否使您无法对多个表进行编号?我不清楚.
This doesn't make sense to me -- why does the join produce so many nulls? Does MySQL behave in a way such that you can't number multiple tables? Not clear to me.
推荐答案
我认为它与您认为的方式不匹配的原因是，对于每个子查询，@row_number都没有重置为1
I'm supposing that the reason it isn't matching up the way you think it should is that @row_number isn't resetting to 1 for each subquery.
我进行了测试，只是加入了前两个(教授和医生)，但是使用了CROSS JOIN，所以我可以看到所有的row_number值.
I tested it out, just joining the first two (Professors and Doctors), but using a CROSS JOIN, so I could see all the row_number values.
+------------+--------+------------+----------+
| 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都已完成.
You can see that apparently the row numbers are incremented progressively, and the initial value of 1 in both subqueries has already been done by the time the rows are numbered.
您也许可以通过在每个子查询中使用不同的用户变量来解决此问题.
You might be able to fix this by using a distinct user variable in each subquery.
但是无论如何，该查询都无法以您想要的方式工作，例如，如果您的教授人数少于其他行业的成员.
But this query will not work the way you want anyway, for example if you ever have fewer Professors than members of other professions.
老实说，我不会在SQL中进行这种列式格式化.只需执行四个独立的查询，将所有结果提取到您的应用程序中，然后在输出时将其格式化为列.这样会更简单，并且简单的代码更易于编写，调试和维护.
Honestly, I would not do this kind of columnar formatting in SQL. Just do four independent queries, fetch all the results into your application, and format into columns as you output. It will be much simpler that way, and simple code is easier to write, easier to debug, easier to maintain.
发表您的评论
很公平，只要您(和其他读者)知道在一个真实的项目中，执行过分聪明的SQL并不总是最好的主意，那么这样做就可以了，这是很好的编码挑战.
Fair enough, doing this as a coding challenge is fine, so long as you (and other readers) know that in a real project, doing excessively clever SQL isn't always the best idea.
由于您在进行编码挑战，所以您应该自己解决它，因此我无法为您提供产生下面输出的解决方案.但这是有可能的证据(我保证我没有模拟输出，我确实从终端窗口复制并粘贴了输出).祝你好运！
Since you're doing a coding challenge, you should solve it yourself, so I can't give you the solution that produces the output below. But this is evidence that it's possible (I promise I did not mock up the output, I really copy & pasted it from my terminal window). Good luck!
+------------+-----------+--------+-----------+----------+
| 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 |
+------------+-----------+--------+-----------+----------+
这篇关于LEFT JOIN的行为不像预期的那样在MySQL中给出NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
