力扣数据库部分题解（困难难度部分）

力扣数据库部分题解（困难难度部分）

LC1159

select distinct(u.user_id) seller_id
,if(b.favorite_brand=b.item_brand,'yes','no') 2nd_item_fav_brand
from Users u 
left join(
	select * from (
		select o.order_id,o.order_date,o.item_id,o.seller_id,u.favorite_brand,i.item_brand,
		dense_rank() over(partition by o.seller_id order by o.order_date) rk
		from Orders o,Users u,Items i where 
		o.seller_id=u.user_id and o.item_id=i.item_id)a 
		where a.rk=2
		)b 
on u.user_id=b.seller_id;

LC1194

select group_id,player_id from (
    select p.player_id,p.group_id,b.score,
    dense_rank() over(partition by group_id order by score desc,player_id) rk from(
        select distinct(player) player,sum(score) score from (
        select first_player player,first_score score from Matches
        union all
        select second_player player,second_score score from Matches
        )a group by player
    )b,players p where b.player=p.player_id
)c where c.rk=1;

LC1336

#递归建立一个自增长表
with recursive cte(n) as (
    select 0 
    union all 
    select n+1 from cte where n < (
        select max(num) from (
            select user_id,transaction_date,count(transaction_date) num 
						from Transactions
            group by user_id,transaction_date
        )c
    )
)
#依据Visits表先找到transactions_count为0的数量
select 0 as transactions_count,count(*) as visits_count from (
    select user_id,visit_date from visits 
		where (user_id,visit_date) not in (
        select user_id,transaction_date from Transactions
    )
)a
union all 
#依据Transactions表找到transactions_count不为0的部分
select distinct(num) transactions_count,count(num) as visits_count from (
    select user_id,transaction_date,count(transaction_date) num 
		from Transactions
    group by user_id,transaction_date
)b 
group by transactions_count
union all
#借助自增长表找到transactions_count不为0但visits_count为0的部分
select n as transactions_count,0 as visits_count from cte
where n not in (
    select num from (
        select user_id,transaction_date,count(transaction_date) num 
				from Transactions
        group by user_id,transaction_date
    )d
) and n <> 0
order by transactions_count;

LC1369

select * from UserActivity 
group by username
having count(*)=1
union all 
select username,activity,startDate,endDate from (
    select *,
    dense_rank() over(partition by username order by startDate desc) rk 
    from UserActivity
)a where a.rk = 2;

LC1412

select student_id,student_name from student where student_id not in (
select distinct(student_id) from (
    select *,
    dense_rank() over(partition by exam_id order by score) low_rk,
    dense_rank() over(partition by exam_id order by score desc) high_rk
    from exam
)a where a.low_rk = 1 or a.high_rk = 1
) and student_id in (
    select distinct(student_id) from exam
);

LC1479

select distinct(i.item_category) Category
    ,sum(case when date_format(order_date,'%W')='Monday' then quantity else 0 end) as Monday
    ,sum(case when date_format(order_date,'%W')='Tuesday' then quantity else 0 end) as Tuesday
    ,sum(case when date_format(order_date,'%W')='Wednesday' then quantity else 0 end) as Wednesday
    ,sum(case when date_format(order_date,'%W')='Thursday' then quantity else 0 end) as Thursday
    ,sum(case when date_format(order_date,'%W')='Friday' then quantity else 0 end) as Friday
    ,sum(case when date_format(order_date,'%W')='Saturday' then quantity else 0 end) as Saturday
    ,sum(case when date_format(order_date,'%W')='Sunday' then quantity else 0 end) as Sunday
from Orders o right join Items i 
on o.item_id = i.item_id
group by Category
order by Category;

LC1767

with recursive t(task_id,subtask_id) as (
    select task_id,subtasks_count from Tasks
    union all 
    select task_id,subtask_id - 1 from t where subtask_id >= 2
)
select * from t where (task_id,subtask_id) not in (
    select task_id,subtask_id from Executed
);

LC2004

#创建两个新表
with t1 as (
    select *,row_number() over(order by salary) rk,
    sum(salary) over(order by salary,employee_id) cusum
    from Candidates where experience = 'Senior'
),
t2 as (
    select *,row_number() over(order by salary) rk,
    sum(salary) over(order by salary,employee_id) cusum
    from Candidates where experience = 'Junior'
)

#找到高级员工的数量
select 'Senior' as experience,count(*) as accepted_candidates
from t1 where (
    select max(cusum) 
) < 70000
union
#找到初级员工的数量
select 'Junior' as accepted_candidates,count(*) as accepted_candidates
from t2 where cusum <= (70000 - 
    (select ifnull(max(cusum),0) from t1 
    where cusum < 70000)
);