mysql查询计数量提交到jsp显示_我如何编写一个查询来获取mysql中的计数(how can i write as one query to get count in mysql)...

我如何编写一个查询来获取mysql中的计数(how can i write as one query to get count in mysql)

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

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

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

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

todayCount yesterdayCount

2 4

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

todayCount yesterdayCount

2 4

原文：https://stackoverflow.com/questions/44947662

更新时间：2019-11-10 00:44

最满意答案

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

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;

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

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

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

2017-07-06

相关问答

继承人我的解决方案： $total_days = '14';

// get leads count array

$sql = mysql_query("select count(*) as `count`, `date_updated`

from `leads`

where date_format(from_unixtime(`date_updated`), '%Y-%m

...

如果您知道当前和之前的日期，那么您可以： 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;

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

...

更改： SELECT count(*) as total 至 SELECT count(*) as total, username

然后你可以这样做： $row = mysql_fetch_array($query);

$username = $row['username'];

希望有所帮助。 另请查看此问题以获取更多详细信息： 如何在MySQL表的列中找到最常见的结果 Change: SELECT count(*) as total to SELECT count(*) as total

...

就像Duniyadnd和三氯生指出的那样，这是由IsDeleted的列类型引起的。 在右侧面板中更改查询，以便您可以看到使用int和varchar列类型之间的区别。 sqlfiddle.com/#!2/7bf0a/5 Like Duniyadnd and triclosan point out this is caused by the column type for IsDeleted. Change the query in the right panel so you can see the

...

SELECT asset.id

,asset.company

,asset.location

-- ,... Any more columns from asset

,software.software

,software.license

,laptop.model AS laptop_model

,desktop.model AS desktop_model

-- etc.

,COALESCE(laptop.model, s

...

select a.*

from

(select * from user_balance_table

where report_date < 20130901

) a

left outer join

(select user_id

from user_balance_table

where report_date <20130801

group by user_id

) b

on a.user_id=b.user_id and b.us

...

试试这个查询： SELECT yt1.*, COALESCE(yt2.referral_count, 0)

FROM yourtable yt1 LEFT JOIN

(

SELECT t1.userid, COUNT(*) AS referral_count

FROM yourtable t1 INNER JOIN yourtable t2

ON t1.userid = t2.referralcode

GROUP BY t1.userid

) yt2

ON yt1.

...

尝试这个 CREATE TABLE login_log (

id INT NOT NULL AUTO_INCREMENT,

datetime DATETIME NULL,

iduser INT NULL,

PRIMARY KEY (id));

CREATE TABLE contacts (

id INT NOT NULL AUTO_INCREMENT,

name VARCHAR(45) NULL,

created DATETIME NULL,

iduser INT

...

你可以使用SUM()使用sum和条件将得到一个布尔值1或0，这样它就会给你条件的计数 SELECT locus.ID,COUNT(*) `all_alleles_per_locus`,

SUM(Status = 'Tentative') `tentative_alleles_762`

FROM allele

INNER JOIN locus ON allele.LocusID = locus.PrimKey

GROUP BY locus.ID

You can use SUM

...