题目： 现在运营想要了解复旦大学的每个用户在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 26 复旦大学 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
区块链毕设网qklbishe.com为您提供问题的解答
| 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 | 26 | 复旦大学 | 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 |
FROM user_profile up
LEFT JOIN question_practice_detail qpd
ON up.device_id = qpd.device_id
WHERE up.university = ‘复旦大学’
AND qpd.date LIKE ‘%-08-%’
GROUP BY device_id
UNION
SELECT device_id, university, 0 AS question_cnt, 0 AS right_question_cnt
FROM user_profile up
WHERE up.device_id NOT IN (SELECT DISTINCT device_id FROM question_practice_detail)
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 USING(device_id) WHERE university = '复旦大学' AND (MONTH(date) = 8&nbs***bsp;MONTH(date) IS NULL) GROUP BY device_id;
SELECT u.device_id, university, sum(if(result is not null,1,0)) as question_cnt, sum(if(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 WHERE university='复旦大学' and (month(date)=8 or date is null) GROUP BY u.device_id;
u.device_id
,u.university
,sum(case when q.result is null then 0 else 1 end) as question_cnt
,sum(case when q.result=’right’ then 1 else 0 end) as right_question_cnt
from user_profile as u
left join question_practice_detail as q
on u.device_id =q.device_id
where u.university = ‘复旦大学’
and (month(q.date) =8 or month(q.date) is null)
group by u.device_id
select up.device_id,university, count(upd.device_id) question_cnt, count(case when result='right' then 1 else null end) right_question_cnt from user_profile as up left join question_practice_detail as upd on upd.device_id=up.device_id where university='复旦大学' and (month(date)='08'&nbs***bsp;month(date) is null) group by up.device_id
SELECT u.device_id, university, SUM(IF(result IS NOT NULL, 1, 0)) AS questino_cnt, SUM(IF(result = "right", 1, 0)) 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`) = "08" WHERE university = "复旦大学" GROUP BY u.device_id;
select u.device_id,university,count(result),count(nullif(result,'wrong')) from (select * from question_practice_detail where month(date)=08) q right join (select device_id,university from user_profile where university="复旦大学") u on u.device_id=q.device_id group by device_id;
# 解题思路是题目表中筛选出8月份的答题记录，用户表 中筛选出复旦大学的记录， # 然后用右连接筛选出既是复旦又是八月份的记录(原谅我先写了题目表的子查询，不然就左连接了咳咳) # 利用count(列名)不统计null的数据的特性，以及nullif(expr1,expr2)两参数相等时返回null的特性 # 即可分别计算出 result的条数 以及 非wrong的条数
select u.device_id, u.university, count(q.question_id) as question_cnt, sum(if(q.result = 'wrong'&nbs***bsp;q.result is null, 0, 1)) as right_question_cnt from user_profile u left join question_practice_detail q on u.device_id = q.device_id and q.date like '%-08%' where u.university = '复旦大学' group by u.device_id;
SELECT u.device_id,u.university,COUNT(question_id) question_cnt, SUM(case when p.result = 'right' then 1 else 0 end) right_question_cnt from user_profile u LEFT JOIN question_practice_detail p ON u.device_id = p.device_id where university = '复旦大学' and month(date) = '08' GROUP BY u.device_id,u.university UNION SELECT u.device_id,u.university,0 question_cnt,0 right_question_cnt FROM user_profile u LEFT JOIN question_practice_detail p ON u.device_id = p.device_id WHERE university = '复旦大学' and month(date) != '08'
为了避免某复旦同学在8月外月份答题而导致结果出错的情况，用复旦大学，答题不在8月两个条件补充第一步漏掉的人
select u.device_id,u.university, count(q.question_id)as question_cnt, sum(case when q.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 where ( month(q.date)=08&nbs***bsp;month(q.date) is null) and u.university='复旦大学' group by u.device_id
select t1.device_id ,t1.university ,count(t2.device_id) , sum(if(t2.result='right',1,0)) from user_profile as t1 left OUTER join (SELECT device_id,result, SUBSTRING_INDEX(SUBSTRING_INDEX(date,'-',-2),'-',1) as m FROM question_practice_detail WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(date,'-',-2),'-',1)='08') as t2 on t1.device_id = t2.device_id where t1.university='复旦大学' group by t1.device_id
select t1.device_id,t1.university, count(t2.question_id) as question_cnt, sum(case when result='right' then 1 else 0 end) as right_question_cnt from user_profile t1 left join question_practice_detail t2 using (device_id) where university= '复旦大学' and (MONTH(date)=8 or date is null) group by device_id
重点不要漏掉没有答题的用户
select a.device_id,
a.university,
sum(case when b.question_id is not null then 1 else 0 end) question_cnt,
sum(case when b.result = ‘right’ then 1 else 0 end) right_question_cnt
from user_profile a
LEFT JOIN question_practice_detail b
on a.device_id = b.device_id
where a.university = ‘复旦大学’
and ((MONTH(b.date) = 8) or b.date is null)
GROUP BY a.device_id,a.university;
FROM user_profile as a
LEFT JOIN question_practice_detail as b
ON a.device_id=b.device_id
WHERE university=’复旦大学’
GROUP BY a.device_id;
select q.device_id, u.university, count(q.device_id) question_cnt, count(if(q.result='right',1,null)) right_question_cnt from user_profile u left join question_practice_detail q on u.device_id = q.device_id where q.date regexp '2021-08' and u.university = '复旦大学' group by q.device_id union select u.device_id, u.university, count(q.device_id) question_cnt, count(if(q.result='right',1,null)) right_question_cnt from user_profile u left join question_practice_detail q on u.device_id = q.device_id where q.device_id is null and u.university = '复旦大学' group by u.device_id
select u.device_id, university, count(q.question_id) question_cnt, sum(if(q.result='right',1,0)) right_question_cnt from user_profile u left join question_practice_detail q on u.device_id = q.device_id and month(`date`) = 8 where university = '复旦大学' group by u.device_id;
,sum(case when year(date) = 2021 and month(date) = 08 then 1
else 0 end) `question_cnt`,
sum(case when year(date) = 2021 and month(date) = 08 and q.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
where university = ‘复旦大学’
group by u.device_id
count(t2.question_id),
count(case when result=’right’ then 1 end)
from
user_profile t1 left join question_practice_detail t2
on t1.device_id = t2.device_id
where (month(date) = 8 or date is null) and university = ‘复旦大学’
group by t1.device_id
以上就是关于问题题目： 现在运营想要了解复旦大学的每个用户在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 26 复旦大学 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的答案
欢迎关注区块链毕设网-
专业区块链毕业设计成品源码，定制。
区块链NFT链游项目方科学家脚本开发培训
