sql - 如何使用交叉应用的结果，可以实现在sql server中使用任何其他连接，如inner，left，right join

Question

这是我的带有示例数据的 SQL 脚本

CREATE TABLE [dbo].[Employee]
(
    [ID] [INT] IDENTITY(1,1) NOT NULL,
    [Name] [VARCHAR](100) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[LoginEntry]
(
    [ID] [INT] IDENTITY(1,1) NOT NULL,
    [LoginTime] [DATETIME] NULL,
    [EmpID] [INT] NULL,
    [GateNumber] [VARCHAR](50) NULL
) ON [PRIMARY]
GO

ALTER TABLE Employee 
    ADD CONSTRAINT Pk_Employee PRIMARY KEY (Id)
GO

ALTER TABLE LoginEntry 
    ADD CONSTRAINT Fk_LoginEntry_Employee 
        FOREIGN KEY (EmpId) REFERENCES Employee(Id)
GO

SET IDENTITY_INSERT [dbo].[Employee] ON 
GO

INSERT [dbo].[Employee] ([ID], [Name]) 
VALUES (1, N'Employee 1'), (2, N'Employee 2'), (3, N'Employee 3'),
       (4, N'Employee 4'), (5, N'Employee 5'), (6, N'Employee 6')
GO

SET IDENTITY_INSERT [dbo].[Employee] OFF
GO


SET IDENTITY_INSERT [dbo].[LoginEntry] ON 
GO

INSERT [dbo].[LoginEntry] ([ID], [LoginTime], [EmpID], [GateNumber]) 
VALUES (1, CAST(N'2014-10-24 08:00:00.000' AS DateTime), 1, N'Gate 1'),
       (2, CAST(N'2014-10-24 09:00:00.000' AS DateTime), 1, N'Gate 1'),
       (3, CAST(N'2014-10-24 10:00:00.000' AS DateTime), 1, N'Gate 2'),
       (4, CAST(N'2014-10-24 08:00:00.000' AS DateTime), 2, N'Gate 1'),
       (5, CAST(N'2014-10-24 09:00:00.000' AS DateTime), 2, N'Gate 1'),
       (6, CAST(N'2014-10-24 10:00:00.000' AS DateTime), 2, N'Gate 2'),
       (7, CAST(N'2014-10-24 08:00:00.000' AS DateTime), 3, N'Gate 1'),
       (8, CAST(N'2014-10-24 09:00:00.000' AS DateTime), 3, N'Gate 1'),
       (9, CAST(N'2014-10-24 10:00:00.000' AS DateTime), 3, N'Gate 2'),
       (10, CAST(N'2014-10-24 08:00:00.000' AS DateTime), 4, N'Gate 1'),
       (11, CAST(N'2014-10-24 09:00:00.000' AS DateTime), 4, N'Gate 1'),
       (19, CAST(N'2014-10-24 08:00:00.000' AS DateTime), 5, N'Gate 1'),
       (20, CAST(N'2014-10-24 09:00:00.000' AS DateTime), 5, N'Gate 1'),
       (21, CAST(N'2014-10-24 10:00:00.000' AS DateTime), 5, N'Gate 2'),
       (22, CAST(N'2014-10-24 08:00:00.000' AS DateTime), 6, N'Gate 1'),
       (23, CAST(N'2014-10-24 09:00:00.000' AS DateTime), 6, N'Gate 1'),
       (24, CAST(N'2014-10-24 10:00:00.000' AS DateTime), 6, N'Gate 2')

SET IDENTITY_INSERT [dbo].[LoginEntry] OFF
GO


SELECT 
    e.ID, dt.EmpId, Name, LoginTime
FROM 
    Employee e
CROSS APPLY 
    (SELECT TOP 1
         l.ID, l.LoginTime, l.EmpId
     FROM 
         LoginEntry l 
     WHERE 
         l.EmpId = e.id) dt
GO

我得到的结果:

ID  EmpId   Name            LoginTime
-----------------------------------------------
1   1       Employee 1  2014-10-24 08:00:00.000
2   2       Employee 2  2014-10-24 08:00:00.000
3   3       Employee 3  2014-10-24 08:00:00.000
4   4       Employee 4  2014-10-24 08:00:00.000
5   5       Employee 5  2014-10-24 08:00:00.000
6   6       Employee 6  2014-10-24 08:00:00.000

我期待在 sql server 中使用 Joins(inner,right,left,full) 得到相同的结果我尝试了我的运气但不能，请任何人帮助我提前谢谢

Answer 1

首先，您的查询不完整。当您使用 TOP 1 而不使用 ORDER BY 时，您永远无法保证 它会选择哪个。新数据、并发进程、重新索引、软件补丁、一天中的时间，它们都可能导致结果发生变化。

所以，应该是……

SELECT
  e.ID,dt.EmpId,Name,LoginTime
FROM
  Employee e
CROSS APPLY
(
  SELECT TOP 1
    l.ID
   ,l.LoginTime
   ,l.EmpId
  FROM
    LoginEntry l
  WHERE
    l.EmpId=e.id
  ORDER BY
    l.LoginTime DESC   -- Will cause TOP 1 to pick the most recent value (per employee)
)
  dt

至于使用连接，执行 TOP 1 (或 greatest-n-per-group，您的 n 是 1)，更长、更困惑、更慢。所以我就不多说了。

但您可以使用 ROW_NUMBER() 来执行 TOP 1 部分，然后使用 JOIN 将该结果与您的主要表...

WITH
  ordered_logins AS
(
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY LoginTime DESC, ID DESC) AS row_ordinal
  FROM
    LoginEntry
)
SELECT
  e.ID, l.EmpId, e.Name, l.LoginTime
FROM
  Employee e
LEFT JOIN
  ordered_logins l
    ON  l.EmpID = e.ID
    AND l.row_oridnal = 1

ROW_NUMBER() 为每一行分配一个从 1 向上的值(每个 EmpID - 分区子句)。它是按 loginTime 降序排列的，所以最近的登录在前，如果两个登录的时间完全相同，则按 ID desc 排序。

然后 LEFT JOIN 只选择编号为 1 (最新登录) 的行，如果没有登录则给出 NULL s 代替 (这样员工记录不会因为缺少加入而被丢弃)。

注意:APPLY 的 LEFT JOIN 等效项是使用 OUTER APPLY 而不是 CROSS APPLY.