SQL Server的交叉应用（cross apply）不起作用？

Question

http://sqlfiddle.com/#!3/78273/1

create table emptb1
(
id int,
name varchar(20),
dept int
)

insert into emptb1 values (1,'vish',10);
insert into emptb1 values (2,'vish',10);
insert into emptb1 values (3,'vish',30);
insert into emptb1 values (4,'vish',20);

create table depttb1
(
id int,
name varchar(20)
)

insert into depttb1 values(10,'IT')
insert into depttb1 values(20,'AC')
insert into depttb1 values(30,'LIC')

select * from emptb1

select e.id, e.name, a.id
from emptb1 e
cross apply
(
select top 1 * from depttb1 d
where d.id = e.dept
order by d.id desc
) a

我试图学习交叉应用程序，因为它与内部连接类似，但使用函数工作。

在上面的查询中，我假设它只应该获取dept=30，因为order d.id desc将仅给出第一个id即30，然后它应返回dept id=30的员工，但它给我所有行和所有deptid。

查询有什么问题，或者我对交叉应用程序的概念理解错误。

- Registered User

---

你的查询实际上是在说：“对于每个员工，找到他们所在的所有部门，并选择该员工所在的最高部门ID的部门” - 现在，显然，根据你的模式，每个员工只能在一个部门工作，但它仍将返回每个员工一行。 - Damien_The_Unbeliever

@Damien_The_Unbeliever 好的，如果一个员工在两个不同的部门工作，比如40和50，它只会给我50。但是我尝试插入一个既有40又有50部门ID的员工，但它仍然给我两个部门ID。 - Registered User

这是因为在您当前的模式中，模拟一个员工在两个部门中的唯一方法是在emptbl中有两行记录，其中（大概）具有相同的id和name？如果是这样，请再次查看您的查询 - 它在cross apply期间未提及这些列 - 因此首先它会对deptid为40的行执行操作，并且在depttbl中唯一匹配的行是具有id 40的行。因此，这是来自emptbl的第一行的顶部1匹配行。然后，我们对deptid为50的行执行相同的过程。这一次，我们匹配了一个具有id 50的行，它再次是顶部1... - Damien_The_Unbeliever

Answer 1

你说：“在上面的查询中，我假设它应该只取dept=30，因为order d.id desc只会给出第一个id，该id为30，然后应返回dept id= 30的员工”。

事实并非如此。以下是您的查询（稍作格式化以便更清晰）：

select e.id, e.name, a.id
from   emptb1 e
cross apply
(
    select top 1 * 
    from depttb1 d
    where d.id = e.dept
    order by d.id desc
) a

APPLY 关键词表示内部查询对外部查询的每一行都会执行一次（逻辑上）。为了理解内部查询中发生的情况，有助于了解 SELECT 子句的执行逻辑顺序。这个顺序是：

1. FROM 子句
2. WHERE 子句
3. SELECT 列
4. ORDER BY 子句
5. TOP 运算符

需要注意的是，在你的内部查询中，TOP 运算符最后被应用，而且是在 WHERE 子句之后。这意味着 where d.id = e.dept 会首先将内部行减少到那些其 d.id 匹配外部行的 e.dept 的行（不一定是 30），然后对它们进行排序，然后返回第一个。这是对外部查询中的每一行都执行的，很明显，其中的许多行不会是 30。

你尝试的内容更类似于这样（仍然保留 CROSS APPLY）：

select e.id, e.name, a.id
from   emptb1 e
cross apply
(
    select top 1 * 
    from
    (
        select top 1 * 
        from depttb1 d
        order by d.id desc
    ) b
    where b.id = e.dept
) a

这里，逻辑已经通过使用另一个嵌套的子查询进行重新排序，以确保ORDER BY，然后TOP 1在WHERE子句之前应用。(请注意，嵌套子查询通常不是推荐的方式，因为它们可能会影响可读性，我只是在这里使用它来保留CROSS APPLY和保留原始结构的其余部分。)

- RBarryYoung

---

哦！我觉得我通过这个答案获得了我的秘密帽子！但还是不知道为什么...？ - RBarryYoung

Answer 2

扩展达米恩的评论，内部查询为：

select top 1 * from depttb1 d
where d.id = e.dept
order by d.id desc

将每一行在外部查询中运行：

select e.id, e.name, a.id
from emptb1 e

因此，每一行都将从内部查询中获得一个匹配。我认为您期望内部查询仅运行一次，但这不是APPLY的作用。

因此，以ID为1和dept id为10的外部查询的第一行，您的内部查询将被翻译为：

select top 1 * from depttb1 d
where d.id = 10  //this is the dept id for the current row from your outer query
order by d.id desc

- Andrew

---

那么，top 1 * 的用户是什么？ - Registered User

你在depttb中每个值只有1行，我不明白你想用TOP 1做什么。 - Andrew

Answer 3

如果不使用交叉应用程序来解决这个问题，可以使用子查询。但是在您的示例中，它只会返回一行，即假设ID值递增的最后一个部门。

-- Using a sub query to find max dept
select e.id, e.name
from emptb1 e
where e.dept in
(
select top 1 id 
from depttb1 
order by id desc
)

CROSS APPLY 的概念有点像 CROSS JOIN，它会返回所有行。DBA们经常使用它来处理许多动态管理视图（DMV），这些视图是表值函数（TVF）。

而你需要的是OUTER APPLY，它有点像左连接（LEFT JOIN）。

select e.id, e.name
from emptb1 e
outer apply 
    (
    select top 1 d.id from depttb1 d order by d.id desc
    ) AS m (id)
where e.dept = m.id

看看我关于这些概念的文章。

CROSS APPLY - http://craftydba.com/?p=3767

OUTER APPLY - http://craftydba.com/?p=3796

TABLE VALUE FUNCTION (INLINE) - http://craftydba.com/?p=3733

TABLE VALUE FUNCTION (MULTI LINE) - http://craftydba.com/?p=3754

- CRAFTY DBA

---

CROSS APPLY和OUTER APPLY在这种情况下返回相同的结果，是吗？即使'内部'表中没有匹配项，OUTER APPLY也会返回来自'外部'表的行。 - Andrew

是的 - 在这个玩具例子中，CROSS = OUTER。然而，如果我们有一些更有趣的东西，比如按部门每个订单的总销售额，并且我们想要前三名，如果有数据，或者如果没有，则是一个空条目，外部 apply 是正确的方法。请查看Rob Farley的文章。http://sqlblog.com/blogs/rob_farley/archive/2011/04/13/the-power-of-t-sql-s-apply-operator.aspx - CRAFTY DBA