代码之家 › 专栏 › 技术社区 › Sreenu131

使用交叉应用的结果如何能够使用SQL Server中的任何其他联接(如内部联接、左联接、右联接)来实现?

tsql sql-server sql

Sreenu131 · 技术社区 · 7 年前

这是我的带有示例数据的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中使用join(inner、right、left、full)也能得到同样的结果。我试过了,但没有成功,请大家提前帮我一下,谢谢。

3 回复 | 直到 7 年前

MatBailie 7 年前

首先,您的查询不完整。当你使用 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

至于用连接来做,做 前1名 (或) greatest-n-per-group ,您的 n 是 1 ) 更长、更凌乱、更慢。所以我不想谈这个。

但你可以使用 ROW_NUMBER() 做 前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

这个 行数() 为每行指定一个从1向上的值 (每) EmpID -分割条款) . 它是按登录时间降序排列的,所以最新的登录名是第一个,只是在两个登录名完全相同的情况下,它是按ID DESC排序的第二个登录名。

然后 LEFT JOIN 只选取编号的行 一 (最新登录) 如果没有登录名 NULL 而不是 (这样员工记录不会因为缺少联接而被丢弃) .

注: 左连接 当量 APPLY 是使用 OUTER APPLY 而不是 CROSS APPLY .

Eric Brandt 7 年前

只是为了展示一种解决问题的方法,“更长、更混乱、更慢” JOIN Matbaile没有显示的方法如下:

SELECT TOP (1) WITH TIES
  e.ID
 ,l.EmpID
 ,e.Name
 ,l.LoginTime
FROM
  dbo.Employee AS e
  JOIN
  dbo.LoginEntry AS l
    ON 
      l.EmpID = e.ID
ORDER BY 
  ROW_NUMBER() OVER (PARTITION BY e.ID ORDER BY l.LoginTime DESC, ID DESC)

这个 ROW_NUMBER 在 ORDER BY 子句获取每个员工ID的所有登录名,并使用最新的第一个登录名按登录名对其进行编号(并使用登录名ID作为连接断路器,感谢您的触摸,mat)。

然后, SELECT TOP (1) WITH TIES 做它的事。这个 WITH TIES 位意味着它选择第一个结果 从每个 PARTITION BY 组 在 按顺序 条款。

Zaynul Abadin Tuhin 7 年前

您可以使用窗口功能 row_number()

    with cte as(    
SELECT e.ID,l.EmpId,Name,l.LoginTime, row_number() over(partition by e.ID order by l.LoginTime) as rn
FROM Employee e join LoginEntry l  on l.EmpId=e.id
    ) select ID,EmpId,Name,LoginTime from cte where rn=1

demo link