这个问题是关于一个非常基本的SQL操作的。
我们有两张桌子:
+-----------------------+
| game_results |
+--------+-------+------+
| Name | Score | Rank |
+--------+-------+------+
| David | 100 | null |
| Jacob | 100 | null |
| Gordon | 99.9 | null |
| Ian | 99.9 | null |
| Sarah | 99.9 | null |
| Robert | 1000 | null |
+--------+-------+------+
+-------+-------+
| ranked_scores |
+-------+-------+
| Score | Rank |
+-------+-------+
| 1000 | 1 |
| 100 | 2 |
| 99.9 | 3 |
+-------+-------+
我们的目标是编写一些SQL,这些SQL将填充GAME RESULTS
表中的Rank
行。
我们如何编写SQL来完成以下任务:
- 从
GAME RESULTS
表中选择一个特定分数(例如100( - 在
ranked_scores
表中查找该分数以获得排名(例如2( - 然后在
GAME RESULTS
表中输入秩
示例:
- 注意戈登的得分是99.9
- 在
ranked_scores
表中查找99.9,可以看到秩号为3 - 记录戈登在
game_results
表中排名第3
我写了以下伪代码,但它有for循环,看起来根本不像SQL:
for each row `r1` in the table named `game_results` {
Let `score` be the score value of row `r`
// Example:
// If `r1` is the following row
// +--------+-------+------+
// | David | 100 | null |
// +--------+-------+------+
//
// then `score` is:
// 100
Let `reduce_ranked_score` be a new table created from `ranked_scores`
such that `reduce_ranked_score` only contains the rows where the score
value is equal to `score`
For every row `r2` in `reduce_ranked_score`, (r2.Score == score)
// Example:
// if `r1` is the following row
// | David | 100 | null |
//
// then `reduce_ranked_score` is:
//
// +-------+-------+
// | Score | Rank |
// +-------+-------+
// | 100 | 2 |
// +-------+-------+
Let `rank` be the rank value of the only row in the table named `reduce_ranked_score`
overwrite `r1.rank` with a copy the value of `rank`
}
以下是我为生成ranked_scores
表而编写的一些SQL:
CREATE VIEW distinct_scores AS
SELECT DISTINCT Score
FROM table_name;
CREATE VIEW ranked_scores AS
SELECT
ROW_NUMBER() OVER (
ORDER BY Score
) Rank,
Score,
Rank
FROM
distinct_scores;
您可以将update
与join
:一起使用
update game_results gr join
ranked_scores rs
on gr.score = rs.score
set gr.rank = rs.rank
where rs.score = 100; -- leave this out if you want to update all scores
也就是说,通常最好将数据保留在两个单独的表中,并在需要信息时连接这些表。在多个表中重复相同的信息被认为是一种糟糕的设计实践——有时是合适的,但您应该真正知道自己在做什么以及为什么。