php - 对如何合并两个查询的结果感到困惑

Question

查询 1:

SELECT count(id) as conversions, SUM(user_payout) as amount, geoip.country_name, geoip.country_code
FROM conversions
LEFT JOIN geoip ON conversions.end_ip BETWEEN geoip.start_long AND geoip.end_long
WHERE user_id = 1
AND type != ''
AND status = 1
AND month(created_at) = month(now())
GROUP BY country_name
ORDER BY country_name ASC

结果:

查询 2:

SELECT count(id) as clicks, geoip.country_name, geoip.country_code
FROM clicks
LEFT JOIN geoip on clicks.ip BETWEEN geoip.start_long AND geoip.end_long
WHERE user_id = 1
AND month(created_at) = month(now())
GROUP BY country_name
ORDER BY country_name ASC

结果:

我想将每个国家/地区的两者结合起来，它会显示点击次数、转化次数、国家/地区名称和国家/地区代码。

不知道该怎么做。

谢谢!

Answer 1

您可以使用子查询和左外连接将它们结合起来:

select clicks.country_code, clicks.country_name,
       coalesce(clicks.clicks, 0) as clicks,
       coalesce(conversions.conversions, 0) as conversions
from (<subquery 2>) clicks left outer join
     (<subquery 1>) conversions
     on clicks.country_code = conversions.country_code;

这假设所有国家/地区至少有一次点击。

编辑:

如果国家列表在 geo_ip 中，您可以:

select gi.country_code, gi.country_name, clicks.clicks, conversions.conversions
from (select distinct country_code, country_name
      from geo_ip
     ) gi left outer join
     (<subquery 2>) clicks
     on clicks.country_code = gi.country_code left outer join
     (<subquery 1>) conversions
     on conversions.country_code = gi.country_code;