mysql 两个select结果_在MySQL中减去两个Select查询的结果

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));

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 ;