力扣数据库进阶50--No.1699题解

官方难度中等，体感比较复杂，有多种解法。

解法一：union all

select person1,person2,
    sum(cnt) as call_count,sum(sum) as total_duration
from
    ((select from_id as person1,to_id as person2,
            concat('(',from_id,',',to_id,')') as couple,
            count(*) as cnt,
            sum(duration) as sum
    from Calls
    where from_id < to_id
    group by couple)
    union all
    (select to_id as person1,from_id as person2,
        concat('(',to_id,',',from_id,')') as couple,
        count(*) as cnt,
        sum(duration) as sum
    from Calls
    where to_id < from_id
    group by couple)) as tmp
group by couple

其他更优解法：

解法二：least/greatest行级比较

select
    least(from_id, to_id) person1,
    greatest(from_id, to_id) person2,
    count(1) call_count,
    sum(duration) total_duration
from
    Calls
group by
    least(from_id, to_id), greatest(from_id, to_id)

解法三：强指定小的为person1，大的为person2

select person1,person2, count(1) as call_count, sum(duration) as total_duration from (
select if(from_id>to_id,to_id,from_id) as person1,if(from_id>to_id,from_id,to_id) as person2 , duration  from Calls ) c 
group by person1,person2