时间戳

首页 > 解决方案 > 组合 2 个 MySQL 选择查询

mysql - 组合 2 个 MySQL 选择查询

问题描述

我有两个查询来获取活跃用户和非活跃用户的数量。

查询 1（统计活跃用户）

select COUNT(id) as qty,
       Date(active_on) as active_on_date 
from `activations` 
where `store_id` = 2475 
  and `current_status` = 'active' 
  and `active_on` is not null 
  and (`active_on` between '2021-01-24 14:15:55' and '2021-02-16 14:15:55') 
group by `active_on_date` 
order by `active_on_date` asc

查询 2（统计非活动用户）

select COUNT(id) as qty 
from `activations` 
where `store_id` = 2475 
  and `current_status` = 'inactive' 
  and `active_on` is null
  and `proceed_on` is not null 
  and (`proceed_on` between '2021-01-24 14:15:55' and '2021-02-16 14:15:55') group by Date(proceed_on) 
order by Date(proceed_on) asc

注意：proceed_on 和 active_on 是不同的日期列。我想得到这个结果：

活跃数量 数量未激活 日期

使用一个查询产生结果是否有任何有效的方法？

这是我的表的例子：http ://sqlfiddle.com/#!9/38458b/1

标签： mysqlselect

解决方案

SELECT SUM(current_status = 'active') AS qty_active,
       SUM(current_status = 'unactive') AS qty_unactive,
       DATE(active_on) AS active_on_date 
FROM activations
WHERE store_id = 2475 
  AND active_on BETWEEN '2021-01-24 14:15:55' AND '2021-02-16 14:15:55'
GROUP BY active_on_date 
ORDER BY active_on_date ASC

PS。在按日期分组但从/到中午的时候计数是很奇怪的。这是什么神奇的时间—— 14:15:55？

推荐阅读