mysql 返回值，即使它为空 [英] mysql return value even if it is null

问题描述

如果日期范围匹配，我有一个返回值的查询.如果不匹配，则不返回任何内容.

I have a query that returns value if the date range is match. and if it is not match it returns nothing.

SELECT start, end
FROM (`taxemployee`) 
LEFT JOIN `project_staff_assignment` ON `project_staff_assignment`.`taxemployee_id` = `taxemployee`.`id` 

 WHERE (start >= '2014-09-01' AND end <= '2014-09-15')
    OR (end >=  '2014-09-15' AND start <= '2014-09-01')

GROUP BY `taxemployee`.`id` 
ORDER BY `assigned_hours`ASC

如果我把它放在WHERE

if I put this on WHERE

OR coalesce(`start`, `end`) IS NULL

它返回空值，但不包括带值

it returns null values, but not includes with value

即使结果为空且有值，我如何返回结果

How do I return result even if it is null and with value

这里是sqlfiddle

here is sqlfiddle

http://sqlfiddle.com/#!2/8bbe4/3

推荐答案

更新:

在这里参考小提琴:http://sqlfiddle.com/#!2/2babf/2

如果日期在范围内，我将使用 case 语句显示日期，如果不在范围内，则显示为空.

I am using case statement to display the date if it falls in the range and am displaying null if not.

SELECT `taxemployee`.`id`, Nickname, 
case when project_staff_assignment.startdt >= '2014-09-01' THEN project_staff_assignment.STARTdt ELSE NULL  END AS STARTDATE,
case when project_staff_assignment.ENDdt >= '2014-09-15' THEN project_staff_assignment.ENDdt ELSE NULL END AS ENDDATE
FROM (`taxemployee`) 
LEFT JOIN `project_staff_assignment` ON `project_staff_assignment`.`taxemployee_id` = `taxemployee`.`id` 

GROUP BY `taxemployee`.`id` 
ORDER BY `taxemployee`.`id`  ASC

<小时>

原始答案:

在此处参考 Fiddle:http://sqlfiddle.com/#!2/8bbe4/52

Refer Fiddle here: http://sqlfiddle.com/#!2/8bbe4/52

SELECT `taxemployee`.`id`, Nickname, start, end
FROM (`taxemployee`) 
LEFT JOIN `project_staff_assignment` ON `project_staff_assignment`.`taxemployee_id` = `taxemployee`.`id` 

 WHERE (start >= '2014-08-01' AND end <= '2014-08-15')
    OR (end >=  '2014-08-15' AND start <= '2014-09-01')
    OR coalesce(`start`, `end`) IS NULL

GROUP BY `taxemployee`.`id` 
ORDER BY `taxemployee`.`id`  ASC;

没有返回记录，因为，值仅适用于 01.08.2014 而您的条件适用于 01.09.2014

Records were not returned because, Value was available only for 01.08.2014 whereas, your condition was for 01.09.2014

这篇关于mysql 返回值，即使它为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！