题39:
根据下表编写 SQL 语句,查询每一对用户 (person1, person2) 之间的通话次数和通话总时长,其中 person1 < person2 。
解题思路:
方法1、用UNION ALL重复也不合并
具体实现代码如下:
select person1,
person2,
COUNT(duration) as call_count,
SUM(duration) as total_duration
from (--构建新表
select from_id as person1,
to_id as person2,
duration
from Calls where from_id < to_id--找出from_id < to_id所以信息
union all
select to_id as person1,
from_id as person2,
duration
from Calls where to_id < from_id--找出from_id > to_id所以信息
) t--用union all连接两个表即可
group by person1, person2;
方法2、利用least和greatest的函数
- 用least函数找出两个中最小的一个;
- 用greatest函数找出两个中最大的一个;
这样可以将交叉相同的两个字段进行同一分组
具体实现代码如下:
select
from_id as person1,
to_id as person2,
COUNT(*) as call_count,
SUM(duration) as total_duration
from calls
group by least(from_id, to_id), greatest(from_id, to_id);