结合这些mySQL查询

Question

我需要结合这些查询，所以我回来了一个月，一个新的总数，并返回总数（追踪新的和返回的用户）的列表。我目前有以下两个查询。第一个是新设备（检测频率），第二个设备返回。我想输出结果，所以它是一个包含每个月的行的表格，然后是包含New和Returning data的两列。

SELECT COUNT(DISTINCT (mac)) AS new, 
EXTRACT(MONTH FROM date_time) AS month 
FROM detected_devices 
WHERE client_id = 11 
AND venue_id = 1 
AND detection_frequency = 1 
GROUP BY month 

UNION ALL 

SELECT COUNT(DISTINCT (mac)) AS returning, 
EXTRACT(MONTH FROM date_time) AS month 
FROM detected_devices 
WHERE client_id = 11 
AND venue_id = 1 
AND detection_frequency > 1 
GROUP BY month 

我看了一下，但没有找到任何关于如何用别名完成的信息。

Answer 1

这是怎么回事？

SELECT t1.month, t1.new, t2.returning FROM (query 1) as t1, (query 2) as t2 where t1.month = t2.month; 

（或加入，取决于你喜欢什么）。

Answer 2

这只是给你一个行有两列，但应该给你的，你如何使用别名

SELECT 
A.new, 
B.returning 
FROM 
(SELECT COUNT(DISTINCT (mac)) AS new, 
EXTRACT(MONTH FROM date_time) AS month 
FROM detected_devices 
WHERE client_id = 11 
AND venue_id = 1 
AND detection_frequency = 1 
GROUP BY month) A, 
(SELECT COUNT(DISTINCT (mac)) AS returning, 
EXTRACT(MONTH FROM date_time) AS month 
FROM detected_devices 
WHERE client_id = 11 
AND venue_id = 1 
AND detection_frequency > 1 
GROUP BY month) B