case when遇到的问题

布满荆棘的人生 2023-05-21 10:29 97阅读 0赞

# 1、数据库表 #

user表结构和数据

CREATE TABLE `user`  (
      `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id',
      `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',
      `age` int(11) NOT NULL COMMENT '年龄',
      `status` int(11) NOT NULL DEFAULT 0 COMMENT '状态',
      `points` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '积分',
      `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      `update_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
    
    INSERT INTO `user` VALUES (1, '张三', 12, 0, 0, '2020-04-20 19:07:47', '2020-04-20 19:07:47');

user\_test表

CREATE TABLE `user_test` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `user_id` bigint(20) NOT NULL,
      `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

# 2、case when查询错误 #

当user\_test没有数据时，也要查出user的数据

SELECT
    	u.*,
    	ut.* 
    FROM
    	USER u
    	LEFT JOIN user_test ut ON ( u.id = ut.user_id ) 
    WHERE
    	u.id = 1 
    AND
    CASE
    	
    	WHEN u.age = 11 THEN
    	ut.id = 1 END;

![2020042019165021.png][]

查询结果是空的，因为没有else。

# 3、正确的写法 #

SELECT
    	u.*,
    	ut.* 
    FROM
    	USER u
    	LEFT JOIN user_test ut ON ( u.id = ut.user_id ) 
    WHERE
    	u.id = 1 
    AND
    CASE
    	
    	WHEN u.age = 11 THEN
    	ut.id = 1 ELSE 1 = 1 END;

![20200420192220430.png][]

# 4、OR的写法 #

SELECT
    	u.*,
    	ut.* 
    FROM
    	USER u
    	LEFT JOIN user_test ut ON ( u.id = ut.user_id ) 
    WHERE
    	u.id = 1 
    	AND ( ( u.age = 11 AND ut.id = 1 ) OR ( 1 = 1 ) );

[2020042019165021.png]: /images/20230521/ee6df658518c48f788fad47432230b83.png
[20200420192220430.png]: https://img-blog.csdnimg.cn/20200420192220430.png