Coalesce在SQL Server中不工作

本文介绍了Coalesce在SQL Server中不工作的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

Select tbl_login.userid,tbl_login.name as Employee, (case when Exists(Select * from tbl_attendance where tbl_attendance.userid=tbl_login.userid and tbl_attendance.firstlogin is not null and tbl_attendance.date=''2011/03/25'') then ''Present'' else ''Absent'' end) as Status, (select top 1 COALESCE(tbl_timesheet.description,''Idle'') from tbl_timesheet where tbl_timesheet.userid=tbl_login.userid and date=''2011/03/25'' and stoptime is null) as Filename from tbl_login where tbl_login.privilege <> ''ADMIN''

其给定描述字段为NULL(如果为null，则不给定"Idle")，即(COALESCE)不起作用 任何人都可以帮忙吗

its giving description field as NULL(if its null,not giving ''Idle''),that is (COALESCE) is not working Can Anyone help

推荐答案

可能是问题出在您的where子句中.没有什么比它匹配的?如果描述为NULL，则返回空闲"，但是如果在给定日期没有记录与用户ID匹配，该怎么办? It might be that the problem is your where clause. What is nothing matches it? ''Idle'' is returned if description is NULL, but what if there is no record that matches the userid at a given date? (select top 1 COALESCE(tbl_timesheet.description,''Idle'') from tbl_timesheet where tbl_timesheet.userid=tbl_login.userid and date=''2011/03/25'' and stoptime is null) as Filename

您需要将COALESCE包裹在完整的选择周围.

You need to wrap the COALESCE around the complete select.

COALESCE(select top 1 tbl_timesheet.description from tbl_timesheet where tbl_timesheet.userid=tbl_login.userid and date='2011/03/25' and stoptime is null, 'Idle') as Filename

祝你好运！

Good luck!