mysql - 合并查询结果

Question

我正在尝试将 4 个计算结果返回到一个表中

这是目前的查询:

/* new account */

SELECT count(*) as "new account"
FROM teams_trial teams
WHERE teams.user_id not in (select user_id from user_space_snapshot) and DATEADD(DAY, -30, GETDATE()) < teams.created_day

/* under limit */

SELECT count(*) as "under limit"
FROM user_space_snapshot u INNER JOIN trial_teams t ON (t.user_id = u.user_id)
WHERE DATEADD(DAY, -30, GETDATE()) < t.created_day AND u.space_used/u.space_limit < 0.9 

/* Near Limit */

SELECT COUNT(*) AS "near limit"
FROM user_space_snapshot u INNER JOIN trial_teams t ON (t.user_id = u.user_id)
WHERE DATEADD(DAY, -30, GETDATE()) < t.created_day AND u.space_used/u.space_limit between 0.9 and 1

/* Over Limit */

SELECT count(*) AS "over limit"
FROM user_space_snapshot u INNER JOIN trial_teams t ON (t.user_id = u.user_id)
WHERE DATEADD(DAY, -30, GETDATE()) < t.created_day AND u.space_used/u.space_limit > 1

理想情况下，结果应该是这样的:

usage_bucket | num_active_trials
---------------------------------
new account  | 5043
under limit  | 4560
near limit   | 1200
over limit   | 6452

Answer 1

最简单的方法是使用union all:

SELECT "new account" as usage_bucket, count(*) as num_active_trials
FROM teams_trial teams
WHERE teams.user_id not in (select user_id from user_space_snapshot) and DATEADD(DAY, -30, GETDATE()) < teams.created_day

union all

SELECT "under limit" as usage_bucket, count(*) as num_active_trials
FROM user_space_snapshot u INNER JOIN trial_teams t ON (t.user_id = u.user_id)
WHERE DATEADD(DAY, -30, GETDATE()) < t.created_day AND u.space_used/u.space_limit < 0.9 

union all

SELECT "near limit" as usage_bucket, count(*) as num_active_trials
FROM user_space_snapshot u INNER JOIN trial_teams t ON (t.user_id = u.user_id)
WHERE DATEADD(DAY, -30, GETDATE()) < t.created_day AND u.space_used/u.space_limit between 0.9 and 1

union all

SELECT  "over limit" as usage_bucket, count(*) as num_active_trials
FROM user_space_snapshot u INNER JOIN trial_teams t ON (t.user_id = u.user_id)
WHERE DATEADD(DAY, -30, GETDATE()) < t.created_day AND u.space_used/u.space_limit > 1