代码之家 › 专栏 › 技术社区 › Sayed Mohd Ali

我在子查询MySQL的NOT IN和IF IN Where子句中使用了NOT IN和IF,这是正确的方法吗?

sql mysql

Sayed Mohd Ali · 技术社区 · 7 年前

工作查询: 我的查询正在运行,我也得到了我想要的结果。我只是想知道我是否做错了什么,因为我听说在Where子句中使用if不是正确的方法,但在我的例子中,它甚至在子查询中。我自己提出了这个问题。请告诉我是否有更好的查询替代方案。

寻找: 我试图让所有在14:00&之间不忙的员工;15:00,其中14:00和15:00是来自输入字段的时间(如搜索)。

 SELECT * 
   FROM `schedule` 
  WHERE appointment_id NOT IN (
          SELECT appointment_id 
            FROM `schedule` 
           WHERE ( IF(start_time < '14:00', '14:00', start_time) >= '14:00' 
             AND   IF(end_time > '15:00', '15:00', end_time) >= '14:00') 
             AND  (IF(start_time < '14:00', '14:00', start_time) <= '15:00' 
             AND   IF(end_time > '15:00', '15:00', end_time) <= '15:00') 
             AND  `appoint_date` = '2018-11-30')

5 回复 | 直到 7 年前

Tim Biegeleisen 7 年前

我甚至不明白这件事的意义 IF 调用,我们可能会在不使用它们的情况下重写您的查询:

SELECT * 
FROM schedule 
WHERE appointment_id NOT IN (
    SELECT appointment_id 
    FROM schedule
    WHERE
        end_time     >= '14:00' AND -- this
        start_time   <= '15:00' AND -- and this tests for all possible cases of overlap
        appoint_date = '2018-11-30');

我在你的电脑里删除了两个条件 WHERE

IF (start_time < '14:00', '14:00', start_time) >= '14:00'

这永远是真的,因为任何时候 14:00 . 所以,这样的时刻总是会发生 >= 14:00 更大的 比 14:00

forpas 7 年前

如果查询给出了所需的结果并且足够快,那么就可以了。
你可以做一些改进,比如在索引不存在的情况下创建索引(我相信) appointment_id 已编制索引)。
有可能 NOT EXISTS 而不是 NOT IN 这可能是一个进步。
WHERE 像这样的部分:

WHERE (IF(start_time < '14:00', '14:00', start_time) BETWEEN '14:00' AND '15:00')
  AND (IF(end_time > '15:00', '15:00', end_time) BETWEEN '14:00' AND '15:00') 
  AND  (`appoint_date` = '2018-11-30')

Zaynul Abadin Tuhin 7 年前

我更喜欢 not eixsts

 select * from schedule t where not exists
  (select 1 from schedule t1 where 
  t1.appointment_id=t.appointment_id and
  start_time>='14:00' and end_time<='15:00' and appoint_date` = '2018-11-30'
  from )

LukStorms 7 年前

如果在“选择”对话框中默认为固定时间,则可以使用该选项。

如果你也不想包括那些在14:00之前开始到15:00之后结束的?
然后试试这个:

SELECT DISTINCT employee_id
FROM `schedule` s
WHERE `appoint_date` = '2018-11-30'
  AND NOT EXISTS (
        SELECT 1
        FROM `schedule` s2
        WHERE s2.`appoint_date` = '2018-11-30'
          AND s2.start_time < '15:00'
          AND s2.end_time > '14:00'
          AND s2.appointment_id = s.appointment_id
      );

Arth 7 年前

这是对Tim解决方案的补充回答

分解WHERE部分(我已删除多余的括号)

WHERE IF(start_time < '14:00', '14:00', start_time) >= '14:00' /** Line 1 */
  AND IF(end_time > '15:00', '15:00', end_time) >= '14:00' /** Line 2 */
  AND IF(start_time < '14:00', '14:00', start_time) <= '15:00' /** Line 3 */
  AND IF(end_time > '15:00', '15:00', end_time) <= '15:00' /** Line 4 */
  AND `appoint_date` = '2018-11-30'

看看第1行, IF(start_time < '14:00', '14:00', start_time) 将始终返回大于或等于的时间 '14:00' 对于非空的开始时间。。所以这条线和 WHERE start_time IS NOT NULL

同样,第4行也可以重写 AND end_time IS NOT NULL

看看第2行,很容易看出这相当于 AND start_time <= '15:00' . IF对开始时间的任何操作只会影响将产生真实结果的时间,并使其保持真实

同样,第3行也可以重写 AND end_time >= '14:00'

WHERE start_time IS NOT NULL /** Line 1 */
  AND end_time >= '14:00' /** Line 2 */
  AND start_time <= '15:00' /** Line 3 */
  AND end_time IS NOT NULL /** Line 4 */
  AND `appoint_date` = '2018-11-30'

现在,由于涉及空操作数的任何比较都返回false,第3行实际上免费提供了第1行

去掉这些多余的线条,你就会得到蒂姆在哪里

WHERE end_time >= '14:00' 
  AND start_time <= '15:00'
  AND appoint_date = '2018-11-30'

补遗

在Tim的WHERE中重构IF子句的另一个好处是,这允许引擎在开始时间或结束时间使用索引来满足这些条件。这可以带来显著的性能优势