如何按DATE_FORMAT(DATE，&#39；%Y-%m-%d&39；)限制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;

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

Here's a sample fiddle供参考。

这是在较早的MySQL版本中复制ROW_NUMBER()函数的一种方式：

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

Fiddle

虽然(在极少数情况下)我有过这样做的经验，但这并没有达到我预期的效果：行号有时会出错。

随着进一步的测试，这种罕见的情况似乎正在这里发生。因为您的原始查询包括聚合，所以我将其与V8兼容ROW_NUMBER()进行比较，得到不同的结果。根据观察，行号是在ORDER BY xxx DESC出现之前分配的。因此，从视觉上看，它确实按date进行相应的降序排序，但是rn列"；生成错误。我发现的解决办法是首先在子查询中执行聚合，然后只尝试生成自定义行号。请参阅我更新的查询并处理上面的内容。