MySQL查询提取日期+按天分组

Question

简单问题：为什么以下查询没有输出perday？

SELECT FROM_UNIXTIME(`date`,"%Y-%m-%d") AS `perday`, COUNT(*) AS `count` 
FROM `data` 
WHERE `group` = 1
GROUP BY `perday`

计数正确输出，但perday保持为空。 data表格如下：

 |   id   |   group   |          date          |
------------------------------------------------
 |   1    |     1     |  2013-04-13 06:01:02   |
 |   2    |     1     |  2013-04-13 14:24:18   |
 |   3    |     2     |  2012-01-21 21:33:03   |
              Ect.

谢谢！

编辑：

期望的输出结果：

 |   perday   |
 --------------
 | 2013-04-13 |
 | 2012-01-21 |

- Laurent

---

你期望得到什么样的输出？ - juergen d

我把预期结果放在编辑器里了... 为什么要踩我？ - Laurent

Answer 1

删除WHERE子句。

SELECT  FROM_UNIXTIME(date,'%Y-%m-%d') AS perday, 
        SUM(`group` = 1)  AS `count` 
FROM    data 
GROUP   BY FROM_UNIXTIME(date,'%Y-%m-%d')

如果日期的格式为2013-04-13 06:01:02，那么为什么要使用FROM_UNIXTIME呢？难道不是应该用DATE_FORMAT吗？

SELECT  DATE_FORMAT(date, '%Y-%m-%d') AS perday, 
        SUM(`group` = 1)  AS `count` 
FROM    data 
GROUP   BY DATE(date)
ORDER   BY date

这将显示表中所有可用的日期。

• SQLFiddle演示

但是，如果您只想要选定的group，

SELECT  DATE_FORMAT(date, '%Y-%m-%d') AS perday, 
        COUNT(*)  AS `count` 
FROM    data 
WHERE   `group` = 1
GROUP   BY DATE(date)
ORDER   BY date

- John Woo

Answer 2

你可能在寻找这个

    SELECT DATE_FORMAT(`date`,"%Y-%m-%d") AS `perday`, COUNT(*) AS `count` 
    FROM `data` 
    WHERE `group` = 1
    GROUP BY `perday`

这里演示

- echo_Me

Answer 3

MySQL的DATE()函数可以从一个DATETIME表达式中提取出日期部分。根据您的期望输出，这个查询是正确的。

SELECT
  DATE(`date`) AS `perday`,
  COUNT(*) AS `count`
FROM `data`
GROUP BY `perday`

- Deval Shah