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

Question

我正在使用一个 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 天内每天有多少条目。

Answer 1

如果您只需要每天计数，请使用以下内容:

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