首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >在计算用户变量之前,使用强制索引确保表使用GROUP BY和ORDER BY进行排序

问在计算用户变量之前,使用强制索引确保表使用GROUP BY和ORDER BY进行排序
EN

Stack Overflow用户
提问于 2020-12-31 02:55:30
回答 2查看 42关注 0票数 1

我正在尝试对最高的第n行求和。

我正在计算一个自行车排行榜,其中第一个最快的车手在一个事件中得到50分,第二个最快的49分,以此类推。联盟中有10个项目,但只使用了一个车手的8个最好的结果(这意味着一个车手可以错过最多2个项目,而不会在排行榜上出现灾难性的下降)首先我需要一个表,其中每个车手来自联盟中所有项目的结果被分组在一起,并按最高点的顺序列出,然后计算出一个序列号,这样我就可以对8个或更少的最好结果求和。所以我使用这个表select:

代码语言:javascript
复制
set @r := 0, @rn := 0 ;
SELECT 
t.*,
@rn := if(@r = t.id_rider, @rn + 1, 1) as seqnum,
@r :=  t.id_rider as dummy_rider
from results as t 
ORDER BY t.id_rider, t.points desc

其中表results是一个视图,如下所示:

代码语言:javascript
复制
SELECT 
        a.id_rider,
        b.id_event,
        b.race_no,
        b.id_race,
        b.id_race_type,
        b.`position`,
        c.id_league,
        (51 - b.`position`) AS points
    FROM
    wp_dtk_start_sheet a
    JOIN wp_dtk_position_results b ON a.id_event = b.id_event AND a.race_no = b.race_no
    JOIN wp_dtk_league_races c ON b.id_race = c.id_race
    WHERE
    c.id_league = 1
    AND b.`position` IS NOT NULL

这不起作用,因为所有结果的seqnum都为1。如果我将视图表导出到excel中,并创建一个具有相同列和数据的测试表,它可以正常工作。我认为错误的地方在于,在运行变量之前,ORDER BY t.id_rider, t.points desc没有对表进行排序

这个引用:https://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/声明“这种技术几乎是不确定的,因为它依赖于你和我不能直接控制的东西,比如MySQL决定使用哪些索引进行分组”。

此参考建议尝试强制索引使用id_rider,因此我尝试了:

代码语言:javascript
复制
set @r := 0, @rn := 0 ;
SELECT 
a.id_rider, 
c.id_league, 
(51- b.`position`) as points,
@rn := if(@r = a.id_rider, @rn + 1, 1) as seqnum,
@r :=  a.id_rider as 'set r'
from wp_dtk_start_sheet as a force index (id_rider)
join wp_dtk_position_results as b on a.id_event = b.id_event and a.race_no = b.race_no
join wp_dtk_league_races as c on b.id_race = c.id_race
where c.id_league = 1 and b.`position` is not null   
ORDER BY a.id_rider, points desc

这不起作用,像以前一样,所有行的seqnum =1

我的表结构如下:

表a- wp_dtk_start_sheet

​

​

表b- wp_dtk_position_results

​

​

表c -wp_dtk_league_races

​

​

这个堆栈溢出的答案也很有帮助,但也有同样的问题:Sum Top 10 Values

有人能帮上忙吗?也许我做错了这件事?

EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2020-12-31 03:14:00

如果使用window functions,解决方案就会清晰得多。这允许您指定每个组中行的顺序,以便对行进行编号。

代码语言:javascript
复制
SELECT t.*
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY id_rider ORDER BY points DESC) AS seqnum
  FROM results
) AS t
WHERE t.seqnum <= 8;

对MySQL中的窗口函数的支持是在8.0版中引入的,因此您可能需要升级。但自2018年以来,它一直是MySQL产品的一部分。

票数 1
EN

Stack Overflow用户

发布于 2021-01-02 19:37:38

Bill的回答非常有效,但我也将其合并到一条语句中,这就是组合的select命令:

代码语言:javascript
复制
Select
t.id_rider,
sum(points) as total
from 
(SELECT 
a.id_rider, 
c.id_league, 
(51- b.`position`) as points,
ROW_NUMBER() OVER (PARTITION BY id_rider ORDER BY points DESC) AS seqnum
from wp_dtk_start_sheet as a 
join wp_dtk_position_results as b on a.id_event = b.id_event and a.race_no = b.race_no
join wp_dtk_league_races as c on b.id_race = c.id_race
where c.id_league = 1 and b.`position` is not null  ) as t 
where seqnum <= 8
group by id_rider
order by total desc
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/65512165

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档