空值取代'缺席'？ SQL查询 [英] Null value replace to 'absent' ? sql query

问题描述

空值取代缺席如何？



这是我的查询



仅限一段时间out值为null，因此null值替换为Absent

Null value replace "Absent" how?

this is my query

some time only in out value is null , so null value replace to "Absent"

select  distinct
E.USERID as [رقم الموظف],
U.User_FirstName+ ' ' + U.User_LastName as [اسم الموظف] ,
CONVERT(Date,LOCALTIMESTAMP) as [تاريخ العمل],
(select min(Convert (TIME,LOCALTIMESTAMP))
from access_event_logs As MINCE
where CAST(MINCE.LOCALTIMESTAMP as DATE)=CAST(E.LOCALTIMESTAMP As DATE) AND MINCE.EVENTID ='In' and MINCE.USERID=E.USERID) As [وقت الدخول],
(select max(Convert (TIME,LOCALTIMESTAMP))
from access_event_logs As MAXCE
where CAST(MAXCE.LOCALTIMESTAMP as DATE)=CAST(E.LOCALTIMESTAMP As DATE) AND MAXCE.EVENTID ='Out' and MAXCE.USERID=E.USERID) As [وقت الخروج]
from access_event_logs As E Inner join access_user as U on E.USERID = U.User_ID where Convert(date,LOCALTIMESTAMP) between '14/May/2014' And '14/May/2014'

推荐答案

用例陈述：

use case statement:

select case when column1 is null then 'Absent' else  column1 end from table1

考虑使用 ISNULL 或 COALESCE 请参阅 msdn参考 [< a href =http://msdn.microsoft.com/en-us/library/ms190349.aspxtarget =_ blanktitle =New Window> ^ ]讨论了isnull，coalesce的优点和案例。



例如如果'Out'是默认值...

Consider using ISNULL or COALESCE see msdn reference[^] which discusses the merits of isnull, coalesce and case.

e.g. if 'Out' is the default...

AND ISNULL(MAXCE.EVENTID, 'Absent') ='Out' 

或

or

AND COALESCE(MAXCE.EVENTID, 'Absent') ='Out' 

您可以使用 ISNULL 内置sql server .... :)

，语法是..

You can use ISNULL built in function of sql server .... :)
and the syntax is..

select ISNULL(columnName,'What ever would you like to replace') from TableName

现在，你可以根据它修改它根据你的需要.. :)）

Now, You can modified it according to your need.. :)

这篇关于空值取代'缺席'？ SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！