MySql中判断字符串相等
参考博客:https://blog.csdn.net/yangfengjueqi/article/details/72821603
教程:https://www.runoob.com/mysql/mysql-operator.html
项目案例:V1--->V3实现过程。
SELECT
b.issue_id,
b.current_version,
b.customer_cloud_name,
b.gmt_actual_resolve,
b.gmt_expect_resolve,
b.gmt_issue_feedback,
b.gmt_modified,
b.issue_create_person,
b.issue_duty_person,
b2.sended_timout_ding,
b.issue_feedback_person,
b.issue_name,
b.issue_no,
b.issue_present_person,
b.issue_priority,
b.issue_status,
b.major_catagory,
b.rel_project ,
b2.aone_url,
b2.cost_impact_level,
b2.deliver_stage,
b2.functional_impact_level,
b2.is_analyse,
b2.is_firmware,
b2.question_party,
b2.submit_aone,
b2.submit_work_order,
b2.work_order_url,
b2.now_look_person,
b2.gmt_allocation,
b2.gmt_deliver,
b2.gmt_to_sovling,
c.subproject_id, c.subproject_name
FROM issue_detail b
INNER JOIN deliver_question b2 ON b.issue_id = b2.issue_id
INNER JOIN deliver_subproject c ON b2.subproject_id = c.subproject_id
WHERE
/*优先级 : 紧急1:15分钟; 高2:1小时; 中3:3小时 ; 问题分配时间 和 到当前时间 间隔。 */
CASE
when b.issue_priority =1 then timestampdiff(MINUTE,b2.gmt_allocation,NOW()) > 15
when b.issue_priority =2 then timestampdiff(MINUTE,b2.gmt_allocation,NOW()) > 60
when b.issue_priority =3 then timestampdiff(MINUTE,b2.gmt_allocation,NOW()) > 180
END
and b.is_valid=1
AND b2.is_valid=1
AND c.is_valid=1
and b.issue_duty_person like '%[%' /* 值班人不为空 */
AND b.issue_status = '1' /* 处理中 */
AND b2.gmt_allocation IS NOT NULL /* 分配时间不为空 */
/* ---------------V3新增条件,发送过通知: <=> 和 = 效果不同,<=>可以严格比较null,=不能比较有null情况 。---ok */
and !(b.issue_duty_person <=> b2.sended_timout_ding)
/* ---------------V2新增条件,发送过通知: <> 和 != 效果相同,都不能比较有null情况 ----不行 */
and b.issue_duty_person <> b2.sended_timout_ding
/* ---------------V1新增条件,发送过通知: like 方式 取非---ok, 但是不严谨,而且语法繁琐 */
and b.issue_id NOT IN (
SELECT
b.issue_id
FROM issue_detail b
INNER JOIN deliver_question b2 ON b.issue_id = b2.issue_id
INNER JOIN deliver_subproject c ON b2.subproject_id = c.subproject_id
WHERE
/*优先级 : 紧急1:15分钟; 高2:1小时; 中3:3小时 ; 问题分配时间 和 到当前时间 间隔。 */
CASE
when b.issue_priority =1 then timestampdiff(MINUTE,b2.gmt_allocation,NOW()) > 15
when b.issue_priority =2 then timestampdiff(MINUTE,b2.gmt_allocation,NOW()) > 60
when b.issue_priority =3 then timestampdiff(MINUTE,b2.gmt_allocation,NOW()) > 180
END
and b.is_valid=1
AND b2.is_valid=1
AND c.is_valid=1
and b.issue_duty_person like '%[%' /* 值班人不为空 */
AND b.issue_status = '1' /* 处理中 */
AND b2.gmt_allocation IS NOT NULL /* 分配时间不为空 */
and b.issue_duty_person LIKE CONCAT('%',b2.sended_timout_ding, '%' ) /* 新增条件: 发送过通知 */
)