LeetCode（数据库）- 可以放心投资的国家

题目链接：点击打开链接

题目大意：略。

解题思路：略。

AC 代码

-- 解决方案(1)
WITH t AS((SELECt caller_id call_id, duration FROM Calls) UNIOn ALL (SELECt callee_id, duration FROM Calls)),
t1 AS(SELECt id, c.name FROM Person JOIN Country c ON country_code = SUBSTRINg(phone_number, 1, 3))

SELECt name country
FROM t JOIN t1 ON call_id = id
GROUP BY name
HAVINg AVG(duration) > (SELECt AVG(duration) all_country_avg FROM t)

-- 解决方案(2)
SELECt c.name AS country
FROM Calls, Person, Country c
WHERe (caller_id = id OR callee_id = id) AND country_code = LEFt(phone_number, 3)
GROUP BY country_code
HAVINg AVG(duration) > (SELECt AVG(duration) FROM Calls);