mysql - 有没有办法把以下两句SQL合并成一句

Question

想要完成一个统计的页面，具体用求是统计注册用户的数量，
今天注册了几个用户
昨天注册了几个用户
总过注册了几个用户

现在的做发的是分三次查询，

SELECT COUNT(*) AS today FROM `wx2tb_user` WHERE ( to_days(date_format(from_UNIXTIME(reg_time),"%Y-%m-%d")) = to_days(now()) )

SELECT COUNT(*) AS yesterday FROM `wx2tb_user` WHERE ( to_days(date_format(from_UNIXTIME(reg_time),"%Y-%m-%d")) = to_days( DATE_SUB(now(),INTERVAL 1 DAY) ) )

SELECT COUNT(*) AS total FROM `wx2tb_user`

接下来还要返回列表，这样一个页面要有4次数据库查询，有没有什么好的办法减少数据库查询的

Answer 1

so easy.

SELECT * from 
(SELECT COUNT(*) AS today FROM `wx2tb_user` WHERE ( to_days(date_format(from_UNIXTIME(reg_time),"%Y-%m-%d")) = to_days(now()) ) ) a,
(SELECT COUNT(*) AS yesterday FROM `wx2tb_user` WHERE ( to_days(date_format(from_UNIXTIME(reg_time),"%Y-%m-%d")) = to_days( DATE_SUB(now(),INTERVAL 1 DAY) ) )) b,
(SELECT COUNT(*) AS total FROM `wx2tb_user`) c

Answer 2

可以定义一个函数public function user($search){}
你只要定义一样这个$search 就好了

Answer 3

想快点，可以使用视图，可以封装个存储过程来调用喽

Answer 4

合在一起的查询性能未必比你分开查询要快。

Answer 5

select * from t1

id    name    create_time
1    a    2017-01-17 14:49:37
2    b    2017-01-17 14:49:44
3    c    2017-01-16 14:50:01
4    f    2017-01-13 14:50:09




SELECT
    sum(
        CASE
        WHEN date_format(create_time, "%Y-%m-%d") = date_format(NOW(), "%Y-%m-%d") THEN
            1
        ELSE
            0
        END
    ) AS today_registers,
    sum(
        CASE
        WHEN date_format(create_time, "%Y-%m-%d") = date_format(
            DATE_SUB(now(), INTERVAL 1 DAY),
            "%Y-%m-%d"
        ) THEN
            1
        ELSE
            0
        END
    ) AS yesterday_registers,
    count(*) AS total
FROM
    t1