我如何编写一个查询来获取mysql中的计数

I want to get the today count of users and yesterday's users count for that i want to write only one query how can i do that..?

我想得到今天的用户数和昨天的用户数量，我想只写一个查询我该怎么做...？

these are my queries I want only one query:

这些是我的查询我只想要一个查询：

SELECT COUNT(*) FROM visitors group by visited_date ORDER by visited_date DESC limit 1,1 as todayCount

SELECT COUNT(*) FROM visitors group by visited_date ORDER by visited_date DESC limit 1,0 as yesterdayCount

My expected results or only 2 columns

我的预期结果还是只有2列

todayCount yesterdayCount
     2          4

4 个解决方案

#1

If you know the current and previous date, then you can do:

如果您知道当前和之前的日期，那么您可以：

SELECT SUM(visited_date = CURDATE()) as today,
       SUM(visited_date = CURDATE() - interval 1 day) as yesterday
FROM visitors
WHERE visited_date >= CURDATE() - interval 1 day;

If you don't know the two days, then you can do something similar, getting the latest date in the data:

如果您不知道这两天，那么您可以做类似的事情，获取数据中的最新日期：

SELECT SUM(v.visited_date = m.max_vd) as today,
       SUM(v.visited_date < m.max_vd) as yesterday
FROM visitors v CROSS JOIN
     (SELECT MAX(v2.visited_date) as max_vd FROM visitors v2) as m
WHERE v.visited_date >= m.max_vd - interval 1 day

#2

This should do the trick:

这应该是诀窍：

SELECT COUNT(CASE 
                WHEN visited_date = CURDATE() THEN 1 
             END) AS todayCount ,
       COUNT(CASE 
                WHEN visited_date = CURDATE() - INTERVAL 1 DAY THEN 1 
             END) AS yesterdayCount 
FROM visitors  
WHERE visited_date IN (CURDATE(), CURDATE() - INTERVAL 1 DAY)
GROUP BY visited_date
ORDER by visited_date

#3

Just try this simple query

试试这个简单的查询

select visited_date as date, COUNT(*) as count from `visitors` 
group by `visited_date` order by `visited_date` asc

It will produce output as

它会产生输出

It will work for you.

它会对你有用。

#4

-1

Try this: $sqlToday = "Select COUNT(*) FROM menjava WHERE DATE(date_submitted)=CURRENT_DATE()";

试试这个：$ sqlToday =“选择COUNT（*）FROM menjava WHERE DATE（date_submitted）= CURRENT_DATE（）”;

$sqlYesterday = "Select COUNT(*) FROM menjava WHERE DATE(dc_created) = CURDATE() - INTERVAL 1 DAY";

$ sqlY yesterday =“选择COUNT（*）FROM menjava WHERE DATE（dc_created）= CURDATE（） - INTERVAL 1 DAY”;

#1