mysql 返回值，即使它为空

本文介绍了mysql 返回值，即使它为空的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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

sqlfiddle/#!2/8bbe4/3

推荐答案

更新:

在这里参考小提琴:sqlfiddle/#!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:sqlfiddle/#!2/8bbe4/52

Refer Fiddle here: sqlfiddle/#!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