mysql - 如何只显示当前的记录

Question

我只想选择当天的记录。这样我每天就可以看到有多少访客(“bezoekers”)。

这是我使用的代码:

Select FROM_UnixTime((sensordata1.time), '%Y-%c-%d-%H') "Datum (Descending)", 
COUNT(DISTINCT address) "Bezoekers"  
FROM sensordata1  
ORDER BY 1
FROM_UnixTime((sensordata1.time), '%Y-%c-%d-%H') DESC

我尝试过:

Select FROM_UnixTime((sensordata1.time), '%Y-%c-%d-%H') "Datum (Descending)",
COUNT(DISTINCT address) "Bezoekers"  
FROM sensordata1  
WHERE 1, DATE('Datum') = DATE(CURDATE() 
ORDER BY 1
FROM_UnixTime((sensordata1.time), '%Y-%c-%d-%H') DESC

我想要什么:

Datum         | Bezoekers
2017-4-20-15  | 31
2017-4-20-14  | 34
2017-4-20-13  | 20
2017-4-20-12  | 26

我不想要其中包含例如 2017-04-21 的内容。如果我第二天回来，它必须是从那天开始，所以你不能设定日期。

Answer 1

您缺少GROUP BY。如果您想要每个 FROM_UnixTime(sensordata1.time, '%Y-%c-%d-%H') 一个结果行，则按其分组。

Select
  FROM_UnixTime(sensordata1.time, '%Y-%c-%d-%H') AS "Datum (Descending)", 
  COUNT(DISTINCT address) AS "Bezoekers"  
FROM sensordata1  
GROUP BY FROM_UnixTime(sensordata1.time, '%Y-%c-%d-%H')
ORDER BY FROM_UnixTime(sensordata1.time, '%Y-%c-%d-%H') DESC;

仅当天相同(添加了相应的 WHERE 子句):

Select
  FROM_UnixTime(sensordata1.time, '%Y-%c-%d-%H') AS "Datum (Descending)", 
  COUNT(DISTINCT address) AS "Bezoekers"  
FROM sensordata1  
WHERE FROM_UnixTime(sensordata1.time, '%Y-%m-%d') = curdate()
GROUP BY FROM_UnixTime(sensordata1.time, '%Y-%c-%d-%H')
ORDER BY FROM_UnixTime(sensordata1.time, '%Y-%c-%d-%H') DESC;