mysql - 根据累计运行总计分配标志

Question

我有一个如上所示的表，它来自以下查询：

SELECT f_id, f_datetime, (f_player1_score+f_player2_score) AS legs,
            SUM(f_player1_score+f_player2_score) OVER (ORDER BY f_datetime DESC) AS total_legs,
            IF(DATE(f_datetime) = DATE(NOW()), 1, 0) AS f_status
            FROM results 

我希望我创建的f_status查询列具有以下规则：

1 - If `DATE(f_datetime) = DATE(NOW())` THEN assign `f_status=1`, which is what I've done.
2 - take the maximum total legs where `f_status=1` (i.e. 4 and add 50), then assign `f_status=2` up to total_legs=54,
3) Anything after 2) assign as 3

所需的输出如下：

我知道如何做到这一点，但它需要很多表别名，而且真的很乱。有没有简单的方法来解决它？

谢谢

标签： mysqlsqlpartition

Answer 1

如果我正确地遵循，您可以添加一个CASE带有您想要的布尔逻辑的表达式：

SELECT f_id, f_datetime,
      (f_player1_score+f_player2_score) AS legs,
      SUM(f_player1_score+f_player2_score) OVER (ORDER BY f_datetime DESC) AS total_legs,
       (CASE WHEN DATE(f_datetime) = CURDATE())
             THEN 1
             WHEN SUM(f_player1_score+f_player2_score) OVER (ORDER BY f_datetime DESC) <= 54
             THEN 2
             ELSE 3
        END) AS f_status
FROM results ;

编辑：

如果54只是50加上当天，您可以使用：

SELECT f_id, f_datetime,
      (f_player1_score+f_player2_score) AS legs,
      SUM(f_player1_score + f_player2_score) OVER (ORDER BY f_datetime DESC) AS total_legs,
       (CASE WHEN DATE(f_datetime) = CURDATE()
             THEN 1
             WHEN SUM(f_player1_score + f_player2_score) OVER (ORDER BY f_datetime) <=
                  50 + SUM(CASE WHEN DATE(f_datetime) = CURDATE() THEN f_player1_score + f_player2_score ELSE 0 END)
             THEN 2
             ELSE 3
        END) AS f_status
FROM results ;