与日期和 ID 进行比较?

本文介绍了与日期和 ID 进行比较?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

使用 SQL Server 2000

Using SQL Server 2000

我想根据 Table1.personid 获取 Table2.TimeIn Table2.TimeOut 并且如果 Table1.Date = Table3.Date 那么它应该需要一个 Table3.TimeIn, Table3.TimeOut.

I want to get Table2.TimeIn Table2.TimeOut according to Table1.personid and also If Table1.Date = Table3.Date then it should take a Table3.TimeIn, Table3.TimeOut.

3 桌

表 1

ID    Date

001 20090503
001 20090504
001 20090506
002 20090505
002 20090506

等等……，

表 2

ID    TimeIn TimeOut

001 08:00:00 18:00:00
002 08:00:00 21:00:00

等等……，

表 3

ID    Date  TimeIn TimeOut

001 20090504 10:00:00 22:00:00
001 20090505 17:00:00 23:00:00
002 20090505 12:00:00 21:00:00

等等……，

Select Table1.ID,
       Table1.Date,
       Table2.TimeIn,
       Table2.TimeOut
  from Table1
Inner Join Table2 on Table1.ID = Table2.ID

如果 Table1.Date = Table3.Date 那么它应该取 Table3.TimeIn, Table3.TimeOut 否则 Table2.TimeIn, Table2.Timeout

If Table1.Date = Table3.Date then it should take Table3.TimeIn, Table3.TimeOut else Table2.TimeIn, Table2.Timeout

预期输出

ID Date TimeIn TimeOut

001 20090503 08:00:00 18:00:00
001 20090504 10:00:00 22:00:00
001 20090506 08:00:00 18:00:00
002 20090505 12:00:00 21:00:00
002 20090506 08:00:00 21:00:00

等等……，

如何针对这种情况编写查询?

How to write a query for this condition?

推荐答案

员工时间表回退?:

SELECT Table1.ID
    ,Table1.Date
    ,COALESCE(Table3.TimeIn, Table2.TimeIn) AS TimeIn
    ,COALESCE(Table3.TimeOut, Table2.TimeOut) AS TimeOut
FROM Table1
INNER JOIN Table2 -- Always have an expected schedule for an employee
    ON Table1.ID = Table2.ID
LEFT JOIN Table3 -- May.may not have an actual schedule for an employee
    ON Table3.ID = Table1.ID
    AND Table3.Date = Table1.Date
/*
ORDER BY Table1.ID
    ,Table1.Date
*/

这篇关于与日期和 ID 进行比较?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

1403页，肝出来的..

table3