SQL语句以及在Python中的应用
HDUZN

本文记录一些自己使用过的SQL语句,以及在Python中的应用。

SQL语句

一、学生得分数据表

字段数据举例如下:

id name class_no stu_id lesson score
1 张三 501 1 lesson1 10
2 张三 501 1 lesson2 9

表设计如下:

1
2
3
4
5
6
7
8
9
CREATE TABLE "stu_data" (
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
"name" TEXT NOT NULL,
"class_no" INTEGER NOT NULL,
"stu_id" INTEGER NOT NULL,
"lesson" TEXT,
"score" INTEGER,
UNIQUE ("id" ASC)
);

1.查询同一个班级每个学生所有lesson的score总和

  • 按学号 stu_id 排序用 ORDER BY t1.stu_id
  • 按总分排序用 ORDER BY total_score DESCDESC表示降序排序;
  • 统计非0的个数用COUNT(CASE WHEN t1.score > 0 THEN 1 ELSE NULL END) AS non_zero_count
1
2
3
4
5
6
7
8
9
-- 查询同一个班级每个学生所有lesson的score总和
-- ['班级', '学号', '姓名', '总分', '非0分的次数']
SELECT t1.class_no, t1.stu_id, t1.name, SUM(t1.score) AS total_score,
COUNT(CASE WHEN t1.score > 0 THEN 1 ELSE NULL END) AS non_zero_count
FROM stu_data t1
WHERE t1.class_no = 506
GROUP BY t1.name, t1.class_no
-- ORDER BY t1.stu_id
ORDER BY total_score DESC

2.查询同一个lesson每个班级class_no的平均分

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 查询同一个项目lesson每个班级class_no的平均分
-- ['项目', '班级', '平均分', '去除0分后的平均分', '非0分的次数']
SELECT
t1.lesson,
t1.class_no,
ROUND(AVG(t1.score), 2) AS avg_score,
ROUND(SUM(CASE WHEN t1.score > 0 THEN t1.score ELSE NULL END) /
COUNT(CASE WHEN t1.score > 0 THEN t1.score ELSE NULL END), 2) AS avg_score_excluding0,
COUNT(CASE WHEN t1.score > 0 THEN t1.score ELSE NULL END) AS non_zero_count
FROM stu_data t1
WHERE t1.lesson = 'lesson3'
GROUP BY t1.lesson, t1.class_no
ORDER BY avg_score DESC

3.查询所有项目lesson每个班级class_no的平均分

1
2
3
4
5
6
-- 查询所有项目lesson每个班级class_no的平均分
SELECT
t1.lesson, t1.class_no,
ROUND(AVG(t1.score), 2) AS avg_score
FROM stu_data t1
GROUP BY t1.lesson, t1.class_no

在Python中的应用

1.查询同一个班级每个学生所有lesson的score总和

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
import sqlite3
db_file = 'test.db'
conn = sqlite3.connect(db_file)
cursor = conn.cursor()

class_no = 501
query_sql = f"""
SELECT t1.name, t1.class_no, t1.stu_id, SUM(t1.score) AS total_score,
COUNT(CASE WHEN t1.score > 0 THEN 1 ELSE NULL END) AS non_zero_count
FROM stu_data t1
WHERE t1.class_no = {class_no}
GROUP BY t1.name, t1.class_no
ORDER BY total_score DESC
"""
cursor.execute(query_sql)
query_results = cursor.fetchall()
print(query_results)
cursor.close()
conn.close()
  • 本文标题:SQL语句以及在Python中的应用
  • 本文作者:HDUZN
  • 创建时间:2023-12-01 13:47:59
  • 本文链接:http://hduzn.cn/2023/12/01/SQL语句以及在Python中的应用/
  • 版权声明:本博客所有文章除特别声明外,均采用 BY-NC-SA 许可协议。转载请注明出处!
 评论