SQL 左连接（多重连接条件）

Question

我有两个名为“签入”和“签出”的派生表

签入

CheckDate  CheckIn
---------- ---------
08/02/2011 10:10:03
08/02/2011 15:57:16
07/19/2011 13:58:52
07/19/2011 16:50:55
07/26/2011 15:11:24
06/21/2011 12:36:47
08/16/2011 14:49:36
08/09/2011 13:52:10
08/09/2011 16:54:51
08/23/2011 15:48:58
09/06/2011 15:23:00
09/13/2011 10:09:27
09/13/2011 10:40:14
09/13/2011 11:43:14
09/13/2011 11:59:32
09/13/2011 17:05:24
09/20/2011 11:03:42
09/20/2011 12:08:50
09/20/2011 15:21:06
09/20/2011 15:34:29
09/27/2011 11:34:06
10/04/2011 11:37:59
10/04/2011 15:24:04
10/04/2011 16:57:44
10/11/2011 18:19:33

签出

CheckDate  CheckOut
---------- ---------
08/02/2011 13:29:40
08/02/2011 17:02:25
07/12/2011 17:06:06
07/19/2011 16:40:15
07/19/2011 17:07:35
07/26/2011 14:48:10
07/26/2011 17:27:08
05/31/2011 17:01:39
06/07/2011 17:04:29
06/14/2011 17:08:50
06/21/2011 17:03:46
06/28/2011 17:10:45
07/05/2011 17:02:48
08/16/2011 13:37:36
08/16/2011 17:06:34
08/09/2011 12:00:29
08/09/2011 13:29:36
08/09/2011 14:36:09
08/09/2011 17:00:38
08/23/2011 13:37:11
08/23/2011 17:01:37
09/06/2011 17:00:09
09/13/2011 10:11:50
09/13/2011 11:22:02
09/13/2011 11:47:35
09/13/2011 14:13:36
09/13/2011 14:14:25
09/13/2011 17:08:43
09/20/2011 09:54:55
09/20/2011 11:55:31
09/20/2011 11:55:36
09/20/2011 13:35:16
09/20/2011 15:26:02
09/20/2011 16:33:21
09/20/2011 17:07:52
09/27/2011 11:12:38
10/04/2011 13:26:31
10/04/2011 16:32:56
10/04/2011 17:02:35
10/11/2011 18:25:32

我想将签入与签出配对，前提是两个日志都位于同一位置日期和签出值不能早于签入，因此我提出了此查询，

  SELECT A.ChkDt
      AS CheckDate,
         B.CheckIn,
         MIN(A.ChkTm)
      AS CheckOut
    FROM #tempAttLogs
      AS A LEFT JOIN
         (SELECT ChkDt
              AS CheckDate,
                 MIN(ChkTm)
              AS CheckIn
            FROM #tempAttLogs
           WHERE ChkTp = 'I'
        GROUP BY ChkDt) B
      ON A.ChkDt = B.CheckDate
   WHERE ChkTp = 'O' AND
         A.ChkTm > B.CheckIn
GROUP BY A.ChkDt, B.CheckIn

结果集为 的

CheckDate  CheckIn   CheckOut
---------- --------- ---------
06/21/2011 12:36:47  17:03:46
07/19/2011 13:58:52  16:40:15
07/26/2011 15:11:24  17:27:08
08/02/2011 10:10:03  13:29:40
08/09/2011 13:52:10  14:36:09
08/16/2011 14:49:36  17:06:34
08/23/2011 15:48:58  17:01:37
09/06/2011 15:23:00  17:00:09
09/13/2011 10:09:27  10:11:50
09/20/2011 11:03:42  11:55:31
10/04/2011 11:37:59  13:26:31
10/11/2011 18:19:33  18:25:32

事情是我还想包含没有对应对的日志。这怎么可能？

编辑

我的预期结果集必须包含这样的日志

CheckDate  CheckIn   CheckOut
---------- --------- ---------
05/23/2011 NULL      17:04:27

Answer 1

您可以将引用右侧表的任何谓词移动到 ON 子句中，而不是 WHERE 中，以避免将 JOIN 变成 INNER JOIN

例如

ON A.ChkDt = B.CheckDate AND
         A.ChkTm > B.CheckIn
WHERE ChkTp = 'O'

代替

    ON A.ChkDt = B.CheckDate
   WHERE ChkTp = 'O' AND
         A.ChkTm > B.CheckIn

Answer 2

在 WHERE 子句中包含空值 例如：

WHERE( ChkTp = 'O' AND
         A.ChkTm > B.CheckIn) OR B.CheckDate IS NULL

Answer 3

这可能对解决您的问题有用（未经测试），尽管它与您的问题略有相切。

select 
  Checkdate, CheckIn as CheckTime, 'I' as CheckAction
from CheckIns 

union all

select 
  Checkdate, CheckOut as CheckTime, 'O' as CheckAction
from CheckOuts
order by 1,2