首页 >

最受欢迎的top3课程

接受所有小饼干头像 接受所有小饼干 
select
cid,
CAST(7d_vv AS SIGNED) AS pv,
7d_t AS time_len
-- total_t
from (
    select
    cid,
    sum(if(date(end_time) <= release_date_7d,1,0)) as 7d_vv,
    sum(if(date(end_time) <= release_date_7d,t,0)) as 7d_t,
    -- sum(t) as total_t,
    avg(score) as score
    from (
        select
        t1.cid,
        t1.release_date,
        date_add(release_date,interval 6 day) as release_date_7d,
        uid,
        end_time,
        timestampdiff(minute,start_time,end_time) as t,
        score
        from course_info_tb t1
        left join play_record_tb t2
        on t1.cid = t2.cid
    ) t1
    group by cid
) t2
where score >= 3
order by pv desc
limit 3

查看原题
添加回答
麻薯仙人头像 麻薯仙人
一步到位
select a.cid,
            count(1) as pv,round(sum(timestampdiff(minute,start_time,end_time)),3) as time_len
from play_record_tb a
left join course_info_tb b using(cid)
where datediff(start_time,release_date)<7
group by a.cid
having avg(score)>=3
order by pv desc, time_len desc
limit 3

发表于 2025-03-20 12:32:09 回复(2)
发粪涂墙的小陈同学头像 发粪涂墙的小陈同学
有一个问题欸。
题目里面说的受欢迎定义为：必须是平均评分不低于3，发布后一周内被观看次数越大越受欢迎。
这个平均分是总的平均分，还是一周内的平均分？？？
我看大家基本都是where一周内，然后group by，having平均分大于等于3，这样就是发布一周内的平均分。
但是好像这个题目没有说清楚平均分的口径。
发表于 2025-05-16 16:40:45 回复(1)
讲道理的熊熊整顿职场头像 讲道理的熊熊整顿职场 
select a.cid,count(a.cid) pv,
    sum(timestampdiff(minute,start_time,end_time)) time_len
from course_info_tb a
join play_record_tb b using(cid)
where timestampdiff(day,release_date,start_time)<=7
group by a.cid
having avg(score)>=3
order by pv desc,time_len desc
limit 3

发表于 2025-03-27 16:54:58 回复(0)
小朱在学习ing头像 小朱在学习ing
注意这里是平均评分不低于3
with t1 as(
select uid,a2.cid,release_date,start_time,
timestampdiff(minute,start_time,end_time) as time_len,
score
from course_info_tb a1
right join
play_record_tb a2
on a1.cid=a2.cid
# where score>=3
)

select cid,count(*) as pv,sum(time_len) as time_len
from t1
where date(start_time) between release_date and release_date+7
group by cid
having avg(score)>=3
order by pv desc,time_len desc
limit 3
发表于 2025-04-10 16:00:31 回复(0)
4fbd81e头像 4fbd81e
豆包图片答题直出

WITH RatedCourses AS (
    -- 筛选出平均评分不低于3的课程
    SELECT cid, AVG(score) AS avg_score
    FROM play_record_tb
    GROUP BY cid
    HAVING AVG(score) >= 3
),
WeeklyViews AS (
    -- 统计发布后一周内的观看次数和总时长
    SELECT 
        pi.cid,
        COUNT(pr.uid) AS pv,
        SUM(TIMESTAMPDIFF(MINUTE, pr.start_time, pr.end_time)) AS time_len
    FROM course_info_tb pi
    JOIN play_record_tb pr ON pi.cid = pr.cid
    WHERE pr.start_time >= pi.release_date AND pr.start_time < DATE_ADD(pi.release_date, INTERVAL 1 WEEK)
    GROUP BY pi.cid
)
SELECT 
    wv.cid,
    wv.pv,
    wv.time_len
FROM WeeklyViews wv
JOIN RatedCourses rc ON wv.cid = rc.cid
ORDER BY wv.pv DESC, wv.time_len DESC
LIMIT 3;


发表于 2025-04-18 16:06:39 回复(0)
_Kelion_头像 _Kelion_ 
select inner_result3.cid,count(inner_result3.id) as pv,sum(inner_result3.total_time) as time_len
from (
    select inner_result2.id,cit.cid,timestampdiff(minute,inner_result2.start_time,inner_result2.end_time) as total_time
from course_info_tb cit
join (
    select inner_result1.id,inner_result1.uid,inner_result1.inner_result1.cid,inner_result1.start_time,inner_result1.end_time
    from (select id,uid,cid,start_time,end_time,avg(score) over (partition by cid) as avg_score from play_record_tb) as inner_result1
    where inner_result1.avg_score >= 3
) as inner_result2
on cit.cid = inner_result2.cid
where (inner_result2.start_time between cit.release_date and date_add(cit.release_date,interval 7 day)) and (inner_result2.end_time between cit.release_date and date_add(cit.release_date,interval 7 day))
) as inner_result3
group by inner_result3.cid
order by pv desc,time_len desc
limit 3;

发表于 2025-08-09 14:59:37 回复(0)
牛客961340438号头像 牛客961340438号 
select p.cid,count(*) as pv,
sum(timestampdiff(minute,start_time,end_time)) as time_len
from play_record_tb p
join course_info_tb c 
on c.cid=p.cid
where datediff(start_time, release_date) < 7
group by p.cid
having avg(score)>=3
order by pv desc,time_len desc
limit 3

发表于 2025-08-04 00:27:39 回复(0)
破烂儿_头像 破烂儿_
WITH A AS (
    SELECT
        cid,
        COUNT(*) AS cnt,
        SUM(TIMESTAMPDIFF(MINUTE,start_time,end_time)) AS sum_count,
        AVG(score) AS avg_score
    FROM course_info_tb c
    JOIN play_record_tb p USING (cid)
    WHERE DATEDIFF(end_time,release_date) <= 7
    GROUP BY c.cid
    HAVING avg_score >= 3
)
SELECT
    cid,
    cnt AS pv,
    sum_count AS time_len
FROM A
ORDER BY cnt desc,sum_count desc
LIMIT 3
发表于 2025-07-21 17:26:35 回复(0)
接受所有小饼干头像 接受所有小饼干 
select
cid,
CAST(7d_vv AS SIGNED) AS pv,
7d_t AS time_len
-- total_t
from (
    select
    cid,
    sum(if(date(end_time) <= release_date_7d,1,0)) as 7d_vv,
    sum(if(date(end_time) <= release_date_7d,t,0)) as 7d_t,
    -- sum(t) as total_t,
    avg(score) as score
    from (
        select
        t1.cid,
        t1.release_date,
        date_add(release_date,interval 6 day) as release_date_7d,
        uid,
        end_time,
        timestampdiff(minute,start_time,end_time) as t,
        score
        from course_info_tb t1
        left join play_record_tb t2
        on t1.cid = t2.cid
    ) t1
    group by cid
) t2
where score >= 3
order by pv desc
limit 3

发表于 2025-07-17 15:27:02 回复(0)
EmiyaChen头像 EmiyaChen 
select
    cid
    , pv
    , time_len
from
    (select
        cid
        , count(cid) as pv
        , sum(time_diff) as time_len
    from
        (select
            prtb.cid
            , score
            , floor(timestampdiff(second, start_time, end_time)/60) as time_diff
        from
            play_record_tb as prtb
            left join course_info_tb as citb on prtb.cid = citb.cid
        where
            release_date + 7 > date(start_time)
        ) as t1
    group by
        cid
    having
        avg(score) >= 3
    ) as tt1
order by
    cid
limit
    3

发表于 2025-06-30 15:32:38 回复(0)
迟缓的小饼干1头像 迟缓的小饼干1 
select p.cid,
      count(p.cid) as pv,
      sum(unix_timestamp(p.end_time)-unix_timestamp(p.start_time))/60 as time_len

from play_record_tb as p
join course_info_tb as c on p.cid=c.cid
where timestampdiff(day,c.release_date,p.start_time)<=7

group by p.cid
having avg(p.score) >'3'
order by pv desc,sum(unix_timestamp(p.end_time)-unix_timestamp(p.start_time))/60 desc
limit 3
考虑秒数，最后结果会大一点
发表于 2025-06-23 16:20:18 回复(0)
想去东北泡澡的林北准备进厂头像 想去东北泡澡的林北准备进厂 
SELECT
    t.cid,
    COUNT(*) pv,
    ROUND(SUM(TIMESTAMPDIFF(MINUTE, start_time, end_time)), 2) time_len
FROM
    course_info_tb c
    JOIN
    (
    SELECT
        uid,
        cid,
        start_time,
        end_time
    FROM    
        play_record_tb
    WHERE
        cid IN (
        SELECT
            cid
        FROM
            play_record_tb
        GROUP BY
            cid
        HAVING
            AVG(score) >= 3) -- 平均评分不低于3
    ) t
    USING(cid)
WHERE
    DATEDIFF(start_time, release_date) <= 6 -- 发布后一周内被观看次数越大越受欢迎
GROUP BY
    t.cid
ORDER BY
    pv DESC,
    time_len DESC
LIMIT
    3

发表于 2025-05-20 13:24:27 回复(0)
曲径海盗头像 曲径海盗 
select pb.cid,count(*) pv,sum(timestampdiff(minute,pb.start_time,pb.end_time)) time_len
from play_record_tb pb
left join course_info_tb cb on pb.cid = cb.cid
where datediff(pb.end_time,cb.release_date)<8
group by pb.cid
having avg(pb.score) >= 3
order by pv desc,time_len desc
limit 3

发表于 2025-05-19 22:48:30 回复(0)
饿不死就行了头像 饿不死就行了
仔细看题目要求，7天内 
select
    pt.cid,
    count(*) as pv,
    SUM(TIMESTAMPDIFF(minute,start_time,end_time)) as tilem_len
from 
    course_info_tb as ct join play_record_tb as pt on ct.cid = pt.cid
where TIMESTAMPDIFF(day,release_date,start_time) <= 7
group by pt.cid
having avg(score) >=3
order by pv desc
limit 3


发表于 2025-05-19 16:45:27 回复(0)
牛客702254410号头像 牛客702254410号 
select
  p.cid,count(*) as pv,sum(timestampdiff(minute,p.start_time,p.end_time)) as time_len
from play_record_tb p inner join course_info_tb c
on p.cid=c.cid
where timestampdiff(day,c.release_date,date_format(p.start_time,'%Y-%m-%d'))<=7
group by p.cid
having avg(p.score)>=3
order by pv desc,time_len desc
limit 3
其实重要的还是理解mysql各个关键字的执行流程，关键字作用

发表于 2025-05-17 10:40:06 回复(0)
牛客225506839号头像 牛客225506839号
select
    distinct a.cid,
    count(*) pv,
    round(sum(timestampdiff(minute,start_time,end_time)),3) as time_len
from
    course_info_tb a
join play_record_tb b on a.cid=b.cid
where
    timestampdiff(day,release_date,date(end_time))<7
group by
    1
having
    avg(score)>=3
order by
    3 desc
limit
    3
发表于 2025-05-15 21:21:08 回复(0)
威猛的肱二头肌说这不是bug头像 威猛的肱二头肌说这不是bug
   select cit.cid, count(*) as pv,
    sum(timestampdiff(minute,prt.start_time, prt.end_time)) as time_len
    from course_info_tb as cit
        join play_record_tb as prt
        on(cit.cid = prt.cid)
    where date(prt.end_time) between cit.release_date
                    and date_add(cit.release_date, interval 6 day)
    or date(prt.start_time) between cit.release_date
                    and date_add(cit.release_date, interval 6 day)
    or (date(prt.start_time) < cit.release_date
            and  date(prt.end_time) > date_add(cit.release_date, interval 6 day))  
    group by cid
    order by pv desc, time_len desc  
    limit 3  
发表于 2025-05-05 20:20:38 回复(0)
牛客587638495号头像 牛客587638495号 
select cid, count( 1) as pv, sum(dif_min) as time_len from (select t1.id, uid,
             t1.cid, start_time, end_time, score, tag, release_date, duration, timestampdiff(minute ,start_time,end_time) as dif_min from play_record_tb t1 join course_info_tb t2 on t1.cid=t2.cid where timestampdiff(day ,release_date,date_format(start_time,'%Y-%m-%d'))<=7 and t1.cid in (select cid  from play_record_tb group by cid  having avg(score) >= 3)) a1 group by cid order by pv desc ,time_len desc limit 3;
发表于 2025-05-05 12:06:44 回复(0)
牛客923449060号头像 牛客923449060号 
到底哪里错了啊，跪求大佬解答！
select
    cit.cid,
    count(*) pv,
    round(
        sum(timestampdiff (minute, start_time, end_time)),
        3
    ) time_len
from
    course_info_tb cit
    inner join play_record_tb prt on cit.cid = prt.cid
where
    timestampdiff (minute, start_time, release_date) / 1440 < 7
group by
    cit.cid
having
    avg(score) >= 3
order by
    pv desc,
    time_len desc

发表于 2025-04-30 19:30:11 回复(1)
Jimhacker头像 Jimhacker
select b.cid,count(b.cid) as pv,sum(timestampdiff(minute,b.start_time,b.end_time)) as time_len from course_info_tb a join play_record_tb b on a.cid=b.cid where timestampdiff(day,a.release_date,date(b.start_time))<=7 group by b.cid having avg(score)>=3 order by pv desc,time_len desc limit 3
发表于 2025-04-24 14:08:03 回复(0)
提交观点

问题信息

SQL
难度：
37条回答 322浏览

热门推荐

相关试题

最受欢迎的top3课程

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号