在一个查询中获取所有用户的计数，而不是在mysql中获取多个查询

kt06eoxx 于 2021-06-21 发布在 Mysql

关注(0)|答案(3)|浏览(339)

我正在尝试编写一个mysql查询，其中我只想从一个表中获取数据。结构如下所示：

+--------------------+------------------+------+-----+----------+----------------+
| Field              | Type             | Null | Key | Default  | Extra          |
+--------------------+------------------+------+-----+----------+----------------+
| id                 | int(10) unsigned | NO   | PRI | NULL     | auto_increment |
| name               | varchar(255)     | NO   |     | NULL     |                |
| lastname           | varchar(255)     | NO   |     | NULL     |                |
| slug               | text             | NO   |     | NULL     |                |
| gender             | varchar(225)     | YES  |     | NULL     |                |
| balance            | double(50,6)     | YES  |     | NULL     |                |
| email              | varchar(255)     | NO   |     | NULL     |                |
| password           | varchar(255)     | NO   |     | NULL     |                | 
| city               | varchar(255)     | NO   |     | NULL     |                |
| country            | varchar(255)     | NO   |     | NULL     |                |
| statename          | varchar(255)     | NO   |     | NULL     |                |
| countryname        | varchar(255)     | NO   |     | NULL     |                |
| dob                | date             | YES  |     | NULL     |                |
| membership         | varchar(255)     | YES  |     | standard |                |
| status             | varchar(255)     | NO   |     | NULL     |                |
| marital_status     | varchar(255)     | NO   |     | NULL     |                |
| description        | text             | NO   |     | NULL     |                |
| user_id            | int(10) unsigned | YES  | MUL | NULL     |                |
| active             | smallint(6)      | NO   |     | 1        |                |
| registertype       | varchar(255)     | NO   |     | unpaid   |                |
| created_at         | timestamp        | YES  |     | NULL     |                |
| updated_at         | timestamp        | YES  |     | NULL     |                              
+--------------------+------------------+------+-----+----------+----------------+

现在我想用下面的列来选择这样的数据：

1) created_at (will be a date)
2) total_registrations (will be total count)
3) paid_member (count of total member where registertype=paid on a specific date)
4) unpaid_member (count of total member where registertype=unpaid on a specific date wise)

到目前为止，我通过以下查询成功地得到了如下结果：

select DATE(`created_at`) as created_at, count(*) as total_registrations 
from en_customers 
where DATE(`created_at`)  between date_sub(CURDATE(), interval 7 day) AND CURDATE() 
    AND registertype="paid"  
group by DATE(`created_at`);

通过这个查询，我得到以下结果：

+------------+-----------+
| created_at | total_reg |
+------------+-----------+
| 2018-07-04 |         7 |
| 2018-07-05 |         7 |
| 2018-07-06 |         5 |
| 2018-07-07 |         4 |
| 2018-07-08 |         8 |
| 2018-07-09 |        13 |
| 2018-07-10 |        15 |
| 2018-07-11 |        14 |
+------------+-----------+

但是我不能用我刚刚展示的列来记录。
（提前）谢谢你！

sql mysql

来源：https://stackoverflow.com/questions/51293097/get-all-the-users-count-according-to-type-in-one-query-instead-of-multiple-query

3条答案

按热度按时间

bkhjykvo1#

可以对筛选值使用sum

select DATE(`created_at`) as created_at
      , count(*) as total_registrations 
      , sum(case when registertype='paid' then 1 else 0 end) paid_member
      , sum(case when registertype='unpaid' then 1 else 0 end) unpaid_member
from en_customers 
where DATE(`created_at`)  between date_sub(CURDATE(), interval 7 day) AND CURDATE() 

group by DATE(`created_at`);

赞(0）回复(0）举报 2021-06-21

wgx48brx2#

这样做：

select DATE(`created_at`) as created_at, count(*) as total_registrations 
    ,sum(case when registertype='paid' then 1 else 0 end) as paid_member
    ,sum(case when registertype='unpaid' then 1 else 0 end) as unpaid_member
from en_customers 
where DATE(`created_at`)  between date_sub(CURDATE(), interval 7 day) AND CURDATE()  
group by DATE(`created_at`);

这种技术被称为“条件聚合”。
注意，我删除了 registertype="paid" 来自 WHERE 条款。

赞(0）回复(0）举报 2021-06-21

iszxjhcz3#

使用 CASE WHEN 在 COUNT 功能。

select DATE(`created_at`) as created_at,
       count(*) as total_registrations,
       COUNT(CASE WHEN registertype='paid' THEN 1 END) as paid_member,
       COUNT(CASE WHEN registertype='unpaid' THEN 1 END) as unpaid_member 
from en_customers 
where DATE(`created_at`)  between date_sub(CURDATE(), interval 7 day) AND CURDATE() 
group by DATE(`created_at`);

赞(0）回复(0）举报 2021-06-21

我来回答

在一个查询中获取所有用户的计数，而不是在mysql中获取多个查询

3条答案

相关问题

热门标签

最新问答