获取过去30天中每一天的mysql行数

guz6ccqo 于 2021-06-21 发布在 Mysql

关注(0)|答案(3)|浏览(300)

我正在使用一个3列的表，试图获得过去30天中每天的唯一条目数。

|RecordNumber|extension|date|

我以为我在这方面有所进展

SELECT DATE_FORMAT(date, '%m/%d/%Y') AS Dates, extension 
   FROM API_phones 
  WHERE date LIKE '%' AND date BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
  ORDER BY Dates ASC

但我不知道怎么完成。
基本上，在过去的30天里每天有多少个条目。

mysql

来源：https://stackoverflow.com/questions/51292775/getting-a-mysql-count-of-rows-for-each-of-the-last-30-days

3条答案

按热度按时间

yc0p9oo01#

如果您只需要每天计数，请使用以下方法：

SELECT DATE_FORMAT(date, '%m/%d/%Y') AS Dates, count(*) as count 
   FROM API_phones 
  WHERE date BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
Group by
  Dates
  ORDER BY Dates ASC

如果您每天每次延期都需要它，您可以尝试以下方法：

SELECT DATE_FORMAT(date, '%m/%d/%Y') AS Dates, extension, count(*) as count 
   FROM API_phones 
  WHERE date BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
Group by
  Dates, extension
  ORDER BY Dates ASC

赞(0）回复(0）举报 2021-06-21

hwazgwia2#

SELECT DATE_FORMAT(date, '%m/%d/%Y') AS Dates, COUNT(*) AS entry_count
   FROM API_phones 
  WHERE date LIKE '%' AND date BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
  GROUP BY Dates
  ORDER BY Dates ASC

赞(0）回复(0）举报 2021-06-21

4c8rllxm3#

如果您需要每天的条目数，您应该使用count（*）和group by

SELECT DATE_FORMAT(date, '%m/%d/%Y') AS Dates, count(*) num_entries 
   FROM API_phones 
   WHERE date BETWEEN NOW() - INTERVAL 30 DAY AND NOW() 
   GROUP BY Dates
   ORDER BY Dates ASC

赞(0）回复(0）举报 2021-06-21

我来回答

获取过去30天中每一天的mysql行数

3条答案

相关问题

热门标签

最新问答