如何返回每匹马的最后9个结果
如何返回每匹马的最后9个结果
提问于 2019-05-29 23:16:31
我通过sql查询，列出了以前的比赛中所有的马的表现。我只想列出他们最近的9次表演。
我在不同的网站上搜索了一段时间，试图找到一个答案，但没有找到合适的。如果有任何帮助，我将不胜感激。
SELECT Form1.Horse, Form1.Date, Form1.[Mile Rate], Form1.[Beaten M], Form1.Distance, Form1.[M or S], Form1.[Class], Form1.[1Q], Form1.[2Q], Form1.[3Q], Form1.Bar, Form1.Driver, Form1.Comment, Form1.Trainer, Form1.Adj, Form1.[Race%], Form1.[Bell Pos], Form1.Pegs, Form1.[Adj LH], Form1.[M LastL], Form1.[Track]
FROM Form1
WHERE Horse in ('SOHO PICASSO','LITTLE BITOF FUN','BEEJAYS STAR','MISS STARFIRE','MEDIEVAL MAN','BLUE STONE','SOHO MAJOR PLAYER NZ','HUMBLE FELLA','STARS OF GOLD','MISS SERENA','MISS MARGARETA','NIGHTWATCH STAR','PURE IMAGE','VILLAGE BENNY','CAPTAIN CROISSANT','JESSIE JOAN','THE GLASS CEILING','BROOK PARKS LAST','ANTAGONISTIC NZ');Stack Overflow用户
发布于 2019-05-29 23:24:56
使用ROW_NUMBER函数。
SELECT*
FROM(
SELECT Form1.Horse, Form1.Date, Form1.[Mile Rate], Form1.[Beaten M], Form1.Distance, Form1.[M or S], Form1.[Class], Form1.[1Q], Form1.[2Q], Form1.[3Q], Form1.Bar, Form1.Driver, Form1.Comment, Form1.Trainer, Form1.Adj, Form1.[Race%], Form1.[Bell Pos], Form1.Pegs, Form1.[Adj LH], Form1.[M LastL], Form1.[Track]
,ROW_NUMBER() OVER(PARTITION BY Horse ORDER BY Date) rn
FROM Form1
WHERE Horse in ('SOHO PICASSO','LITTLE BITOF FUN','BEEJAYS STAR','MISS STARFIRE','MEDIEVAL MAN','BLUE STONE','SOHO MAJOR PLAYER NZ','HUMBLE FELLA','STARS OF GOLD','MISS SERENA','MISS MARGARETA','NIGHTWATCH STAR','PURE IMAGE','VILLAGE BENNY','CAPTAIN CROISSANT','JESSIE JOAN','THE GLASS CEILING','BROOK PARKS LAST','ANTAGONISTIC NZ')
)T
WHERE rn <= 9如果您数据库不支持ROW_NUMBER，请使用该查询：
SELECT Form1.Horse, Form1.Date, Form1.[Mile Rate], Form1.[Beaten M], Form1.Distance, Form1.[M or S], Form1.[Class], Form1.[1Q], Form1.[2Q], Form1.[3Q], Form1.Bar, Form1.Driver, Form1.Comment, Form1.Trainer, Form1.Adj, Form1.[Race%], Form1.[Bell Pos], Form1.Pegs, Form1.[Adj LH], Form1.[M LastL], Form1.[Track]
FROM (
SELECT t1.Horse, t1.Date, COUNT(*) AS rn
FROM Form1 AS t1
INNER JOIN Form1 AS t2
ON t2.Horse = t1.Horse
AND t2.Date >= t1.Date
GROUP BY t1.Horse, t1.Date
HAVING COUNT(*) <= 9
)t
INNER JOIN Form1
ON Form1.Horse = t.Horse
AND Form1.Date = t.Date内部查询模拟ROW_NUMBER并使用rn <= 9过滤到行。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56363789
复制相关文章
相似问题
腾讯云开发者
