exists取代内联基本例子

之前是exists理论mysql的EXISTS理论
但是今天写sql突然用到exists

原因：

任务表t_project_template_task和标签表t_project_template_task_label没有相互的关联外键
菜鸟的我一开始想不通怎么关联(习惯写on关联条件)，后来就产生了内联

内联使用

#当前用户查询时间段的任务的label(label是json,存着label表的uuid) 后面就是 t1是五条数据
SELECT a.uuid,label  FROM t_project_template_task a
LEFT JOIN t_project_template_task_executor e ON a.uuid = e.task_id,sys_user s
WHERE s.user_name_id = e.user_id AND e.user_id = "ewsd0001" AND
a.`label` IS NOT NULL AND
IFNULL(DATE_FORMAT(a.start_time,'%Y-%m-%d'),'1970-01-01') <= IFNULL("2024-03-20",'2099-01-01') AND
IFNULL(DATE_FORMAT(a.end_time,'%Y-%m-%d'),DATE_FORMAT(a.end_time,'%Y-%m-%d')) >= IFNULL(NULL,'1970-01-01')
ORDER BY a.create_time DESC;

执行结果

#t2是153条数据
SELECT * FROM t_project_template_task_label;

执行结果

#由于两个表没有关联外键，最后直接内联，笛卡尔乘积
#内联查找任务里包含的标签  t1是五条数据
# 内联结果就是 765条数据 再用where筛选(下面执行结果没加where执行)
SELECT t1.label,t2.`uuid` FROM 
(SELECT label  FROM t_project_template_task a
LEFT JOIN t_project_template_task_executor e ON a.uuid = e.task_id,sys_user s
WHERE s.user_name_id = e.user_id AND e.user_id = "ewsd0001" AND
a.`label` IS NOT NULL AND
IFNULL(DATE_FORMAT(a.start_time,'%Y-%m-%d'),'1970-01-01') <= IFNULL("2024-03-20",'2099-01-01') AND
IFNULL(DATE_FORMAT(a.end_time,'%Y-%m-%d'),DATE_FORMAT(a.end_time,'%Y-%m-%d')) >= IFNULL(NULL,'1970-01-01')
ORDER BY a.create_time DESC ) t1, `t_project_template_task_label` t2
WHERE  INSTR(t1.label,t2.`uuid`)  > 0

补充知识

因为我们目标是
在当前用户查询时间段的任务的label(label是json,存着label表的uuid) 存在那些label 用到instr函数
所以在多对多的时候加上 WHERE INSTR(t1.label,t2.uuid) > 0

用exists取代上面内联

#用exists取代内联，不知道性能是不是比内联高
SELECT t2.`uuid` FROM `t_project_template_task_label` t2
WHERE EXISTS(
SELECT 1 FROM
(SELECT label 
FROM t_project_template_task a
LEFT JOIN t_project_template_task_executor e ON a.uuid = e.task_id,sys_user s
WHERE s.user_name_id = e.user_id AND e.user_id = "ewsd0001" AND
a.`label` IS NOT NULL AND
IFNULL(DATE_FORMAT(a.start_time,'%Y-%m-%d'),'1970-01-01') <= IFNULL("2024-03-20",'2099-01-01') AND
IFNULL(DATE_FORMAT(a.end_time,'%Y-%m-%d'),DATE_FORMAT(a.end_time,'%Y-%m-%d')) >= IFNULL(NULL,'1970-01-01')
ORDER BY a.create_time DESC ) t1
WHERE  INSTR(t1.label,t2.`uuid`)  > 0
)