union 专题

union：对合并结果去重
union all：保留所有合并结果

力扣602：

with tmp1 as 
((select requester_id as id 
from RequestAccepted)
union all
(select accepter_id id 
from RequestAccepted))

select id,count(id) as num
from tmp1
group by id
order by num desc
limit 1

 力扣1759：

(select product_id,'store1' as store,store1 as price
from Products
where store1 is not null)
union
(select product_id,'store2' as store,store2 as price
from Products
where store2 is not null)
union
(select product_id,'store3' as store,store3 as price
from Products
where store3 is not null)

力扣578：

关键点1：合并两个新建表

with tmp as
((select question_id,count(*) as show_cnt,'0' as answer_cnt
from SurveyLog 
where action='show'
group by question_id)
union all
(select question_id,'0' as show_cnt,count(*) as answer_cnt
from SurveyLog 
where action='answer'
group by question_id))

关键点2：求和计算指标

select question_id,
    sum(answer_cnt)/sum(show_cnt) as answer_rate
from tmp

题解：

with tmp as
((select question_id,count(*) as show_cnt,'0' as answer_cnt
from SurveyLog 
where action='show'
group by question_id)
union all
(select question_id,'0' as show_cnt,count(*) as answer_cnt
from SurveyLog 
where action='answer'
group by question_id))

select question_id as survey_log
from
(select question_id,
    sum(answer_cnt)/sum(show_cnt) as answer_rate
from tmp
group by question_id
order by answer_rate desc,question_id
limit 1) as tmp3

力扣1212：

关键点1：新建列，每一轮应该有一列主队得分、和宾队得分

with tmp as
(select *,
    case when host_goals>guest_goals then '3'
    when host_goals=guest_goals then '1'
    else '0' end as host_score,
    case when guest_goals>host_goals then '3'
    when guest_goals=host_goals then '1'
    else '0' end as guest_score
from Matches)

关键点2：union all 合并

(select host_team as team_id,host_score as score
from tmp
union all
select guest_team as team_id,guest_score as score
from tmp)

关键点3：联表后处理 null

ifnull(num_points,0)

题解：

# 新建列：每一轮应该有一列主队得分、和宾队得分
with tmp as
(select *,
    case when host_goals>guest_goals then '3'
    when host_goals=guest_goals then '1'
    else '0' end as host_score,
    case when guest_goals>host_goals then '3'
    when guest_goals=host_goals then '1'
    else '0' end as guest_score
from Matches)

# union合并
,tmp1 as
(select host_team as team_id,host_score as score
from tmp
union all
select guest_team as team_id,guest_score as score
from tmp)

# 聚合求和
,tmp2 as
(select team_id,sum(score) as num_points
from tmp1
group by team_id)

# 联表
select Teams.team_id,team_name,ifnull(num_points,0) as num_points
from Teams left join tmp2
on tmp2.team_id=Teams.team_id
order by num_points desc,team_id 

力扣1264：

关键点1：union 找到 ta 的所有朋友 id

关键点2：找到 ta 喜欢的页面

关键点3：推荐页面在是 ta 朋友喜欢的页面，但不包含他 ta 喜欢的页面

题解：

# ta的朋友id
with tmp1 as
((select user2_id as user_id
from Friendship 
where user1_id=1)
union
(select user1_id as user_id
from Friendship 
where user2_id=1))

# ta自己喜欢的页面
,tmp2 as
(select page_id 
from Likes 
where user_id=1)

# ta朋友喜欢的页面
select distinct page_id as recommended_page  
from Likes 
where user_id in 
    (select distinct user_id
    from tmp1)
and page_id not in
    (select distinct page_id
    from tmp2)

力扣1270：

关键点1：找到直接汇报给 CEO 的员工 id，其中不包含 CEO

关键点2：找到间接汇报给 CEO 的员工 id

关键点3：找到再间接汇报给 CEO 的员工 id

关键点4：将以上查找结果合并，利用 union 不保留重复项

题解：

# 直接汇报给CEO：
with tmp1 as
(select distinct employee_id
from Employees
where manager_id=1
and employee_name<>'Boss'
and employee_id<>1)

# 间接汇报给CEO：
,tmp2 as
(select distinct employee_id
from Employees
where manager_id in
    (select distinct employee_id
    from tmp1))

# 再间接汇报给CEO：
,tmp3 as
(select distinct employee_id
from Employees
where manager_id in
    (select distinct employee_id
    from tmp2))

# 合并：
(select *
from tmp1)
union
(select *
from tmp2)
union
(select *
from tmp3)

--这里类似 self join 专题中的上下属关系，但这里使用 where in 来解题。

力扣1205 v.s. 力扣1132：

这两题为中等题，但思路上存在一定难度。

注意，前者的日期聚合分别按照各表日期；而后者的日期聚合都按照行动表里的日期，不分别按照各表日期。

力扣1205：

关键点1：左连接，获得退款金额，同时也处理退款表的月份

关键点2：处理交易表的月份

关键点3：union all 合并

关键点4：聚合计数、求和

关键点5：过滤金额为 0 的记录

题解：

# 左连接，获得退款金额，同时也处理退款表的月份
with tmp1 as
(select trans_id as id,country,
    'chargeback' as state,
    amount,left(Chargebacks.trans_date,7) as month
from Chargebacks left join Transactions 
on Chargebacks.trans_id=Transactions.id)

# 处理交易表的月份
,tmp2 as
(select id,country,state,amount,
    left(trans_date,7) as month
from Transactions)

# 合并
,tmp3 as
((select *
from tmp1)
union all
(select *
from tmp2))

# 聚合计数、求和
select month,country,
    count(if(state='approved',1,null)) as approved_count,
    sum(if(state='approved',amount,0)) as approved_amount,
    count(if(state='chargeback',1,null)) as chargeback_count,
    sum(if(state='chargeback',amount,0)) as chargeback_amount 
from tmp3
group by month,country
having approved_amount<>0 or chargeback_amount<>0

力扣1132：

关键点1：预处理，过滤行动表；注意去重

关键点2：左连接，获得日期，同时新建移除列；这里以移除表为左

关键点3：union all 合并

关键点4：对合并后的大表，进行聚合计数

关键点5：对所有取平均值；这里不会含有 null，因为前面以移除表（较小者）为左连接

题解：

# 过滤 Actions 表；注意题干有重复项
with tmp0 as
(select distinct post_id,action_date,extra
from Actions 
where extra='spam')

# 左连接，获得日期，同时新建移除列
,tmp1 as
(select Removals.post_id as post_id,
    action_date,'remove' as extra
from Removals left join tmp0
on tmp0.post_id=Removals.post_id)

# 合并
,tmp2 as
((select *
from tmp0)
union all
(select *
from tmp1))

# 聚合计数
,tmp3 as
(select action_date,
    count(if(extra='remove',1,null)) as remove_num,
    count(if(extra='spam',1,null)) as spam_num,
    count(if(extra='remove',1,null))/
    count(if(extra='spam',1,null))*100 as daily_percent
from tmp2
group by action_date)

# 取平均值
select round(avg(daily_percent),2) 
    as average_daily_percent 
from tmp3

力扣1127：

该题为 hard 题，有点越做越上头了（啊不是）。

关键点1：union all 合并

关键点2：构建全种类表，以左连接

关键点3：处理 null 值； count (column_name) 不计 null 值， sum (column_name) 需处理 null 值

题解：

# 创建 both 表
with tmp1 as
(select user_id,spend_date,
    'both' as platform,
    sum(amount) as amount
from Spending 
group by user_id,spend_date 
having count(*)=2)

# 创建仅 mobile 表
,tmp2 as
(select user_id,spend_date,
    platform,amount
from Spending
group by user_id,spend_date
having count(*)=1
and platform='mobile')

# 创建仅 desktop 表
,tmp3 as
(select user_id,spend_date,
    platform,amount
from Spending
group by user_id,spend_date
having count(*)=1
and platform='desktop')

# 合并
,tmp4 as
((select *
from tmp1)
union all
(select *
from tmp2)
union all
(select *
from tmp3))

# 创建全日期表
,all_date as
(select distinct spend_date
from tmp4)

# 创建全种类表（这里种类可能会有缺失！！！）
,all_platform as
((select 'mobile' as platform)
union all
(select 'desktop' as platform)
union all
(select 'both' as platform))

# 创建全日期表、全种类表
,all_date_platform as
(select spend_date,platform
from all_date join all_platform)

# 左连接，保留全日期、全种类；聚合计数、求和
select all_date_platform.spend_date,
    all_date_platform.platform,
   sum(ifnull(amount,0)) as total_amount,
    count(user_id) as total_users
from all_date_platform left join tmp4
on all_date_platform.spend_date=tmp4.spend_date
and all_date_platform.platform=tmp4.platform
group by all_date_platform.spend_date,all_date_platform.platform

-END