为什么这个左联接只返回一个记录? [英] 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屋！