如何按 DATE_FORMAT(date,'%Y-%m-%d') 限制 20 对值求和，如果大于 20，则对剩余值求和？

如何求和DATE_FORMAT(date,'%Y-%m-%d')id 前 20 行数据，如果大于 20，则对剩余值求和，否则为 0？假设我有以下数据和以下 SQL，该怎么做？非常感谢您的任何建议。

SELECT SUM(value), id, DATE_FORMAT(date,'%Y-%m-%d') 
FROM test_table
GROUP BY id, DATE_FORMAT(date,'%Y-%m-%d')

sum(value)  id  DATE_FORMAT(date,'%Y-%m-%d')
-------------------------------------------- 
64.98   123456  2021-01-01
64.98   123456  2021-01-02
64.98   123456  2021-01-03
64.98   123456  2021-01-04
64.98   123456  2021-01-05
64.98   123456  2021-01-06
72.34   123456  2021-01-07
64.98   123456  2021-01-08
64.98   123456  2021-01-09
103.80  123456  2021-01-10
64.98   123456  2021-01-11
64.98   123456  2021-01-12
64.98   123456  2021-01-13
64.98   123456  2021-01-14
64.98   123456  2021-01-15
64.98   123456  2021-01-16
64.98   123456  2021-01-17
64.98   123456  2021-01-18
64.98   123456  2021-01-19
64.98   123456  2021-01-20
64.98   123456  2021-01-21
64.98   123456  2021-01-22
64.98   123456  2021-01-23
64.98   123456  2021-01-24
144.98  123456  2021-01-25
64.98   123456  2021-01-26
64.98   123456  2021-01-27
64.98   123456  2021-01-28
64.98   123456  2021-01-29
64.98   123456  2021-01-30
64.98   123456  2021-01-31
64.98   123456  2021-02-01
64.98   123456  2021-02-02
64.98   123456  2021-02-03
64.98   123456  2021-02-04
720.92  123456  2021-02-05
66.98   123456  2021-02-06
66.98   123456  2021-02-07
66.98   123456  2021-02-08
64.98   123456  2021-02-09
64.98   123456  2021-02-10
64.98   123456  2021-02-11
64.98   123456  2021-02-12
64.98   123456  2021-02-13
64.98   123456  2021-02-14
64.98   123456  2021-02-15
64.98   123456  2021-02-16
64.98   123456  2021-02-17
64.98   123456  2021-02-18
64.98   123456  2021-02-19
64.98   123456  2021-02-20
64.98   223456  2021-01-20
54.98   223456  2021-01-21

......................................

如果您使用支持窗口函数的 MySQL 版本，那么可能：

SELECT id,
       SUM(CASE WHEN rn <= 20 THEN vals ELSE 0 END) AS 'SumOf1st20',
       SUM(CASE WHEN rn > 20 THEN vals ELSE 0 END) AS 'SumOFOthers'
FROM
(SELECT SUM(value) AS vals, id, DATE_FORMAT(date,'%Y-%m-%d') AS dt,
       ROW_NUMBER() OVER (PARTITION BY id ORDER BY dt DESC) AS rn
FROM test_table
GROUP BY id, dt) v
GROUP BY id;

但我想知道这是否正是您想要的，因为您的示例数据似乎是原始查询的结果。

以供参考。

这是一种复制方法ROW_NUMBER()旧 MySQL 版本中的函数：

SELECT t1.*,
       CASE WHEN id=@idx THEN @rnk := @rnk+1 
        ELSE @rnk := 1 END AS rn,
        @idx := id
 FROM (SELECT id, 
          SUM(value) AS vals, DATE_FORMAT(date,"%Y-%m-%d") AS dt 
   FROM test_table 
   GROUP BY id, dt)t1
 CROSS JOIN (SELECT @rnk := 0, @idx := NULL) r
 ORDER BY id, dt DESC

不过，（在极少数情况下）我有过这样的经验：行编号有时会变得混乱。

经过进一步的测试，这种“罕见的情况”似乎正在发生在这里。由于您的原始查询包含聚合，因此我将其与 v8 兼容的进行比较ROW_NUMBER()我得到了不同的结果。根据观察，行号是在ORDER BY xxx DESC发生。所以，从视觉上看，它确实按date相应地下降，但rn column “不正确地”生成的。我发现的解决方法是首先在子查询中执行聚合，然后仅尝试生成自定义行编号。请参阅上面我更新的查询和小提琴。