巧用#{}和${},实现多模块共用的分数分段算法

Posted on Edited on In Views:

在开发自己负责的模块的过程中，发现其他模块也有与自己相似的需求，都需要进行分数分段。比如竞赛，培训，测试，考试。

于是就在想能不能实现一个分数分段的工具类，使所有模块都能共用，从而减少重复代码呢？

测试数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test`  (
  `id` int(11) NOT NULL,
  `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `score` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES (1, 'qwe', '11');
INSERT INTO `test` VALUES (2, 'qwe', '22');
INSERT INTO `test` VALUES (3, 'qwe', '22');
INSERT INTO `test` VALUES (4, 'qwe', '77');
INSERT INTO `test` VALUES (5, 'qwe', '88');
INSERT INTO `test` VALUES (6, 'qwe', '99');
INSERT INTO `test` VALUES (7, 'asd', '11');
INSERT INTO `test` VALUES (8, 'asd', '99');

SET FOREIGN_KEY_CHECKS = 1;

导入后的样子

image-20230303192751893

MySQL case when 用法

参考:https://blog.csdn.net/rongtaoup/article/details/82183743

首先我们先写出对单个表实现的Sql语句

拿获取一名同学的测试成绩分段来说

1
2
3
4
5
6
7
8
9
10
11
SELECT 
   CASE 
      WHEN score >= 90 THEN '优秀' 
      WHEN score >= 80 AND score < 90 THEN '良好' 
      WHEN score >= 70 AND score < 80 THEN '中等' 
      WHEN score >= 60 AND score < 70 THEN '及格' 
      ELSE '不及格' 
   END AS grade, 
   COUNT(*) AS count 
FROM test where username ='qwe'
GROUP BY grade;

结果：

1
2
3
4
5
6
7
8
+--------+-------+
| grade  | count |
+--------+-------+
| 不及格 |     3 |
| 中等   |     1 |
| 优秀   |     1 |
| 良好   |     1 |
+--------+-------+

那么如何实现多个表共用呢？

其实我们只需要让表名,以及表示成绩的字段(因为成绩字段不一定都是score，也可能使test_score)可变就行.

但我们得知道，MySQL的查询语句中，form后面的表名和where后面的字段名是不能被单引号包裹的(前者包裹后，语法错误；后者包裹后字段名变成常量值)，这就意味着我们需要用${}来连接表名和字段名.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
mysql> select * from 'test';
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''test'' at line 1

===================================================================================

mysql> select * from test where username='qwe';
+----+----------+-------+
| id | username | score |
+----+----------+-------+
|  1 | qwe      |    11 |
|  2 | qwe      |    22 |
|  3 | qwe      |    22 |
|  4 | qwe      |    77 |
|  5 | qwe      |    88 |
|  6 | qwe      |    99 |
+----+----------+-------+
6 rows in set (0.10 sec)

mysql> select * from test where 'username'='qwe';
Empty set

mysql> select * from test where 'username'='username';
+----+----------+-------+
| id | username | score |
+----+----------+-------+
|  1 | qwe      |    11 |
|  2 | qwe      |    22 |
|  3 | qwe      |    22 |
|  4 | qwe      |    77 |
|  5 | qwe      |    88 |
|  6 | qwe      |    99 |
|  7 | asd      |    11 |
|  8 | asd      |    99 |
+----+----------+-------+
8 rows in set (0.10 sec)

所以，我们只需使用${}和#{}对上面的sql语句进行改造。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
tbName – 表名
tbIdName – id字段名
idValue – id值
tbScoreName – 成绩字段名

SELECT 
   CASE 
      WHEN ${tbScoreName} >= 90 THEN '优秀' 
      WHEN ${tbScoreName} >= 80 AND score < 90 THEN '良好' 
      WHEN ${tbScoreName} >= 70 AND score < 80 THEN '中等' 
      WHEN ${tbScoreName} >= 60 AND score < 70 THEN '及格' 
      ELSE '不及格' 
   END AS grade, 
   COUNT(*) AS count 
FROM ${tbName} where ${tbIdName}=#{idValue}
GROUP BY grade;

上述sql即可做到：查询某找表，某个人的某个成绩分段