细雨噜噜噜

分类

题解(99)

/ 注册

全部文章（共60篇）

题解 | #每个6/7级用户活跃情况#

SELECT user_info.uid, COUNT(DISTINCT DATE_FORMAT(act_time,'%Y-%m')) act_month_total, COUNT(DISTINCT IF(YEAR(act_time)=2021, DATE_FOR...

Mysql

2021-11-22

0 408

题解 | #满足条件的用户的试卷完成数和题目练习数#

SELECT exam_record.uid, COUNT(DISTINCT IF(YEAR(exam_record.submit_time)=2021,exam_record.id,NULL)) exam_cnt, COUNT(DISTINCT IF(YEAR(practice_record.su...

Mysql

2021-11-22

0 376

题解 | #每个题目和每份试卷被作答的人数和次数#

法一： SELECT * FROM (SELECT exam_id AS tid, COUNT(DISTINCT exam_record.uid) uv, COUNT(*) pv FROM exam_record GROUP BY exam_id ORDER BY uv DESC, pv DESC...

Mysql

2021-11-21

98 3856

题解 | #试卷发布当天作答人数和平均分#

法一：直观地把三个表连起来，把条件写上去。 SELECT examination_info.exam_id, COUNT(DISTINCT exam_record.uid) uv, ROUND(AVG(score),1) avg_score FROM user_info, examination_...

Mysql

2021-11-19

0 443

题解 | #月均完成试卷数不小于3的用户爱作答的类别#

SELECT tag, COUNT(tag) tag_cnt FROM exam_record,examination_info WHERE exam_record.exam_id=examination_info.exam_id AND uid IN (SELECT uid FROM exam_r...

Mysql

2021-11-19

0 364

题解 | #月总刷题数和日均刷题数#

(SELECT DATE_FORMAT(submit_time,'%Y%m') AS submit_month, COUNT(*) AS month_q_cnt, ROUND(COUNT(*)/DAY(LAST_DAY(submit_time)),3) AS avg_day_q_cnt FROM ...

Mysql

2021-11-19

0 386

题解 | #每类试卷得分前3名#

SELECT tag, uid, ranking FROM( SELECT tag, uid, row_number() over(partition by tag order by MAX(score) DESC, MIN(score) DESC, uid DESC) AS ran...

Mysql

2021-11-19

1 684

题解 | #插入记录（三）#

REPLACE INTO examination_info VALUES(null, 9003, 'SQL', 'hard', 90, '2021-01-01 00:00:00'); mark的是REPLACE INTO的用法。另外还有一种INSERT IGNORE INTO INSERT I...

Mysql

2021-11-18

0 322

题解 | #插入记录（二）#

法一： INSERT INTO exam_record_before_2021(uid,exam_id,start_time,submit_time,score) SELECT uid,exam_id,start_time,submit_time,score FROM exam_record WHE...

Mysql

2021-11-18

0 354

题解 | #牛客的课程订单分析(二)#

法一：先统计指定日期后所有的订单数，然后再选其中订单数大于等于2的客户。 SELECT user_id FROM (SELECT user_id,COUNT(IF(status='completed',1,NULL)) num FROM order_info WHERE product_name...

Mysql

2021-09-30

0 469