时间戳

首页 > 解决方案 > 选择在 2 个日期之间，但也选择 NULL 值

mysql - 选择在 2 个日期之间，但也选择 NULL 值

问题描述

我正在尝试运行一个查询，在其中选择 2 个值之间的日期

OR ( `order_products`.`deleted_at` BETWEEN
                 '2021-01-01 00:00:00' AND '2021-07-28 23:59:59' )

但我还需要收集具有 NULL 值的 order_products。

这有可能吗，就像我放一个

OR `order_products`.`deleted_at` IS NULL

在我的代码中，它似乎只返回了数据库中的所有内容:-(

我当前的代码，对于上下文是：

SELECT 
`orders`.`id`, 
`orders`.`reference`, 
`orders`.`order_date`, 
`orders`.`cancellation_date`,
 `order_products`.`description`, 
`order_products`.`st_code`, 
`order_products`.`supplier_stock_code`,
 `order_products`.`status`, 
`order_products`.`deleted_at`,
 `supplier`.`name`, 
 order_invoice.created_at AS credit_date, 
fitters.account_number AS fitter_account_number, 
fitters.name AS fitter 
FROM `order_products`
 LEFT JOIN `orders` ON (`orders`.`id` = `order_products`.`order_id`) 
LEFT JOIN `supplier` ON (`order_products`.`supplier_id` = `supplier`.`id`) 
LEFT JOIN `fitters` ON (`orders`.`fitter_id` = `fitters`.`id`) 
JOIN `order_invoice` ON (`order_invoice`.`order_id` = `orders`.`id`) 
WHERE `cancellation_date` IS null 
AND `cancellation_date` BETWEEN '2021-01-01 00:00:00' AND '2021-07-28 23:59:59'
 AND `order_invoice`.`status` = 'approved' 
OR (`order_products`.`deleted_at` BETWEEN '2021-01-01 00:00:00' 
OR `order_products`.`deleted_at` IS NULL
AND '2021-07-28 23:59:59') 
AND `order_products`.`product_type` = 'product' 
ORDER BY `orders`.`id` ASC

标签： mysqlwhere-clause

解决方案

请尝试将 where 条件更改为 WHERE cancellation_dateIS null AND cancellation_dateBETWEEN '2021-01-01 00:00:00' AND '2021-07-28 23:59:59' AND order_invoice。status=“已批准”或（order_products。为deleted_at空或order_products。deleted_at在“2021-01-01 00:00:00”和“2021-07-28 23:59:59”之间）和order_products。product_type='产品'

推荐阅读