- android - RelativeLayout 背景可绘制重叠内容
- android - 如何链接 cpufeatures lib 以获取 native android 库?
- java - OnItemClickListener 不起作用,但 OnLongItemClickListener 在自定义 ListView 中起作用
- java - Android 文件转字符串
我有如下表格:
id homeTeam awayTeam homeScore awayScore
1 t1 t2 3 2
2 t3 t7 0 1
. . . . .
. . . . .
. . . . .
这是本地联赛的足球比赛结果。我想获得“最长连胜”、“最长连败”和......只需要一个查询。我找了一圈,找到了oracle版本,但是我找不到如何做?PS:我有mysql数据库。提前致谢
最佳答案
这是一种方法,但我觉得您不会喜欢它...
考虑以下数据 DDL...
CREATE TABLE results
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,homeTeam INT NOT NULL
,awayTeam INT NOT NULL
,homeScore INT NOT NULL
,awayScore INT NOT NULL
);
INSERT INTO results VALUES
(1,1,2,3,2),
(2,3,4,0,1),
(3,2,1,2,0),
(4,4,3,1,0),
(5,3,2,1,2),
(6,2,3,0,2),
(7,1,4,4,1),
(8,4,1,1,2),
(9,1,3,3,0),
(10,3,1,1,0),
(11,4,2,1,0),
(12,2,4,1,2);
从这里,我们可以得到如下的中间结果......
SELECT x.*, COUNT(*) rank
FROM
( SELECT id,hometeam team, CASE WHEN homescore > awayscore THEN 'w' ELSE 'l' END result FROM results
UNION
SELECT id,awayteam, CASE WHEN awayscore > homescore THEN 'w' ELSE 'l' END result FROM results
) x
JOIN
( SELECT id,hometeam team, CASE WHEN homescore > awayscore THEN 'w' ELSE 'l' END result FROM results
UNION
SELECT id,awayteam, CASE WHEN awayscore > homescore THEN 'w' ELSE 'l' END result FROM results
) y
ON y.team = x.team
AND y.id <= x.id
GROUP
BY x.id
, x.team
ORDER
BY team, rank;
+----+------+--------+------+
| id | team | result | rank |
+----+------+--------+------+
| 1 | 1 | w | 1 |
| 3 | 1 | l | 2 |
| 7 | 1 | w | 3 |
| 8 | 1 | w | 4 |
| 9 | 1 | w | 5 |
| 10 | 1 | l | 6 |
| 1 | 2 | l | 1 |
| 3 | 2 | w | 2 |
| 5 | 2 | w | 3 |
| 6 | 2 | l | 4 |
| 11 | 2 | l | 5 |
| 12 | 2 | l | 6 |
| 2 | 3 | l | 1 |
| 4 | 3 | l | 2 |
| 5 | 3 | l | 3 |
| 6 | 3 | w | 4 |
| 9 | 3 | l | 5 |
| 10 | 3 | w | 6 |
| 2 | 4 | w | 1 |
| 4 | 4 | w | 2 |
| 7 | 4 | l | 3 |
| 8 | 4 | l | 4 |
| 11 | 4 | w | 5 |
| 12 | 4 | w | 6 |
+----+------+--------+------+
通过观察,我们可以看到团队 1 的连胜时间最长(连续 3 次“w”)。你可以设置几个@vars 来跟踪这个,或者,如果你有点自虐(像我一样),你可以做一些更慢、更长、更复杂的事情......
SELECT a.team
, MIN(c.rank) - a.rank + 1 streak
FROM (SELECT x.*, COUNT(*) rank
FROM
( SELECT id,hometeam team, CASE WHEN homescore > awayscore THEN 'w' ELSE 'l' END result FROM results
UNION
SELECT id,awayteam, CASE WHEN awayscore > homescore THEN 'w' ELSE 'l' END result FROM results
) x
JOIN
( SELECT id,hometeam team, CASE WHEN homescore > awayscore THEN 'w' ELSE 'l' END result FROM results
UNION
SELECT id,awayteam, CASE WHEN awayscore > homescore THEN 'w' ELSE 'l' END result FROM results
) y
ON y.team = x.team
AND y.id <= x.id
GROUP
BY x.id
, x.team
) a
LEFT
JOIN (SELECT x.*, COUNT(*) rank
FROM
( SELECT id,hometeam team, CASE WHEN homescore > awayscore THEN 'w' ELSE 'l' END result FROM results
UNION
SELECT id,awayteam, CASE WHEN awayscore > homescore THEN 'w' ELSE 'l' END result FROM results
) x
JOIN
( SELECT id,hometeam team, CASE WHEN homescore > awayscore THEN 'w' ELSE 'l' END result FROM results
UNION
SELECT id,awayteam, CASE WHEN awayscore > homescore THEN 'w' ELSE 'l' END result FROM results
) y
ON y.team = x.team
AND y.id <= x.id
GROUP
BY x.id
, x.team
) b
ON b.team = a.team
AND b.rank = a.rank - 1
AND b.result = a.result
LEFT
JOIN (SELECT x.*, COUNT(*) rank
FROM
( SELECT id,hometeam team, CASE WHEN homescore > awayscore THEN 'w' ELSE 'l' END result FROM results
UNION
SELECT id,awayteam, CASE WHEN awayscore > homescore THEN 'w' ELSE 'l' END result FROM results
) x
JOIN
( SELECT id,hometeam team, CASE WHEN homescore > awayscore THEN 'w' ELSE 'l' END result FROM results
UNION
SELECT id,awayteam, CASE WHEN awayscore > homescore THEN 'w' ELSE 'l' END result FROM results
) y
ON y.team = x.team
AND y.id <= x.id
GROUP
BY x.id
, x.team
) c
ON c.team = a.team
AND c.rank >= a.rank
AND c.result = a.result
LEFT
JOIN (SELECT x.*, COUNT(*) rank
FROM
( SELECT id,hometeam team, CASE WHEN homescore > awayscore THEN 'w' ELSE 'l' END result FROM results
UNION
SELECT id,awayteam, CASE WHEN awayscore > homescore THEN 'w' ELSE 'l' END result FROM results
) x
JOIN
( SELECT id,hometeam team, CASE WHEN homescore > awayscore THEN 'w' ELSE 'l' END result FROM results
UNION
SELECT id,awayteam, CASE WHEN awayscore > homescore THEN 'w' ELSE 'l' END result FROM results
) y
ON y.team = x.team
AND y.id <= x.id
GROUP
BY x.id
, x.team
) d
ON d.team = a.team
AND d.rank = c.rank + 1
AND d.result = a.result
WHERE a.result = 'w'
AND b.id IS NULL
AND c.id IS NOT NULL
AND d.id IS NULL
GROUP
BY a.team
, a.rank
ORDER
BY streak DESC
LIMIT 1;
+------+--------+
| team | streak |
+------+--------+
| 1 | 3 |
+------+--------+
请注意,这不考虑个人比赛关系(对重复子查询的适度更改),也不考虑两支球队是否拥有最长的相等长度的连胜纪录(需要将此处的所有内容重新加入自身!)。
关于mysql - 查询最长连胜纪录,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/20028832/
我正在尝试编写一个名为 map-longest 的 Clojure 实用函数(感谢备用名称建议)。该函数将具有以下“签名”: (map-longest fun missing-value-seq c1
为什么我创建了一个重复的线程 我在阅读后创建了这个线程 Longest increasing subsequence with K exceptions allowed .我意识到提出问题的人并没有真
我正在编写一个 Sub 来识别 1 到 1000 之间最长的 Collatzs 序列。由于我刚刚开始学习 VBA,我想知道如何添加过程来计算每个序列的长度。 Sub Collatz() Dim i
我正在编写一个 Sub 来识别 1 到 1000 之间最长的 Collatzs 序列。由于我刚刚开始学习 VBA,我想知道如何添加过程来计算每个序列的长度。 Sub Collatz() Dim i
我正在尝试减去 CSV 中的两列以创建第三列“持续时间”结束时间 - 开始时间 每一行也对应一个用户 ID。 我可以创建一个仅包含“持续时间”列的 csv 文件,但我宁愿将其重定向回原始 csv。 例
我在 2018.04 玩这个最长的 token 匹配,但我认为最长的 token 不匹配: say 'aaaaaaaaa' ~~ m/ | a+? | a+ /; # 「a」
因此,按照规范规定最终用户/应用程序提供的给定变量(200 字节)的字节长度。 使用 python 字符串,字符串的最大字符长度是多少,满足 200 字节,因此我可以指定我的数据库字段的 max_le
我需要针对我们的Jenkins构建集群生成每周报告。报告之一是显示具有最长构建时间的作业列表。 我能想到的解决方案是解析每个从属服务器(也是主服务器)上的“构建历史”页面,对于作业的每个构建,都解析该
我正在构建一个 iOS 应用程序,它将流式传输最长为 15 秒的视频。我阅读了有关 HLS 的好文章,因此我一直在对片段大小为 5 秒的视频进行转码。如果视频的第一部分加载时间太长,那么我们可以在接下
docs for Perl 6 longest alternation in regexes punt to Synopsis 5记录 longest token matching 的规则.如果不同的
我是一名优秀的程序员,十分优秀!