我有一个表,其中包含创建该行的日期时间信息。但是,我试图计算在给定时间范围内每天的创作数量。
select count(*), DateCreated FROM Stories WHERE DateCreated BETWEEN '2013-03-28' AND '2014-01-07' group by DateCreated;
但是,由于我将它们按DateCreated分组,因此尽管知道同一天创建了多个故事,但所有内容的计数均为1。 datetime字段的时间部分使每一个都足够独特,可以进行分组。
有没有一种方法可以修改查询以忽略datetime的时间部分并仅使用date来区分group by?
最佳答案
SELECT
NumPerDay = count(*),
DateCreated = cast(DateCreated as Date)
FROM
Stories
WHERE
cast(DateCreated as Date) BETWEEN '2013-03-28' AND '2014-01-07'
GROUP BY
cast(DateCreated as Date)
要么
SELECT
NumPerDay = count(*),
DateCreated = DATE(DateCreated)
FROM
Stories
WHERE
Date(DateCreated) BETWEEN '2013-03-28' AND '2014-01-07'
GROUP BY
DATE(DateCreated)
或者,如果在where子句中强制转换Datecolumn,则无法使用可用索引:
SELECT
NumPerDay = count(*),
DateCreated = DATE(DateCreated)
FROM
Stories
WHERE
DateCreated BETWEEN '2013-03-28 00:00:00' AND '2014-01-07 23:59:59.999'
GROUP BY
DATE(DateCreated)
关于mysql - 仅基于数据而不是日期和时间来搜索MySQL数据库,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/23098441/