为什么这个左联接只返回一个记录? [英] why this left join returns only one record?
问题描述
这里有一个示例输入,SQL和输出,想知道为什么只返回一行?我正在使用MySQL/MySQL Workbench.
Here is a sample input, SQL and output, and wondering why only one row is returned? I am using MySQL/MySQL Workbench.
+----+-------+
| Id | Score |
+----+-------+
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
+----+-------+
SELECT
score_primary.score, COUNT(DISTINCT score_higher.score) + 1 AS rank
FROM
TestRank score_primary
LEFT JOIN TestRank score_higher ON score_higher.score > score_primary.score
输出:
+-------+------+
| score | Rank |
+-------+------+
| 3.5 | 4 |
+-------+------+
顺便说一句,如果我为这个新的SQL语句添加group by,我如何知道(1)首先执行group by,然后在group-by结果上左联接,还是(2)左联接,然后group-通过左联接结果?
BTW, if I add group by, for this new SQL statement, how do I know if (1) group by is first executed, then left join on group-by results, or (2) left join, then group-by on left join results?
SELECT
score_primary.score, COUNT(DISTINCT score_higher.score) + 1 AS rank
FROM
TestRank score_primary
LEFT JOIN TestRank score_higher ON score_higher.score > score_primary.score
GROUP BY score_primary.score
推荐答案
使用不带GROUP BY
的聚合函数(例如COUNT()
)将导致
Using aggregate function (e.g. COUNT()
) without GROUP BY
will cause implicit GROUP BY grouping all rows into a single row.
如果在不包含GROUP BY子句的语句中使用组函数,则等效于对所有行进行分组.
If you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows.
这篇关于为什么这个左联接只返回一个记录?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!