[sql] 내부 조인에 언제 교차 적용을 사용해야합니까?

두 개의 테이블이 있다고 가정하십시오.

마스터 테이블

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

우리는 교체 할 필요가 많은 상황이있다 INNER JOIN와 함께 CROSS APPLY.

1. TOP n결과에 따라 두 테이블을 조인

우리가 선택해야 할 경우 고려 Id및 Name에서 Master각 마지막 두 날짜 Id에서 Details table.

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

• SQL FIDDLE

위 쿼리는 다음과 같은 결과를 생성합니다.

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

마지막 두 날짜의 마지막 두 날짜에 대한 결과를 생성 Id한 다음의 외부 쿼리에서만 이러한 레코드를 조인했습니다 Id. 이것은 Ids1과 2를 모두 반환해야 하지만 1에는 마지막 두 날짜가 있기 때문에 1 만 반환했습니다. 이를 위해서는을 사용해야 CROSS APPLY합니다.

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

• SQL FIDDLE

다음 결과를 형성합니다.

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

작동 방식은 다음과 같습니다. 내부 쿼리 CROSS APPLY는 외부 테이블을 참조 INNER JOIN할 수 있습니다.이 작업을 수행 할 수 없습니다 (컴파일 오류가 발생 함). 마지막 두 날짜를 찾을 때, 내부에서 수행되는 접합 CROSS APPLY, 즉 WHERE M.ID=D.ID.

2. INNER JOIN기능을 사용하여 기능이 필요한 경우 .

CROSS APPLYtable과 a INNER JOIN에서 결과를 얻어야 할 때를 대신하여 사용할 수 있습니다 .Masterfunction

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
)

• SQL FIDDLE

다음 결과를 생성했습니다

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

크로스 적용의 추가 이점

APPLY의 대체품으로 사용할 수 있습니다 UNPIVOT. 하나 CROSS APPLY또는 OUTER APPLY교환 할 수있는, 여기에 사용할 수 있습니다.

아래 표 ( MYTABLE) 가 있다고 가정하십시오 .

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)

• SQL FIDDLE

결과를 가져옵니다

  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

답변

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