我创建的这个表用了三个计算列，可这三个列第一行数据时能计算时不能计算。后面的行就都不计算了。

Question

--这是什么原因。时能计算，时不能计算,要怎么能解决呢？SQL2005
create table EmployeesLeave (
   ID                   int                  identity,
   Department           nvarchar(30)         null,
   Position             nvarchar(30)         null,
   Numbers              varchar(50)          null,
   StartTime            datetime             null,
   EndTime              datetime             null,
   LaveType             tinyint              null,
   EnjoyPaidleave       int                  null,
   Paidleave AS (case when EmployeesLeave.LaveType=6 then  (case when datediff(hh,StartTime,EndTime)<12 then 0.5  else case when datediff(hh,StartTime,EndTime)*1./24<1 then 1 end  end)else null end),
   BingJia AS  (case when EmployeesLeave.LaveType=2 then  (case when datediff(hh,StartTime,EndTime)<12 then 0.5  else case when datediff(hh,StartTime,EndTime)*1./24<1 then 1 end  end)else null end),
   ShiJia   AS   (case when EmployeesLeave.LaveType=1 then  (case when datediff(hh,StartTime,EndTime)<12 then 0.5  else case when datediff(hh,StartTime,EndTime)*1./24<1 then 1 end  end)else null end),
   Reason               nvarchar(200)        null,
   Registrant           nvarchar(30)         null,
   constraint PK_EMPLOYEESLEAVE primary key (ID)
)
goalter table EmployeesLeave
   add constraint FK_EMPLOYEE_REFERENCE_EMPLOYEELeave foreign key (Numbers)
      references Employees (Numbers)
         on update cascade on delete cascade
go

Answer 1

 你把为null的这行记录的EmployeesLeave.LaveType、StartTime、EndTime　三个字段值看一下，
肯定是不满足前面条件不满足，执行了else null

Answer 2

谢谢豆子       ID          Department                     Position                       Numbers                                            StartTime               EndTime                 LaveType EnjoyPaidleave Paidleave                               BingJia                                 ShiJia                                  Reason                                                                                                                                                                                                   Registrant
----------- ------------------------------ ------------------------------ -------------------------------------------------- ----------------------- ----------------------- -------- -------------- --------------------------------------- --------------------------------------- --------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------
1           A部门                            会计                             001                                                2011-07-22 08:39:00.000 2011-07-22 14:39:00.000 1        NULL           NULL                                    NULL                                    0.5                                                                                                                                                                                                                                              F1
2           A部门                            会计                             001                                                2011-07-22 08:39:00.000 2011-07-23 14:39:00.000 2        NULL           NULL                                    NULL                                    NULL                                                                                                                                                                                                                                             F1
3           C部门                            财务                             002                                                2011-07-23 14:45:00.000 2011-07-25 14:45:00.000 6        NULL           NULL                                    NULL                                    NULL                                                                                                                                                                                                                                             F1(3 行受影响)                                                                                                                                          F1
--这是我查询出来的结果。二三行没有计算。条件满足

Answer 3

你的最后一条记录时间差24小时，并不在你的case列举的范围内，所以然成NULL了

Answer 4

改成这：
   Paidleave AS (case when EmployeesLeave.LaveType=6 then  (case when datediff(hh,StartTime,EndTime)<12 then 0.5 when datediff(hh,StartTime,EndTime)*1./24<1 then 1 else 100 end)else null end),
   BingJia AS   (case when EmployeesLeave.LaveType=2 then  (case when datediff(hh,StartTime,EndTime)<12 then 0.5 when datediff(hh,StartTime,EndTime)*1./24<1 then 1 else 101 end)else null end),
   ShiJia   AS  (case when EmployeesLeave.LaveType=1 then  (case when datediff(hh,StartTime,EndTime)<12 then 0.5 when datediff(hh,StartTime,EndTime)*1./24<1 then 1 else 102 end)else null end)再看看你的结果

Answer 5

找下面这个公式进行分析Paidleave AS (case when EmployeesLeave.LaveType=6 
　　　　　　　　　　then  (case when datediff(hh,StartTime,EndTime)<12 
　　　　　　　　　　　　　　　　then 0.5  
                            else case when datediff(hh,StartTime,EndTime)*1./24<1 then 1 end  end)
             else null end)
如果EmployeesLeave.LaveType<>6时,肯定为空，所以只有第三条记录满足
3   C部门        财务      002    2011-07-23 14:45:00.000 2011-07-25 14:45:00.000 6        NULL           NULL       NULL    NULL  
再看条件
case when datediff(hh,StartTime,EndTime)<12 
　　　　　　　　　　　　　　　　then 0.5  
                            else case when datediff(hh,StartTime,EndTime)*1./24<1 then 1 end  enddatediff(hh,StartTime,EndTime)=2011-07-23 14:45:00.000到 2011-07-25 14:45:00.000＝48(小时)
所以case when datediff(hh,StartTime,EndTime)<12 不满足，
再看else case when datediff(hh,StartTime,EndTime)*1./24<1 then 1 end 
48*1.0/24是大于１的所以也不满足，这里你没有写else ...那么就是默认else 为null 了