我有一个表,其中包含创建该行的日期时间信息。但是,我试图计算在给定时间范围内每天的创作数量。

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/

10-11 22:37
查看更多