如果SQL查询中的条件

Question

我有以下SQL查询

SELECT l.LoginName ,p.name as project_name, p.start_date as 
project_start_date, p.end_date as project_end_date, 
pe.budget, pe.start_date, pe.end_date  
FROM project_expenses_new pe 
INNER JOIN projects p ON  pe.project_uuid=p.uuid 
INNER JOIN LoginUsers l ON pe.employee_uuid=l.uuid 
WHERE pe.employee_uuid='8a373382-c921-11ec-a7c7-005056020962' AND 
('2021-08-01' BETWEEN  p.start_date AND p.end_date);

基本上我使用3个表，但其中只有2个表与查询相关。这些表分别是projects和projects_expenses_new。这两个表都有字段start_date和end_date。

我要做的是检查project_expenses_new表中的start_date和end_date的值是否具有非空值。在这种情况下，我将检查我正在寻找的日期是否在project_expenses_new表的start_date和end_date之间。如果pe的值。Start_date和pe。End_date不为空，查询将为:

SELECT l.LoginName ,p.name as project_name, p.start_date as 
project_start_date, p.end_date as 
project_end_date, pe.budget, pe.start_date, pe.end_date  
FROM project_expenses_new pe 
INNER JOIN projects p ON  pe.project_uuid=p.uuid 
INNER JOIN LoginUsers l ON pe.employee_uuid=l.uuid 
WHERE pe.employee_uuid='8a373382-c921-11ec-a7c7-005056020962' AND 
('2021-08-01' BETWEEN  pe.start_date AND pe.end_date);

另一方面，如果pproject_expenses_new表上的start_date和end_date的值为null，我想查询条件获取项目表start_date和end_date之间的记录。这些值永远不是NULL。

我不知道如何在我的查询中包含一个if条件。

Answer 1

如果另一个日期是NULL，则可以使用IFNULL()使用一个日期。

WHERE pe.employee_uuid = '8a373382-c921-11ec-a7c7-005056020962'
AND '2021-08-01' BETWEEN IFNULL(pe.start_date, p.start_date) AND 
IFNULL(pe.end_date, p.end_date)

Answer 2

如果必须检查start_date和end_date是否为空:

SELECT l.LoginName ,p.name as project_name, p.start_date as 
project_start_date, p.end_date as project_end_date, 
pe.budget, pe.start_date, pe.end_date  
FROM project_expenses_new pe 
INNER JOIN projects p ON  pe.project_uuid=p.uuid 
INNER JOIN LoginUsers l ON pe.employee_uuid=l.uuid 
WHERE pe.employee_uuid='8a373382-c921-11ec-a7c7-005056020962' AND 
'2021-08-01' BETWEEN (case when p.start_date is null or p.end_date is null then 
pe.p.start_date else p.start_date end) and (case when p.start_date is null or p.end_date is null then pe.p.end_date else p.end_dateend);