什么时候应该在内部联接上使用交叉应用?

create table Company (
    companyId int identity(1,1)
,   companyName varchar(100)
,   zipcode varchar(10) 
,   constraint PK_Company primary key (companyId)
)
GO

create table Person (
    personId int identity(1,1)
,   personName varchar(100)
,   companyId int
,   constraint FK_Person_CompanyId foreign key (companyId) references dbo.Company(companyId)
,   constraint PK_Person primary key (personId)
)
GO

insert Company
select 'ABC Company', '19808' union
select 'XYZ Company', '08534' union
select '123 Company', '10016'


insert Person
select 'Alan', 1 union
select 'Bobby', 1 union
select 'Chris', 1 union
select 'Xavier', 2 union
select 'Yoshi', 2 union
select 'Zambrano', 2 union
select 'Player 1', 3 union
select 'Player 2', 3 union
select 'Player 3', 3 


/* using CROSS APPLY */
select *
from Person p
cross apply (
    select *
    from Company c
    where p.companyid = c.companyId
) Czip

/* the equivalent query using INNER JOIN */
select *
from Person p
inner join Company c on p.companyid = c.companyId

答案

SELECT  t1.*, t2o.*
FROM    t1
CROSS APPLY
        (
        SELECT  TOP 3 *
        FROM    t2
        WHERE   t2.t1_id = t1.id
        ORDER BY
                t2.rank DESC
        ) t2o
WITH    t2o AS
        (
        SELECT  t2.*, ROW_NUMBER() OVER (PARTITION BY t1_id ORDER BY rank) AS rn
        FROM    t2
        )
SELECT  t1.*, t2o.*
FROM    t1
INNER JOIN
        t2o
ON      t2o.t1_id = t1.id
        AND t2o.rn <= 3
WITH    q AS
        (
        SELECT  *, ROW_NUMBER() OVER (ORDER BY id) AS rn
        FROM    master
        ),
        t AS 
        (
        SELECT  1 AS id
        UNION ALL
        SELECT  2
        )
SELECT  *
FROM    t
JOIN    q
ON      q.rn <= t.id
WITH    t AS 
        (
        SELECT  1 AS id
        UNION ALL
        SELECT  2
        )
SELECT  *
FROM    t
CROSS APPLY
        (
        SELECT  TOP (t.id) m.*
        FROM    master m
        ORDER BY
                id
        ) q
select F.* from sys.objects O  
inner join dbo.myTableFun(O.name) F   
on F.schema_id= O.schema_id
select F.* from sys.objects O  
cross apply ( select * from dbo.myTableFun(O.name) ) F  
where F.schema_id= O.schema_id
select F.* from sys.objects O  
cross apply dbo.myTableFun(O.name) F
where F.schema_id= O.schema_id
x------x--------------------x
| Id   |        Name        |
x------x--------------------x
|  1   |          A         |
|  2   |          B         |
|  3   |          C         |
x------x--------------------x
x------x--------------------x-------x
| Id   |      PERIOD        |   QTY |
x------x--------------------x-------x
|  1   |   2014-01-13       |   10  |
|  1   |   2014-01-11       |   15  |
|  1   |   2014-01-12       |   20  |
|  2   |   2014-01-06       |   30  |
|  2   |   2014-01-08       |   40  |
x------x--------------------x-------x
SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
INNER JOIN
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D      
    ORDER BY CAST(PERIOD AS DATE)DESC
)D
ON M.ID=D.ID
x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
x------x---------x--------------x-------x
SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
CROSS APPLY
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D  
    WHERE M.ID=D.ID
    ORDER BY CAST(PERIOD AS DATE)DESC
)D
x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-08   |  40   |
|   2  |   B     | 2014-01-06   |  30   |
x------x---------x--------------x-------x
SELECT M.ID,M.NAME,C.PERIOD,C.QTY
FROM MASTER M
CROSS APPLY dbo.FnGetQty(M.ID) C
CREATE FUNCTION FnGetQty 
(   
    @Id INT 
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT ID,PERIOD,QTY 
    FROM DETAILS
    WHERE ID=@Id
)
x------x---------x--------------x-------x
|  Id  |   Name  |   PERIOD     |  QTY  |
x------x---------x--------------x-------x
|   1  |   A     | 2014-01-13   |  10   |
|   1  |   A     | 2014-01-11   |  15   |
|   1  |   A     | 2014-01-12   |  20   |
|   2  |   B     | 2014-01-06   |  30   |
|   2  |   B     | 2014-01-08   |  40   |
x------x---------x--------------x-------x
x------x-------------x--------------x
|  Id  |   FROMDATE  |   TODATE     |
x------x-------------x--------------x
|   1  |  2014-01-11 | 2014-01-13   | 
|   1  |  2014-02-23 | 2014-02-27   | 
|   2  |  2014-05-06 | 2014-05-30   | 
|   3  |     NULL    |    NULL      |
x------x-------------x--------------x
SELECT DISTINCT ID,DATES
FROM MYTABLE 
CROSS APPLY(VALUES (FROMDATE),(TODATE))
COLUMNNAMES(DATES)
x------x-------------x
  | Id   |    DATES    |
  x------x-------------x
  |  1   |  2014-01-11 |
  |  1   |  2014-01-13 |
  |  1   |  2014-02-23 |
  |  1   |  2014-02-27 |
  |  2   |  2014-05-06 |
  |  2   |  2014-05-30 | 
  |  3   |    NULL     | 
  x------x-------------x

在我看来,CROSS APPLY 在处理复杂 / 嵌套查询中的计算字段时可以填补一定的空白,并使它们更简单易读。

举个简单的例子:您有一个 DoB,并且您想要显示多个与年龄相关的字段,这些字段也将依赖于其他数据源(例如,就业),例如 Age,AgeGroup,AgeAtHiring,MinimumRetirementDate 等,供最终用户应用程序使用(例如,Excel PivotTables)。

选项是有限的,很少是优雅的:

  • JOIN 子查询不能基于父查询中的数据在数据集中引入新值(它必须独立存在)。

  • UDF 整洁,但速度较慢,因为它们倾向于阻止并行操作。作为一个单独的实体可以是一件好事(更少的代码)也可以是一件坏事(代码在哪里)。

  • 连接表。有时它们可以工作,但是很快您就可以与大量 UNION 一起加入子查询。大混乱。

  • 假设您的计算不需要在主查询过程中获得的数据,则可以创建另一个视图。

  • 中间表。是的... 通常可以正常工作,并且通常是一个不错的选择,因为它们可以被索引并且速度很快,但是由于 UPDATE 语句不平行并且不允许级联公式(重用结果)来更新内部的多个字段,因此性能也会下降相同的声明。有时,您只喜欢一口气做事。

  • 嵌套查询。是的,您可以随时在整个查询上加上括号并将其用作子查询,在子查询上您可以操作源数据和计算字段。但是您只能在丑陋之前做很多事情。很丑陋。

  • 重复代码。 3 个长(CASE ... ELSE ... END)语句的最大值是多少?那将是可读的!

    • 告诉您的客户自己计算该死的事情。

我错过了什么?可能吧,随时发表评论。但是,在这种情况下,CROSS APPLY 就像天赐之物:您只需添加一个简单的CROSS APPLY (select tbl.value + 1 as someFormula) as crossTblCROSS APPLY (select tbl.value + 1 as someFormula) as crossTbl !现在,您的新字段几乎可以像源数据中一直存在的那样准备好使用。

通过 CROSS APPLY 引入的值可以...

  • 用于创建一个或多个计算字段,而不会增加性能,复杂性或可读性
  • 像 JOIN 一样,几个后续的 CROSS APPLY 语句可以引用自己: CROSS APPLY (select crossTbl.someFormula + 1 as someMoreFormula) as crossTbl2
  • 您可以在随后的 JOIN 条件中使用 CROSS APPLY 引入的值
  • 作为奖励,还有表值函数方面

ang,他们无能为力!

SELECT
       id as [xt_id]
      ,xmlfield.value('(/root/@attribute)[1]', 'varchar(50)') root_attribute_value
  ,node_attribute_value = [some_node].value('@value', 'int')
  ,lt.lt_name   
FROM dbo.table_with_xml xt
CROSS APPLY xmlfield.nodes('/root/subnode1/some_node') as g ([some_node])
LEFT OUTER JOIN dbo.lookup_table lt
ON [some_node].value('@value', 'int') = lt.lt_id
xt_id root_attribute_value node_attribute_value lt_name
----------------------------------------------------------------------
1     test1            1                    Benefits
1     test1            4                    FINRPTCOMPANY
SELECT *
FROM Customer
CROSS APPLY (
  SELECT TOP 1 *
  FROM Order
  WHERE Order.CustomerId = Customer.CustomerId
  ORDER BY OrderDate DESC
) T
select * from person p where
p.companyId in(select c.companyId from company c where c.companyname like '%yyy%')
select P.*,T.CompanyName
from Person p
cross apply (
    select *
    from Company C
    where p.companyid = c.companyId and c.CompanyName like '%yyy%'
) T

我想应该是可读性;)

对于那些要告诉他们正在使用 UDF 的人来说,CROSS APPLY 在某种程度上将是唯一的,它将应用于左侧表格的每一行。

当然,在其他限制条件下,使用 CROSS APPLY 比使用其他好友在上面发布的 JOIN 更好。

CREATE FUNCTION dbo.fn_GetAllEmployeeOfADepartment(@DeptID AS INT)  
RETURNS TABLE 
AS 
RETURN 
   ( 
   SELECT * FROM Employee E 
   WHERE E.DepartmentID = @DeptID 
   ) 
GO 
SELECT * FROM Department D 
CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)
Create PROCEDURE [dbo].[Message_FindHighestMatches]

-- Declare the Topical Neighborhood
@TopicalNeighborhood nchar(255)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

Create table  #temp
(
    MessageID         int,
    Subjects          nchar(255),
    SubjectsCount    int
)

Insert into #temp Select MessageID, Subjects, SubjectsCount From Message

Select Top 20 MessageID, Subjects, SubjectsCount,
    (t.cnt * 100)/t3.inputvalues as MatchPercentage

From #temp 

cross apply (select count(*) as cnt from dbo.Split(Subjects,',') as t1
             join dbo.Split(@TopicalNeighborhood,',') as t2
             on t1.value = t2.value) as t
cross apply (select count(*) as inputValues from dbo.Split(@TopicalNeighborhood,',')) as t3

Order By MatchPercentage desc

drop table #temp