项目中一条sql关于case when then else end的使用与小结

项目中一条sql关于case when then else end的使用与小结

业务表
DROP TABLE IF EXISTS apply_broadband_t;
CREATE TABLE apply_broadband_t (
id varchar(55) NOT NULL,
district varchar(25) DEFAULT NULL COMMENT ‘区县’,
community varchar(55) DEFAULT NULL COMMENT ‘小区名’,
custom_name varchar(25) DEFAULT NULL COMMENT ‘用户姓名’,
custom_phone varchar(25) DEFAULT NULL COMMENT ‘用户电话’,
custom_address varchar(55) DEFAULT NULL COMMENT ‘用户地址’,
schedule_date varchar(55) DEFAULT NULL COMMENT ‘预约时间’,
mark_desc varchar(255) DEFAULT NULL COMMENT ‘备注描述’,
work_state varchar(5) DEFAULT NULL COMMENT ‘表示事件处理的进度 0：表示还没有开始处理’,
apply_state varchar(5) DEFAULT NULL COMMENT ‘宽带安装的申请事件的状态 0：表示 刚刚提交 ；-1：表示作废’,
has_worker varchar(5) DEFAULT NULL COMMENT ‘是否有对应的工程师’,
approve_id varchar(55) DEFAULT NULL COMMENT ‘最终审批人’,
approve_state varchar(5) DEFAULT NULL COMMENT ‘最终审批状态（1:结束）’,
approve_date varchar(55) DEFAULT NULL COMMENT ‘最终审批时间’,
server_level varchar(55) DEFAULT NULL COMMENT ‘录单人员满意度评价(满意或者不满意)’,
approve_mark varchar(255) DEFAULT NULL COMMENT ‘最终审批人意见’,
workerId varchar(25) DEFAULT NULL COMMENT ‘对应的区域负责人’,
createBy varchar(55) DEFAULT NULL,
createDate varchar(55) DEFAULT NULL,
lastUpdateBy varchar(55) DEFAULT NULL,
lastUpdateDate varchar(55) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

用户表
DROP TABLE IF EXISTS user;
CREATE TABLE user (
id bigint(20) NOT NULL COMMENT ‘主键ID’,
loginName varchar(30) NOT NULL COMMENT ‘登录名称’,
password varchar(32) NOT NULL COMMENT ‘密码’,
email varchar(60) DEFAULT NULL COMMENT ‘邮箱’,
type varchar(500) NOT NULL DEFAULT ‘0’ COMMENT ‘0、普通用户 1、管理员’,
status smallint(6) NOT NULL DEFAULT ‘1’ COMMENT ‘0、禁用 1、正常’,
crTime varchar(30) NOT NULL COMMENT ‘创建时间’,
lastTime varchar(30) NOT NULL COMMENT ‘最后登录时间’,
userFullName varchar(20) DEFAULT NULL COMMENT ‘真实姓名’,
phoneNumber varchar(15) DEFAULT NULL COMMENT ‘电话号码’,
sex varchar(2) DEFAULT NULL COMMENT ‘性别’,
nickName varchar(50) DEFAULT NULL COMMENT ‘昵称’,
imageUrl varchar(255) DEFAULT NULL,
jobNo varchar(50) DEFAULT NULL COMMENT ‘工号’,
organizationId varchar(300) DEFAULT NULL COMMENT ‘组织隶属关系ID’,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=‘用户表’;

<select id="getWorkListByCreateBy" resultType="java.util.Map">
		SELECT 
			t.id, 
			t.district, 
			t.community, 
			t.custom_name, 
			t.custom_phone, 
			t.custom_address, 
			t.schedule_date, 
			t.mark_desc, 
			t.work_state,
			t.apply_state, 
			t.has_worker, 
			t.workerId,
			t.createDate,
			t.approve_state,
			CASE 
				WHEN work_state='0' AND apply_state='0' THEN '待接单'
				WHEN work_state='1' AND apply_state='1' THEN '待预约'
				WHEN work_state='2' AND apply_state='2' THEN '待安装'
				WHEN work_state='3' AND apply_state='3' THEN '安装成功'
				WHEN work_state='9' AND apply_state='1' THEN '无法预约'
				WHEN work_state='8' AND apply_state='2' THEN '无法安装'
				WHEN work_state='-1' AND apply_state='0' THEN '拒绝接单'
			ELSE '待分配'
			END as currentState,
			u.userFullName as workerName,
			u.loginName as workerPhone,
			t.createBy,
			u2.userFullName as applyUser,
			u2.loginName as applyPhone
		FROM apply_broadband_t t
		LEFT JOIN user u on t.workerId = u.id
		LEFT JOIN user u2 on t.createBy = u2.id
		WHERE t.createBy=#{0} AND t.approve_state IS NULL AND (  t.work_state = 3 
		OR (t.work_state = -1 AND t.apply_state = 0) OR (t.work_state = 9 AND t.apply_state = 1) OR(t.work_state = 8 AND t.apply_state = 2))		
		<if test='_parameter.get("1") != null and  _parameter.get("1") !=""'>
			and district = #{1}
		</if>
		ORDER BY createDate DESC LIMIT  #{2}, #{3}
	</select>



在业务表中createBy为数据的提交人，而workerId为业务的执行者。都是对应user表中的id；
	所以
	(1):以前一直认为左连接只能连接某个表一次，实际发现通过apply_broadband_t通过左连接user表两次；
	(2):判断某个字段为空或是非空时，不能使用 approve_state ==NULL 、approve_state !=NULL;而是 approve_state IS NULL或是 approve_state IS NOT NULL；
	(3)：如果需要对字段进行某些判断可以使用 case when then else end；