1127. 用户购买平台：union做维表

 题目来源：力扣

---

正确答案如下：

-- # Write your MySQL query statement below
select t2.spend_date, t2.platform, ifnull(sum(amount), 0) as total_amount,
ifnull(count(distinct user_id), 0) as total_users
from
(   #1.构造所需的表
    select distinct spend_date, "desktop" as platform
    from Spending
    union
    select distinct spend_date, "mobile" as platform
    from Spending
    union
    select distinct spend_date, "both" as platform
    from Spending    
) as t2
left join 
(   #2.查询每个用户，每个日期，每个平台类型，总金额
    select spend_date, user_id, sum(amount) as amount,
    if(count(*)=1,platform,'both') as platform
    from Spending 
    group by spend_date, user_id
) as t1
#3.左连接，并按日期和平台分组
on t2.spend_date = t1.spend_date and t2.platform = t1.platform
group by t2.spend_date, t2.platform

---

自己做的答案如下：

有一定问题，主要是没有将platform字段做成维表，比如在如下这段sql中，2019-07-02的那条记录结果就不会出现； 

select t1.spend_date,t1.platform,sum(amount) as total_amount,
count(distinct user_id) as total_users from 

(select t1.user_id,t1.spend_date,t1.amount,if(t2.platform_cnt=2,'both',t1.platform) as platform from 

(
    select * from Spending 
)t1

left join

(select 
spend_date,
user_id,
count(distinct platform) as platform_cnt
from Spending 
group by spend_date,user_id)t2   

on t1.user_id = t2.user_id and 
t1.spend_date = t2.spend_date)t1 
group by t1.spend_date,t1.platform 

order by t1.spend_date,t1.platform