项目中一条sql关于case when then else end的使用与小结
业务表
DROP TABLE IF EXISTSapply_broadband_t
;
CREATE TABLEapply_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 EXISTSuser
;
CREATE TABLEuser
(
id
bigint(20) NOT NULL COMMENT ‘主键ID’,
loginName
varchar(30) NOT NULL COMMENT ‘登录名称’,
password
varchar(32) NOT 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;