在MySQL中减去两个Select查询的结果 [英] Subtract results of two Select queries in Mysql
问题描述
我编写了两个mysql查询,一个查询给我获取一年中特定月份的总用户(注册用户),另一个查询获取该年特定月份中的活动用户.我需要查找该年度非活动用户的数量.为此,我正在考虑减去通过两个单独的查询获得的totalUsers和activeUsers列. 以下是查询
I have written two mysql queries, one fetches me the total users(registered) present in a particular month of the year and the other fetches the active users in that particular month of the year. I need to find the number of inactive users for that year. For this, I was thinking of subtracting the totalUsers and the activeUsers columns obtained through two separate queries. Below are the queries
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.我该如何实现?
I need to subtract activeUsers from totalUsers. How do i achieve this?
推荐答案
您可以简单地减去两个查询或合并
you can simply subtract two query or merge
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 ;
这篇关于在MySQL中减去两个Select查询的结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!