数据库实验5答案（手撕）

数据库实验5答案（手撕）

前言

写个简单参考版，都是暴力拆分撕的。

1

select id, sum(count) as total from trans natural join goods
where weight < 50 group by id having total > 20 order by id asc;

2

select distinct music_name from music mu
    join music_likes ml on mu.id = ml.music_id
    join follow f on ml.user_id = f.follower_id
where f.user_id = 1 and ml.music_id not in
                        (select music_id from music_likes ml2
                        where ml2.user_id = 1)
order by music_name;

3

select f.user_id, m.music_name from follow f join music_likes ml on f.follower_id = ml.user_id
join music m on ml.music_id = m.id
where ml.music_id not in
      (select music_id from music_likes ml2
          where ml2.user_id = f.user_id)
group by f.user_id, m.music_name
order by f.user_id asc;

4

create view v1
as select dt, count(user_id) as dau from (
    select dt, user_id from login_record lr group by dt, user_id order by dt) lr1
   group by dt;

create view v2 as
select dt, count(lr2.user_id) as dau_new
from (select lr.dt, lr.user_id
from login_record lr group by dt, user_id order by dt) lr2
join new_user nu on lr2.user_id = nu.user_id
where nu.is_new = 1 group by lr2.dt;


create view v3(dt, total_pay) as
select dt, sum(up.pay_money)  from user_pay up group by dt;

create view v4 as
select dt, sum(pay_money) as total_pay_new from user_pay natural join new_user
where is_new = 1
group by dt
order by dt;

select  dt, v1.dau, v2.dau_new, v3.total_pay, v4.total_pay_new
from v1 natural join v2 natural join v3 natural join v4;

5

create view v1 as
select user_id, min(date) as first_day from login group by user_id;

create view v2 as
select v1.user_id, l.date from v1 join login l on v1.user_id = l.user_id
where datediff(l.date, v1.first_day) = 1;

create view v3 as
select l.date, ifnull(count(first_day), 0) as new_count
from login l left join mydata.v1 v on l.user_id = v.user_id
and l.date = v.first_day
group by l.date;

create view v4 as
select  l.date, ifnull(count(v.date), 0) as next_day_usr
from login l left join mydata.v2 v on l.user_id = v.user_id
and datediff(v.date, l.date) = 1
group by l.date;

select date, round(ifnull(v4.next_day_usr / v3.new_count, 0), 3) as p
from v3 natural join v4;

6

create view v1 as
select user_id, product_name, date
from order_info
where product_name in ('C++', 'Python', 'Java')
  and status = 'completed'
  and datediff(date, '2021-10-15') > 1
order by user_id, date;


create view v2(user_id, cnt) as
select user_id, count(user_id) as cnt from v1
group by user_id
having cnt >= 2;

create view v3 as
select user_id, date as first_buy_date from (select *, row_number() over (partition by user_id) as rn
               from v1 natural join v2) v
where rn = 1;

create view v4 as
select user_id, date as second_buy_date, cnt
from (select *, row_number() over (partition by user_id) as rn
from v1 natural join v2) v
where rn = 2;

select v3.user_id, v3.first_buy_date, v4.second_buy_date,
       v4.cnt
from v3 natural join v4;

7

create view vs1 as
select JNO, PNO, sum(qty) as sq
from SPJ group by JNO, PNO;

create view vs2 as
select jno, max(sq) as msq from vs1
group by JNO;

select vs1.JNO, vs1.PNO, sq as maxsum from vs1 join vs2
    on vs1.JNO = vs2.JNO and vs1.sq = vs2.msq
order by JNO, PNO;

后话

如果有时间，再写优化版，还是太菜了，手撕了这么久。