Combine 2 MySQL queries

问题:

I have this query

$mysqli->query("SELECT COUNT(*) from `login_log` where from_unixtime(`date`) >= DATE_SUB(NOW(), INTERVAL 1 WEEK);") or die($mysqli->error.__LINE__);

and the same one with 1 diff. it's not 1 WEEK , but 1 MONTH how can I combine those two and assign them to aliases ?

回答1:

I would do this with conditional aggregation:

SELECT SUM(from_unixtime(`date`) >= DATE_SUB(NOW(), INTERVAL 1 WEEK)),
       SUM(from_unixtime(`date`) >= DATE_SUB(NOW(), INTERVAL 1 MONTH))
FROM `login_log`;

MySQL treats boolean values as integers, with 1 being "true" and 0 being "false". So, using sum() you can count the number of matching values. (In other databases, you would do something similar using case.)

回答2:

Even though it's pretty tough to understand what you ask:

If you want them in same column use OR

$mysqli->query("SELECT COUNT(*) from 'login_log' where from_unixtime('date') >= DATE_SUB(NOW(), INTERVAL 1 WEEK) OR from_unixtime('date') >= DATE_SUB(NOW(), INTERVAL 1 MONTH) ;") or die($mysqli->error.__LINE__);

If you dont want duplicate answers: use GROUP BY

回答3:

Use the where condition with one month internal and add the same where condition with one week internal as a Boolean column return.

I mean

Select count (*) all_in_month, (from_unixtime(`date`) >= DATE_SUB(NOW(), INTERVAL 1 WEEK)) as in_week from `login_log` where from_unixtime(`date`) >= DATE_SUB(NOW(), INTERVAL 1 a MONTH) GROUP BY in_week;

P.s. haven't tested but afaik it should work