首页 > 试题广场 >

统计复旦用户8月练题情况

[编程题]统计复旦用户8月练题情况
  • 热度指数:346019 时间限制:C/C++ 1秒,其他语言2秒 空间限制:C/C++ 256M,其他语言512M
  • 算法知识视频讲解
题目: 现在运营想要了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况,请取出相应明细数据,对于在8月份没有练习过的用户,答题数结果返回0.

示例:用户信息表user_profile
id device_id gender age university gpa active_days_within_30
1 2138 male 21 北京大学 3.4 7
2 3214 male 复旦大学 4.0 15
3 6543 female 20 北京大学 3.2 12
4 2315 female 23 浙江大学 3.6 5
5 5432 male 25 山东大学 3.8 20
6 2131 male 28 山东大学 3.3 15
7 4321 female 28 复旦大学 3.6 9
示例:question_practice_detail
id device_id question_id result date
1 2138 111 wrong 2021-05-03
2 3214 112 wrong
2021-05-09
3 3214 113 wrong
2021-06-15
4 6543 111 right 2021-08-13
5 2315 115 right
2021-08-13
6 2315 116 right
2021-08-14
7 2315 117 wrong
2021-08-15
……




根据示例,你的查询应返回以下结果:
device_id
university question_cnt right_question_cnt
3214 复旦大学 3 0
4321 复旦大学 0 0

示例1

输入

drop table if exists `user_profile`;
drop table if  exists `question_practice_detail`;
drop table if  exists `question_detail`;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);
CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL,
`date` date NOT NULL
);
CREATE TABLE `question_detail` (
`id` int NOT NULL,
`question_id`int NOT NULL,
`difficult_level` varchar(32) NOT NULL
);

INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong','2021-05-03');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong','2021-06-15');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(8,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(9,3214,113,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(10,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(11,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(12,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(13,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(14,3214,112,'wrong','2021-08-16');
INSERT INTO question_practice_detail VALUES(15,3214,113,'wrong','2021-08-18');
INSERT INTO question_practice_detail VALUES(16,6543,111,'right','2021-08-13');
INSERT INTO question_detail VALUES(1,111,'hard');
INSERT INTO question_detail VALUES(2,112,'medium');
INSERT INTO question_detail VALUES(3,113,'easy');
INSERT INTO question_detail VALUES(4,115,'easy');
INSERT INTO question_detail VALUES(5,116,'medium');
INSERT INTO question_detail VALUES(6,117,'easy');

输出

device_id|university|question_cnt|right_question_cnt
3214|复旦大学|3|0
4321|复旦大学|0|0
Select a1.device_id, a1.university, sum(Case When a2.date Between '2021-08-01' And '2021-08-31' Then 1 Else 0 End) As question_cnt, sum(Case When (a2.date Between '2021-08-01' And '2021-08-31') And (a2.result = 'right') Then 1 Else 0 End) As right_question_cnt
From
  (
    Select device_id, university
    From user_profile
    Where university = '复旦大学'
  ) As a1
  Inner Join
  (
    Select device_id, result, date
    From question_practice_detail As qpd
  ) As a2
  On a2.device_id =a1.device_id
Group By device_id;

发表于 2025-07-05 21:15:19 回复(0)
select
    t.*,
    (select count(1) from question_practice_detail q2 where q2.device_id = t.device_id and q2.result = 'right' and month(q2.date) = 8) right_question_cnt
from (
    select
           u.device_id,
           u.university,
           count(q.question_id) as question_cnt
    from
        user_profile u
        left join question_practice_detail q on u.device_id = q.device_id and month(q.date) = 8
    where
        u.university = '复旦大学'
    group by u.device_id, q.device_id
) t

发表于 2025-06-28 14:35:13 回复(0)
注意:8月没有答题的人要返回0,可以在连接表的时候把月份带上。
SELECT
    u.device_id,
    u.university,
    COUNT(q.question_id) AS question_cnt,
    SUM(CASE WHEN result = 'right' THEN 1
        ELSE 0 END) AS right_question_cnt
FROM user_profile u
LEFT JOIN question_practice_detail q
ON u.device_id = q.device_id AND month(q.date) = 8
WHERE university = '复旦大学'
GROUP BY device_id

发表于 2025-06-27 19:22:21 回复(0)
WHERE university = '复旦大学' AND ((YEAR(date) = 2021 AND MONTH(date) = 8) OR date IS NULL)
用来筛选有答题记录(2021-8月)和没有答题记录的人员可以吗?
发表于 2025-06-26 10:00:18 回复(0)
select u.device_id,u.university,count(q.question_id) question_cnt,
sum(case when result="right" then 1 else 0 end) right_question_cnt
from user_profile u left join question_practice_detail q
on u.device_id=q.device_id and month(date)=08
where university='复旦大学'
group by device_id
发表于 2025-06-16 15:48:33 回复(0)
这样为什么会报错
select
    a.device_id,
    a.university,
    b.question_cnt,
    b.right_question_cnt
from
    user_profile a
     join (
        select
            device_id,
            count(question_id) question_cnt 
            SUM(CASE WHEN result = 'right' THEN 1 ELSE 0 END) right_question_cnt
        from
            question_practice_detail
        where
            year(date) = 2021
            and month(date) = 8
        group by
            device_id
     )b on a.device_id = b.device_id
    and a.university = '复旦大学'

发表于 2025-06-14 14:57:02 回复(0)
with fd as(
    select device_id,university
    from user_profile
    where university='复旦大学'),
   
    ques as(
        select fd.device_id,university,question_id,result
        from fd
        left join question_practice_detail as q
        on fd.device_id=q.device_id
        and q.date like '2021-08-%')

select
    device_id,university,
    count(question_id)as question_cnt,
    sum(case when result='right' then 1 else 0 end)as right_question_cnt
from ques
group by device_id,university
发表于 2025-06-11 16:57:15 回复(0)
select
    u.device_id,
    u.unviersity,
    count(q.question_id) as question_cnt,
    sum(if(q.result = right, 1, 0)) as right_question_cnt
from
    user_profile as u
    left join question_practice_detail as q on u.device_id = q.device_id and month (q.date) = 8
where
    unviersity = '复旦大学'
group by
    u.device_id;

发表于 2025-06-11 11:05:06 回复(1)
为什么这个报错呢
select up.device_id,university,count(question_id) question_cnt,count(result='right') right_question_cnt
from user_profile up
left join question_practice_detail qpd on
up.device_id=qpd.device_id
group by university
having  (month(date)=8&nbs***bsp;date is null) and university='复旦大学' 

发表于 2025-05-22 15:46:57 回复(0)
SELECT
  t1.device_id,
  t1.university,
  COUNT(t2.question_id) AS question_cnt,  -- 统计有多少题目
  SUM(IF(t2.result = 'right', 1, 0)) AS right_question_cnt
FROM user_profile t1
LEFT JOIN question_practice_detail t2 ON t1.device_id = t2.device_id AND MONTH(t2.date) = 8   -- 条件写在 JOIN 里
WHERE
  t1.university = '复旦大学'
GROUP BY t1.device_id, t1.university;

发表于 2025-05-19 13:42:06 回复(0)
有无大佬帮我看看,我这个自测可以过,提交不行
SELECT
  a.device_id,
  a.university,
  COUNT(b.question_id) AS question_cnt,
  COUNT(DISTINCT CASE WHEN b.result = 'right' THEN b.question_id END) AS right_question_cnt
FROM
  user_profile a
LEFT JOIN
  question_practice_detail b
  ON a.device_id = b.device_id AND b.date LIKE '2021-08%'
WHERE
  a.university = '复旦大学'
GROUP BY
  a.device_id, a.university;
发表于 2025-05-13 17:53:07 回复(0)
SELECT u.device_id,university, 
sum(IF(`date` LIKe '%-08-%',1, 0)) question_cnt, 
sum(IF(result = 'right' AND `date` LIKe '%-08-%',1,0)) right_question_cnt
FROM user_profile u LEFT JOIN question_practice_detail q
ON u.device_id = q.device_id
WHERE university = '复旦大学'
GROUP BY u.device_id;

发表于 2025-05-02 03:22:34 回复(0)
使用左连接将 user_profile 和question_practice_detail 连接起来
然后运用count以及case统计8月份回答题目数量为question_cnt
同理可以求出回答正确的数量
然后使用where函数筛选出“复旦大学”
最后GROUP BY device_id 以及 university
select u1.device_id, u1.university, 
count(
    case 
        when month(u2.date) = 8 then u2.question_id
        else null
    end
    ) as question_cnt,
count(
    case
        when u2.result = 'right' then u2.question_id
        else null
    end
    ) as right_question_cnt
from user_profile as u1
left join question_practice_detail as u2
on u1.device_id = u2.device_id 
where u1.university = '复旦大学' 
group by u1.device_id, u1.university


发表于 2025-04-30 16:18:00 回复(0)
为什么这样不行啊
SELECT
    device_id,
    university,
    COUNT(question_id) AS question_cnt,
    COUNT(
        result = 'right)'
       &nbs***bsp;NULL
    ) AS right_question_cnt
FROM
    user_profile
    LEFT JOIN (
        SELECT 
            *
        FROM
            question_practice_detail
        WHERE 
            MONTH (date) = 8
    ) a USING (device_id)
WHERE
    university = '复旦大学'
GROUP BY
    device_id




发表于 2025-04-28 12:02:20 回复(0)