如何按日期对表中qty列求和并按日期排序？

没问题，这是个典型的按日期维度聚合数据的需求，下面针对不同主流数据库给出具体实现，完美匹配你想要的输出格式：

你的原始数据 #

qty dttime
15.83 2015-04-28 18:58:25.000
3.78 2015-04-28 18:58:27.000
29.39 2015-04-28 18:59:41.000
25.6 2015-04-29 19:01:23.000
2.39 2015-04-29 19:04:24.000
8.9 2015-04-30 19:07:25.000
8.18 2015-04-30 19:10:32.000
1.92 2015-04-30 19:13:48.000

期望输出 #

qty dttime
49 2015-04-28
27.99 2015-04-29
19 2015-04-30

1. MySQL / MariaDB 写法 #

SELECT 
    ROUND(SUM(qty), 2) AS qty,
    DATE(dttime) AS dttime
FROM your_table_name
GROUP BY DATE(dttime)
ORDER BY dttime ASC;

• DATE(dttime)：把带时间戳的字段提取出纯日期部分，实现按天分组
• ROUND(SUM(qty), 2)：对每天的qty求和后保留两位小数，和你的期望输出格式对齐
• GROUP BY DATE(dttime)：按日期维度聚合数据
• ORDER BY dttime ASC：确保结果按日期升序排列

2. SQL Server 写法 #

SQL Server没有直接的DATE()函数，用CAST或CONVERT提取日期：

SELECT 
    ROUND(SUM(qty), 2) AS qty,
    CAST(dttime AS DATE) AS dttime
FROM your_table_name
GROUP BY CAST(dttime AS DATE)
ORDER BY dttime ASC;

或者用CONVERT的等价写法：

SELECT 
    ROUND(SUM(qty), 2) AS qty,
    CONVERT(DATE, dttime) AS dttime
FROM your_table_name
GROUP BY CONVERT(DATE, dttime)
ORDER BY dttime ASC;

3. PostgreSQL 写法 #

可以用类型转换运算符::DATE或者DATE()函数：

SELECT 
    ROUND(SUM(qty), 2) AS qty,
    dttime::DATE AS dttime
FROM your_table_name
GROUP BY dttime::DATE
ORDER BY dttime ASC;

注意事项 #

• 记得把your_table_name替换成你实际使用的表名
• 如果不需要严格保留两位小数（比如求和刚好是整数时自动显示整数），可以去掉ROUND()函数，数据库会根据求和结果自动适配格式

内容的提问来源于stack exchange，提问作者user3997016