Mysql查询结果匹配不起作用

Question

我的简单表以这种方式格式化(4个字段)和超过100行：

id结果id_user数据

1 0 11 2016-10-15 18:00

2 0 13 2016-10-15 18:00

3 0 16 2016-10-15 18:00

4 0 24 2016-10-15 18:00:00

5 0 6 2016-10-15 18:00:00

6 3 5 2016-10-15 18:00

7 3 45 2016-10-15 18:00

8 3 66 2016-10-15 18:00

2016年4月9日3-10-15 18:00

10 3 33 2016-10-8 17:00

11 1 55 2016-10-8 17:00

12 1 101 2016-10-8 17:00

……

按照这种逻辑，我每周保存10个记录:如果一个团队获胜，我将为团队的每个成员节省3分，并将0分留给每一个松散的团队成员。(如果他们抽签，我将把1分给所有10个成员)，以此类推。

现在我的问题是如何提取这个表中保存的前3名球员，他们至少打了5场比赛，在过去的5场比赛中，他们得到了主要的分数。

我以这样的方式格式化我的查询：

(SELECT m1.id_user, m1.data, SUM(result) AS ris 
FROM my_table m1 
JOIN(SELECT id_user 
      FROM my_table 
      GROUP BY id_user 
      HAVING COUNT(id_user) >= 5) m2 
ON m2.id_user = m1.id_user 
GROUP BY id_user 
ORDER BY m1.data DESC) 
ORDER BY ris DESC LIMIT 3;

但是这个查询考虑了所有比赛中的点数，而不仅仅是在最后的5场比赛中！如果有可能，如何在我的查询中插入此条件？

谢谢你帮忙。

Answer 1

没有一种非常有效的方法可以做到这一点，但这是可能的，即使没有MySQL对窗口函数的支持。它有助于保持简单的分数是个位数，只有两个可能的非零分数。

select id_user, 
    3*length(replace(replace(left(group_concat(result order by data desc separator ''), 5), '0', ''), '1', ''))
        +length(replace(replace(left(group_concat(result order by data desc separator ''), 5), '0', ''), '3', '')) 
        as score_sum 
from my_table 
group by 1 
having count(*) >= 5 
order by 2 desc 
limit 3;

把它拆开：

group by 1是group by id_user (select中的第一个值)的缩写，因此它将只返回每个用户一行。

group_concat(result order by data desc separator '')将用户的所有分数连在一起，最近的一次是第一次，没有在它们之间添加分隔符字符串，为用户生成一个类似于“033101003”的字符串。group_concat会在特定的(可配置的)长度之后截断数据并产生警告，但这里并不关心这一点。

left(..., 5)为用户取最左边(最近的)5分(例如'03310')。

替换(.，'0'，'')移除0's，因此上面的示例变成'331‘。

我们用这个字符串数多少个3，用‘(产生'33')替换1's，取长度(2)。

同样，我们用这个字符串来计算多少个1，用‘(产生'1')替换3's，然后取长度(1)。

3*...+...通过将3's数的3倍加到1的数中来计算分数之和(2*3+1是最近5个分数(0，3，3，1，0，1，0，3)中的7之和)。

我采取这种方法是因为只有两个非零分数需要处理。如果有更多的话，我就会用不同的计数技巧。而不是删除我不想要的数字，我会数一数我做了。你这样做是通过说(length(string)-length(replace(string,searchstring,'')))/length(searchstring)。例如。要计算abccbacba中的c's，可以删除c's (产生abbaba)，并从原始字符串的长度(9，得到3的结果)中减去那个(6)的长度。

Answer 2

您能尝试将SUM(结果)放到子查询中吗？

选择m1.id_user，m1。数据，结果作为ris从my_table m1联接(选择id_user，和(结果)作为结果，从my_table组中按id_user有计数( id_user ) >= 5) m2在m2.id_user = m1.id_user组上按m1顺序排列，数据DESC )顺序由ris DESC限制3；