MySQL常用的几种查询场景

查询出某班级的学生成绩及成绩排名

---

SELECT
  total.*FROM (SELECT
    obj.class,
    obj.total_score,
    CASE WHEN @rowtotal = obj.total_score THEN @rownum WHEN @rowtotal := obj.total_score THEN @rownum := @rownum + 1 WHEN @rowtotal = 0 THEN @rownum := @rownum + 1 END AS rownum
  FROM (SELECT
           class,
           total_score
         FROM oc_exam_students_achievement
         ORDER BY total_score DESC) AS obj,
       (SELECT
           @rownum := 0,
           @rowtotal := NULL) r) AS totalWHERE total.class = 12;

# 结果class total_score rownum
12 4105
12 4006
12 0    7
12 5401
12 5382
12 5043
12 4684

查询出某班的第一名及排名

---

select s1.class,s1.`total_score`,(select count(0) + 1 from oc_exam_students_achievement s2 where s1.class = s2.class and s2.total_score > s1.total_score)rank from oc_exam_students_achievement s1 order by class,rank;

# 结果class total_score rank
501
12 5401
12 5382
12 5043
12 4684
12 4105
12 4006
12 07
14 01

按照班级分组计算出最高分、最低分、平均分、总分

---

select class,max(`total_score`),min(total_score),avg(total_score),sum(total_score) as `total_score` from oc_exam_students_achievement where (`grade` = 3) and (`class` = 12) and (`exam_id` = 18)  group by class;
# 结果claa max min avg sum
125400408.57142860

根据tags分组查询出科目和对应的数量

---

select tags,count(id) as article_count from tb_post group by tags order by article_count desc
# 结果tags article_count,
自动化测试、Python,20,
PHP,7,
社会心理学,5,
nginx,4,
laravel，PHP,4,
MySQL,4,
django,3,
科幻小说,3,
热爱阅读,2,
docker,2,
产品设计,1,
代码整洁,1,
工具,1,
自我修养,1,
二叉树,1,
思考力,1,
Supervisor,1,
vsftpd,1,
其他,1,
vagrant,1,
Linux,1,
程序员,1,
产品思路和设计,1,
rsync,scp,1,
beautifulsoup,1,
redis,1,
ssh,1

按照日期查询文章数

---

select DATE_FORMAT(posttime, '%Y年%m月') as years,DATE_FORMAT(posttime, '%Y-%m') as find_years,count(id) as article_num from tb_post group by years
# 结果year  find_year num
2021年02月2021-0255
2021年03月2021-0316

按时间来查询文章内容

---

select count(id) as count from tb_post where DATE_FORMAT(posttime, '%Y-%m') = '2021-02'orselect count(id) as count from tb_post where posttime like '%2021-02%';
# 结果
count55

mysql实现名次降序排列，其中成绩相同的排名相同。

---

SELECT
total_score AS total_score,
CASEWHEN @pre = total_score THEN
@pic + 0WHEN @pre := total_score THEN
@pic := @pic + 1ELSE
@pic := @pic + 1END AS Rank,exam_id,class,grade,student_id,numberFROM
oc_students_achievement AS s,
(SELECT @pre := NULL ,@pic := 0) AS init where s.exam_id = 19 and s.grade = 13 and s.class = 17 and s.exam_subject_id = 23ORDER BY
total_score DESC