mysql - MySQL中两次Select查询的结果相减

Question

我写了两个 mysql 查询，一个获取一年中特定月份的总用户数(注册)，另一个获取一年中那个特定月份的活跃用户。我需要找到那一年的不活跃用户数。为此，我正在考虑减去通过两个单独的查询获得的 totalUsers 和 activeUsers 列。以下是查询

1. Fetch Total Registered users

set @numberOfUsers := 0; 
SELECT T.createdMonth, T.monthlyusers, (@numberOfUsers := @numberOfUsers + T.monthlyusers) as totalUsers 
FROM 
(
SELECT month(from_unixtime(u.createdDate)) as createdMonth, count(u.id) as monthlyusers
FROM user u 
where year(from_unixtime(u.createdDate)) = '2016'  
group by month(from_unixtime(u.createdDate))    
) T;

2. Fetch Active Users

select month(from_unixtime(lastActive)), (count(u.id))  as activeUsers from user u 
where year(from_unixtime(lastActive)) = '2016' 
group by month(from_unixtime(lastActive));

我需要从 totalUsers 中减去 activeUsers。我如何实现这一目标？

Answer 1

你可以简单地减去两个查询或合并

select (select query) - (select query);

SET @numberOfUsers := 0; 

SELECT l.totalUsers-a.monthlyusers FROM (SELECT T.createdMonth, 
T.monthlyusers, (@numberOfUsers := @numberOfUsers + T.monthlyusers) AS totalUsers 
FROM 
(
SELECT MONTH(FROM_UNIXTIME(u.createdDate)) AS createdMonth, COUNT(u.id) AS monthlyusers
FROM USER u 
WHERE YEAR(FROM_UNIXTIME(u.createdDate)) = '2016'  
GROUP BY MONTH(FROM_UNIXTIME(u.createdDate))    
) T ) l,(SELECT MONTH(FROM_UNIXTIME(lastActive)), (COUNT(u.id))  AS  activeUsers FROM USER u 
WHERE YEAR(FROM_UNIXTIME(lastActive)) = '2016' 
GROUP BY MONTH(FROM_UNIXTIME(lastActive))) a ;