Mysql使用case when判断

最新推荐文章于 2024-02-25 09:00:00 发布

码奴生来只知道前进~

最新推荐文章于 2024-02-25 09:00:00 发布

阅读量726

点赞数 2

分类专栏： mysql 文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/tanqingfu1/article/details/126779646

版权

mysql 专栏收录该内容

31 篇文章 2 订阅

订阅专栏

1、创建测试表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for achievement
-- ----------------------------
DROP TABLE IF EXISTS `achievement`;
CREATE TABLE `achievement`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` int(10) NULL DEFAULT NULL COMMENT '学生主键ID',
  `type` int(3) NULL DEFAULT NULL COMMENT '类型 0-语文,1-数学,2-英语,3-历史,4-美术,5-体育',
  `total` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '分数',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '成绩表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of achievement
-- ----------------------------
INSERT INTO `achievement` VALUES (1, 1, 0, '50', '2022-09-09 11:20:53', NULL);
INSERT INTO `achievement` VALUES (2, 1, 1, '75', '2022-09-09 11:21:06', NULL);
INSERT INTO `achievement` VALUES (3, 1, 2, '80', '2022-09-09 11:21:15', NULL);
INSERT INTO `achievement` VALUES (4, 1, 3, '90', '2022-09-09 11:21:27', NULL);
INSERT INTO `achievement` VALUES (5, 1, 4, '80', '2022-09-09 11:21:37', NULL);
INSERT INTO `achievement` VALUES (6, 1, 5, '85', '2022-09-09 11:21:53', NULL);

SET FOREIGN_KEY_CHECKS = 1;

2、使用case when查询

-- 类型 0-语文,1-数学,2-英语,3-历史,4-美术,5-体育

SELECT
*,
	CASE type
	WHEN 0 THEN '语文'
	WHEN 1 THEN '数学'
	WHEN 2 THEN '英语'
	WHEN 3 THEN '历史'
	WHEN 4 THEN '美术'
	WHEN 5 THEN '体育'
	ELSE  '其他'
END subject

FROM
	`achievement` 
WHERE
	user_id = 1;