如何在单个查询中获得最后一天的总和和计数，
这里是样本数据。
+----+-----------------+---------------------+
| id | transfer_amount | addedon |
+----+-----------------+---------------------+
| 1 | 50000.0000 | 2020-02-20 10:51:12 |
| 2 | 2000.0000 | 2020-02-20 10:52:57 |
| 3 | 10000.0000 | 2020-02-17 10:53:37 |
| 4 | 7000.0000 | 2020-02-17 10:54:28 |
| 5 | 500.0000 | 2020-02-17 10:55:07 |
| 23 | 1000.0000 | 2020-02-19 17:37:06 |
| 24 | 1000.0000 | 2020-02-19 17:41:12 |
| 25 | 1000.0000 | 2020-02-19 17:46:48 |
| 26 | 1000.0000 | 2020-02-19 17:47:17 |
| 30 | 1000.0000 | 2020-02-19 17:58:38 |
+----+-----------------+---------------------+
我试图给出查询，
select SUM(amount) as total_amount, COUNT('ALL') as total_count,
(select SUM(amount) from `transfers`WHERE date > '2021-01-04 23:59:59' AND
date <= '2021-01-05 23:59:59') as last_day_sum
from `transfers`;
和结果
+----------------+-------------+--------------+----------------+
| total_amount | total_count | last_day_sum | last_day_count |
+----------------+-------------+--------------+----------------+
| 314286380.0000 | 88452 | 1200.0000 | 0 |
+----------------+-------------+--------------+----------------+
还有其他选择吗？
您可以使用此查询
select SUM(amount) as total_amount, COUNT('ALL') as total_count,
SUM(case when date > '2021-01-04 23:59:59' AND
date <= '2021-01-05 23:59:59' then amount else 0 end) as last_day_sum
from `transfers`;
更新最后一天计数
select SUM(amount) as total_amount, COUNT('ALL') as total_count,
SUM(case when date > '2021-01-04 23:59:59' AND
date <= '2021-01-05 23:59:59' then amount else 0 end) as last_day_sum,
SUM(date > '2021-01-04 23:59:59' AND
date <= '2021-01-05 23:59:59') as last_day_count
from `transfers`;
较短代码
select sum(transfer_amount) as total_amount, count(*) as total_count,
sum(if(date(`date`) = '2021-01-05', transfer_amount, 0)) as last_day_sum,
sum(date(`date`) = '2021-01-05') as last_day_count
from `transfers`;
select SUM(transfer_amount) as total_amount, COUNT(*) as total_count,
(select ifnull(SUM(transfer_amount),0) from transfers WHERE date(addedon) = '2020-02-19') as last_day_sum
from transfers;
您必须将日期字符串转换为日期类型
此外，由于您只需要日期，因此可以将其缩减为上面的日期，而不是where x and y。这将在同一查询中给出最后24小时的总和。