检索每个名称的前n-4个点的总和

l7mqbcuq  于 2021-06-18  发布在  Mysql
关注(0)|答案(7)|浏览(426)

如何检索每个名称前n-4个点的总和。我们可以在一个sql语句中不使用用户定义的会话变量来实现这一点吗?
输入:看下面或这里(图片)
输出:看下面或这里(图片)
总结:
杰夫=7+5+4+4=20
凯特=8+5+5+4=22
尼尔=10+7+5+0=22
里克=9+8+3+2=22
输入数据库:
±-----±

kfgdxczn

kfgdxczn1#

-±-------+
谢谢您!!

lskq00tm

lskq00tm2#

mysql 8+中最简单的解决方案是:

select name, sum(points)
from (select t.*, row_number() over (partition by name order by points desc) as seqnum
      from t
     ) t
where seqnum <= 4
group by name;

在mysql的早期版本中,您需要做更多的工作。这样的事情似乎可以解决你的问题:

select t.name, sum(t.points)
from t
where t.assignment in (select t2.assignment
                       from t t2
                       where t2.name = t.name
                       order by t2.points desc
                       limit 4
                      )
group by t.name;

但是mysql不允许这种语法。如果没有重复的点,那么这将起作用:

select t.name, sum(t.points)
from t
where t.points >= coalesce( (select t2.points
                             from t t2
                             where t2.name = t.name
                             order by t2.points desc
                             limit 1 offset 3
                            ), 0)
group by t.name;

但如果第4行和第5行的点数相同,那么总和中就有4行以上。
现在,你可以用变量来解决这个问题。但我们可以采用最后一种解决方案:

select t.name,
       (case when count(*) = 4 then sum(t.points)
             else sum(t.points) - (count(*) - 4) * min(t.points)
        end)
from t
where t.points >= coalesce( (select t2.points
                             from t t2
                             where t2.name = t.name
                             order by t2.points desc
                             limit 1 offset 3
                            ), 0)
group by t.name;

这会考虑到任何关系。严格来说 case 是没有必要的,但我认为它使逻辑明确。
编辑:
如果你想要除倒数第四名以外的所有人(你的问题不清楚),同样的方法也适用:

select t.name,
       (case when count(*) = 4 then sum(t.points)
             else sum(t.points) - (count(*) - 4) * min(t.points)
        end)
from t
where t.points >= coalesce( (select t2.points
                             from t t2
                             where t2.name = t.name
                             order by t2.points asc
                             limit 1 offset 3
                            ), 0)
group by t.name;

唯一的区别是颠倒顺序。

gdx19jrr

gdx19jrr3#

-±-------+
|名称|分配|点|
±-----±

gc0ot86w

gc0ot86w4#

-±-------+
|杰夫| 1 | 7|
|杰夫| 2 | 5|
|杰夫| 3 | 3|
|杰夫| 4 | 4|
|杰夫5 4|
|凯特1 4|
|凯特| 2 | 8|
|凯特| 3 | 5|
|凯特| 4 | 3|
|凯特| 5 | 5|
|尼尔| 1 | 5|
|尼尔| 2 | 7|
|尼尔| 3 | 10|
|尼尔| 4 | 0|
|尼尔| 5 | 0|
|里克| 1 | 2|
|里克| 2 | 1|
|里克| 3 | 8|
|里克| 4 | 3|
|里克| 5 | 9|
±-----±

szqfcxe2

szqfcxe25#

-±-------+
|杰夫| 20|
|凯特| 22|
|尼尔| 22|
|里克| 22|
±-----±

t2a7ltrp

t2a7ltrp6#

-±-------+
|名称|分配|点|
±-----±

hsgswve4

hsgswve47#

-±-------+
输出:±-----±

相关问题