如何编写SQL SELECT语句统计每日ACW_TIME大于180的次数？

先把你提供的原始数据整理出来方便参考：

TimeStamp ACW_TIME 
2017-09-25T00:04:41 178 
2017-09-25T00:09:41 181 
2017-09-26T00:14:41 183 
2017-09-26T00:19:41 184 
2017-09-26T00:24:41 186 
2017-09-27T00:29:42 188 
2017-09-28T00:34:42 188 
2017-09-28T00:39:41 188 

你的需求核心是提取时间戳的日期部分，再按日期分组统计符合ACW_TIME > 180条件的记录数。下面针对几种常用数据库给出对应的SQL语句：

MySQL/MariaDB #

用DATE()函数直接提取日期：

SELECT 
    DATE(`TimeStamp`) AS day_date,
    COUNT(*) AS acw_over_180_count
FROM 
    your_table_name
WHERE 
    ACW_TIME > 180
GROUP BY 
    DATE(`TimeStamp`)
ORDER BY 
    day_date;

SQL Server #

通过CAST()将时间戳转为日期类型：

SELECT 
    CAST([TimeStamp] AS DATE) AS day_date,
    COUNT(*) AS acw_over_180_count
FROM 
    your_table_name
WHERE 
    ACW_TIME > 180
GROUP BY 
    CAST([TimeStamp] AS DATE)
ORDER BY 
    day_date;

PostgreSQL #

用DATE_TRUNC()截断到日期维度：

SELECT 
    DATE_TRUNC('day', "TimeStamp")::DATE AS day_date,
    COUNT(*) AS acw_over_180_count
FROM 
    your_table_name
WHERE 
    ACW_TIME > 180
GROUP BY 
    DATE_TRUNC('day', "TimeStamp")::DATE
ORDER BY 
    day_date;

Oracle #

使用TRUNC()函数截取日期部分：

SELECT 
    TRUNC("TimeStamp", 'DD') AS day_date,
    COUNT(*) AS acw_over_180_count
FROM 
    your_table_name
WHERE 
    ACW_TIME > 180
GROUP BY 
    TRUNC("TimeStamp", 'DD')
ORDER BY 
    day_date;

注意事项 #

• 记得把your_table_name替换成你实际的表名
• GROUP BY子句里的日期处理逻辑要和SELECT中的保持一致，避免分组错误
• ORDER BY是为了让结果按日期顺序排列，方便查看

按照你提供的数据，执行后会得到如下结果：

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