A表字段的值计不计算由B表字段值来决定.怎么写呢?

Question

本帖最后由 chirea 于 2011-05-21 07:45:42 编辑

Answer 1

case when WhetherCalOvertime=1 then 执行计算公式 end 

Answer 2

计算方式图
--相要的结果
/*
ID          Numbers         BCID           ClassUP1                ClassNext1              ClassUP2                ClassNext2              ClassUP3                ClassNext3              ClassUP4                ClassNext4              LateNumber                              LeftEarlyNumber                         LateMinutesNumber                       AbsenteeismHours                        LeftEarlyMinutesNumber
----------- --------------- -------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
1           001             1              2011-05-06 08:16:17.000 2011-05-06 12:16:17.000 2011-05-06 13:00:17.000 2011-05-06 17:50:17.000 2011-05-06 20:00:17.000 2011-05-06 23:00:10.000 NULL                    NULL                    NULL                                    NULL                                    0.46                                    3                                       NULL
2           002             2              2011-05-06 08:16:17.000 2011-05-06 20:16:17.000 NULL                    NULL                    NULL                    NULL                    NULL                    NULL                    NULL                                    NULL                                    0.46                                    4                                       NULL
3           003             3              2011-05-06 08:16:17.000 2011-05-06 23:16:17.000 NULL                    NULL                    NULL                    NULL                    NULL                    NULL                    NULL                                    NULL                                    0.46                                    0                                       NULL*/

Answer 3

1、SELECT * FROM #EmployeesAttendance e LEFT JOIN (SELECT * FROM #ClassManage WHERE WhetherCalOvertime=1 )c ON e.BCID=c.ClassNumber --左连接，把等于0的值去掉
2、SELECT * FROM #EmployeesAttendance e WHERE BCID IN (SELECT ClassNumber FROM #ClassManage WHERE WhetherCalOvertime=1)--嵌套一下
3、还有就先全都计算出来，再把WhetherCalOvertime=1时候的值替换为null
以上参考