c# - 选择第 7 个人(行)并得到 7 作为结果
问题描述
我想选择第 7 个人(Pierre Gasly(例如))Score list Season F1,所以我可以在我的 C# 应用程序中返回数字 7。
SELECT CONCAT(strVoorNaam, strTussenVoegsel, ' ', strAchterNaam) AS Naam, SUM(CAST(tblPunten.strScore AS INT)) AS score
FROM tblPunten
JOIN tblPuntenCoureur ON tblPuntenCoureur.PuntenID = tblPunten.ID
JOIN tblPersoon ON tblPersoon.ID = tblPuntenCoureur.PersoonID
JOIN tblSeizoen ON tblSeizoen.ID = tblPuntenCoureur.SeizoenID
WHERE tblPunten.ID != 12 AND tblPunten.ID != 13 AND tblPunten.ID != 14 AND tblPunten.ID != 15
AND tblPunten.ID != 16 AND tblPunten.ID != 17 AND tblPunten.ID != 18 AND tblPunten.ID != 19 AND tblSeizoen.intJaartal = 2019
GROUP BY CONCAT(strVoorNaam, strTussenVoegsel, ' ', strAchterNaam), tblPersoon.strAchterNaam
ORDER BY score DESC
解决方案
使用行限制子句:
select
concat(strvoornaam, strtussenvoegsel, ' ', strachternaam) as naam,
sum(cast(pu.strscore as int)) as score
from tblpunten pu
join tblpuntencoureur pc on pc.puntenid = pu.id
join tblpersoon pe on pe.id = pc.persoonid
join tblseizoen se on se.id = pc.seizoenid
where pu.id not between 12 and 19 and se.intjaartal = 2019
group by strvoornaam, strtussenvoegsel, strachternaam, pe.strachternaam
order by score desc
offset 6 rows fetch next 1 row only
这为您提供了结果集中的第七行。
旁注:
- 表别名有助于保持查询简洁且易于编写
- 您应该对查询中起作用的所有列进行限定
另一方面,如果你想获得特定人的等级,那就不同了。您可以rank()
改用:
select *
from (
select
concat(strvoornaam, strtussenvoegsel, ' ', strachternaam) as naam,
sum(cast(pu.strscore as int)) as score,
rank() over(order by sum(cast(pu.strscore as int)) desc) rn
from tblpunten pu
join tblpuntencoureur pc on pc.puntenid = pu.id
join tblpersoon pe on pe.id = pc.persoonid
join tblseizoen se on se.id = pc.seizoenid
where pu.id not between 12 and 19 and se.intjaartal = 2019
group by strvoornaam, strtussenvoegsel, strachternaam, pe.strachternaam
) t
where naam = 'Pierre Gasly'
推荐阅读
- java - Java,groovy REST 客户端比 Postman 慢 30 倍以上
- vue.js - Axios 拦截器仅在页面重新加载后工作
- html - 使用后代选择器的响应式网站
- firebase - 计数值匹配的位置,并检索计数和值 Firebase 和 Vue
- javascript - 方法不返回值位警报框显示值 react.js
- javascript - Gatsby JS 中的自定义 Javascript
- delphi - 在 Delphi 5 中实现埃拉托色尼筛法
- java - 如何从 Java 中的 TextField 中获取字符串
- java - 将 XSSFWorkbook excel 文件写入响应流
- ios - “AuthDataResult”类型的 Firebase 错误值没有成员“displayName”