SQL算术和连接三列

Question

我有一个模式，看起来像这样：

+----------+
| tour     |
+----------+
| id       |
| name     |
+----------+

+----------+
| golfer   |
+----------+
| id       |
| name     |
| tour_id  |
+----------+

+-----------+
| stat      |
+-----------+
| id        |
| round     |
| score     |
| golfer_id |
+-----------+

所以从本质上讲，一次高尔夫巡回赛有X个高尔夫球手参加。一个高尔夫球手将有X个统计数据。stat表中的round列只包含数字(1，2，3，4...等等)。它们不一定是一个接一个，但它们是独一无二的。

我现在想要找到所有属于"PGA“巡回赛的高尔夫球手，并为这些球手中的每一个，计算他们在过去两轮中的得分。最后两轮基本上是状态表中具有最大两个数字的高尔夫球手的行。假设高尔夫球手“老虎伍兹”已经打了1、3、6和10轮，那么我只想计算他在第6轮和第10轮的得分。另一个要求是，我不想向那些还没有打过两轮的高尔夫球手展示。

我尝试了几种方法来解决这个问题，但总是让自己陷入困境。

Answer 1

如果你只想要最后两轮(强调“两轮”)，有一个简单的诀窍。这个技巧不会扩展到获取两个以上的记录，或者不是最后两个记录。要获取分区中的任意记录，您必须使用窗口函数，这些函数涉及的内容更多，并且只在主流数据库引擎的较新版本中受支持。

诀窍是在高尔夫球手id上自我相等地加入"stat“表。这样，你就可以得到任何两轮高尔夫球手的所有组合，包括同一轮的组合：

SELECT s1.round as s1_round, s2.round AS s2_round
FROM stat s1 INNER JOIN stat s2 ON (s1.golfer_id = s2.golfer_id)

然后(通过WHERE子句)排除具有相同轮次的组合，并确保这些组合始终是第一轮>第二轮。这意味着现在你有了任何两轮高尔夫球手的所有组合，没有重复的：

SELECT s1.round as s1_round, s2.round AS s2_round
FROM stat s1 INNER JOIN stat s2 ON (s1.golfer_id = s2.golfer_id)
WHERE s1.round > s2.round

请注意，如果只选择特定高尔夫球手的记录，并在两个轮次列上对DESC进行排序，则顶行将是该高尔夫球手的最后两轮：

SELECT TOP 1 s1.round as s1_round, s2.round AS s2_round
FROM stat s1 INNER JOIN stat s2 ON (s1.golfer_id = s2.golfer_id)
WHERE s1.round > s2.round
ORDER BY s1.round DESC, s2.round DESC

TOP 1是SQL Server的行话，用于获取最上面的行。对于MySQL，您需要使用LIMIT 1。对于其他数据库，使用数据库引擎的特定方式。

然而，在这种情况下，你不能这么简单地这样做，因为你需要所有高尔夫球手的最后两轮。你将不得不做更多的连接：

SELECT id,
   (SELECT MAX(s1.round) FROM stat s1 INNER JOIN stat s2 ON (s1.golfer_id = s2.golfer_id)
    WHERE s1.round > s2.round AND s1.golfer_id = golfer.id) AS last_round,
   (SELECT MAX(s2.round) FROM stat s1 INNER JOIN stat s2 ON (s1.golfer_id = s2.golfer_id)
    WHERE s1.round > s2.round AND s1.golfer_id = golfer.id) AS second_to_last_round
FROM golfer

这将为每个高尔夫球手提供最后两轮(分两列)。

或者，使用两列temp集连接高尔夫球桌也应该有效：

SELECT golfer.id, MAX(r.s1_round) AS last_round, MAX(r.s2_round) AS second_to_last_round
FROM golfer INNER JOIN 
(
 SELECT s1.golfer_id AS golfer_id, s1.round AS s1_round, s2.round AS s2_round
 FROM stat s1 INNER JOIN stat s2 ON (s1.golfer_id = s2.golfer_id)
 WHERE s1.round > s2.round
) r ON (r.golfer_id = golfer.id)
GROUP BY golfer.id

我将这个查询连接到tour表以获得PGA巡回赛的高尔夫球手，并将此查询连接回stats表以获得最后两轮的分数，这是一项微不足道的练习。

Answer 2

HSQLDB 2.1支持横向连接，这允许使用任意条件进行这种选择。

一个简单的连接将列出PGA巡回赛中的所有高尔夫球手：

select golfer.name from tour join golfer on (tour.id = tour_id and  tour.name = 'PGA')

然后，您可以根据需要多次横向加入此表，以获得特定分数。下一个示例包括最后一轮的得分(仅当该游戏已经玩了一轮)

select golfer.name, firststat.score from tour join golfer on (tour.id = tour_id and  tour.name = 'PGA' ),
 lateral(select * from stat where golfer_id = golfer.id order by round desc limit 1) firststat

在下一个示例中，您将使用另一个横向联接来包含倒数第二轮。如果玩家还没有完成两轮比赛，将不会出现该玩家的排位：

select golfer.name, secondstat.score score1, firststat.score score2 from tour join golfer on (tour.id = tour_id and  tour.name = 'PGA' ), 
 lateral(select * from stat where golfer_id = golfer.id order by round desc limit 1 offset 1) secondstat, 
 lateral(select * from stat where golfer_id = golfer.id order by round desc limit 1) firststat

横向联接不需要WHERE子句，因为"where条件“取自出现在当前表之前的from列表中的表。因此，横向表的子查询中的SELECT语句可以使用第一个连接表中的golfer.id。