Beispiel für ein echtes Leben, wenn OUTER / CROSS APPLY in SQL verwendet werden soll

Schaue ich CROSS /OUTER APPLY mit einem Kollegen und wir kämpfen, um zu finden, Beispiele aus dem wirklichen Leben, wo Sie zu nutzen.

Hab ich verbrachte ziemlich viel Zeit mit der Suche an Wann sollte ich verwenden, Cross Apply-über Inner Join? und googeln, aber die wichtigste (einzige) Beispiel scheint ziemlich Bizarr (mit der Zeilenanzahl einer Tabelle bestimmen, wie viele Zeilen wählen Sie aus einer anderen Tabelle).

Dachte ich, dass dieses Szenario profitieren können OUTER APPLY:

Contacts-Tabelle (enthält 1 Eintrag für jeden Kontakt)
Kommunikation Tabelle Einträge (enthalten kann, n Telefon, fax, E-Mail-fro pro Kontakt)

Aber die Verwendung von Unterabfragen, häufige Tabellenausdrücke, OUTER JOIN mit RANK() und OUTER APPLY alle scheinen zu führen ebenso. Ich vermute, dies bedeutet, das Szenario ist nicht anwendbar auf APPLY.

Bitte teilen Sie einige Beispiele und erläutern Sie die Funktion!

InformationsquelleAutor der Frage Lee Tickett | 2012-02-14

132

Einige Anwendungen für APPLY sind...

1) Top N per group Abfragen (kann es effizienter sein, für einige kardinalitäten)

SELECT pr.name,
       pa.name
FROM   sys.procedures pr
       OUTER APPLY (SELECT TOP 2 *
                    FROM   sys.parameters pa
                    WHERE  pa.object_id = pr.object_id
                    ORDER  BY pr.name) pa
ORDER  BY pr.name,
          pa.name

2) Aufruf einer Tabellenwertfunktion für jede Zeile der äußeren Abfrage

SELECT *
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)

3) Die Wiederverwendung einer Spalte alias

SELECT number,
       doubled_number,
       doubled_number_plus_one
FROM master..spt_values
CROSS APPLY (SELECT 2 * CAST(number AS BIGINT)) CA1(doubled_number)  
CROSS APPLY (SELECT doubled_number + 1) CA2(doubled_number_plus_one)

4) Unpivoting mehr als eine Gruppe von Spalten

Übernimmt 1NF gegen die Struktur einer Tabelle....

CREATE TABLE T
  (
     Id   INT PRIMARY KEY,

     Foo1 INT, Foo2 INT, Foo3 INT,
     Bar1 INT, Bar2 INT, Bar3 INT
  );

Beispiel mit 2008+ VALUES syntax.

SELECT Id,
       Foo,
       Bar
FROM   T
       CROSS APPLY (VALUES(Foo1, Bar1),
                          (Foo2, Bar2),
                          (Foo3, Bar3)) V(Foo, Bar);

2005 UNION ALL können stattdessen verwendet werden.

SELECT Id,
       Foo,
       Bar
FROM   T
       CROSS APPLY (SELECT Foo1, Bar1 
                    UNION ALL
                    SELECT Foo2, Bar2 
                    UNION ALL
                    SELECT Foo3, Bar3) V(Foo, Bar);

InformationsquelleAutor der Antwort Martin Smith

Es gibt verschiedene Situationen, wo Sie nicht vermeiden können CROSS APPLY oder OUTER APPLY.

Überlegen, Sie haben zwei Tabellen.

MASTER-TABELLE

x------x--------------------x
| Id   |        Name        |
x------x--------------------x
|  1   |          A         |
|  2   |          B         |
|  3   |          C         |
x------x--------------------x

DETAILS TABELLE

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

CROSS APPLY

Gibt es viele situation, wo wir brauchen, um zu ersetzen INNER JOIN mit CROSS APPLY.

1. Wenn wir wollen zu join 2 Tabellen auf TOP n Ergebnisse mit INNER JOIN Funktionalität

Betrachten, wenn wir auswählen müssen, um Id und Name aus Master und die letzten beiden Terminen für jede Id aus 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

Die oben stehende Abfrage generiert das folgende Ergebnis.

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

Sehen, erzeugt es Ergebnisse für die letzten beiden Termine mit den letzten beiden Datum ' s Id und dann kam diese Datensätze nur in der äußeren Abfrage auf Id die falsch ist. Um dies zu erreichen, müssen wir mit 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

und Formen, die er zu folgendem Ergebnis.

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

Hier ist die Arbeit. Die Abfrage in CROSS APPLY verweisen können, die outer-Tabelle, wo INNER JOIN dies nicht tun(wirft compile-Fehler). Bei der Suche nach den letzten zwei dates, der Beitritt erfolgt innerhalb CROSS APPLY dh WHERE M.ID=D.ID.

2. Wenn wir Sie brauchen INNER JOIN Funktionalität mit Funktionen.

CROSS APPLY können als Ersatz verwendet werden mit INNER JOIN wenn wir brauchen, um Ergebnis aus Master Tisch und ein function.

SELECT M.ID,M.NAME,C.PERIOD,C.QTY
FROM MASTER M
CROSS APPLY dbo.FnGetQty(M.ID) C

Und hier ist die Funktion

CREATE FUNCTION FnGetQty 
(   
    @Id INT 
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT ID,PERIOD,QTY 
    FROM DETAILS
    WHERE ID=@Id
)

generiert das folgende Ergebnis

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

OUTER APPLY

1. Wenn wir wollen zu join 2 Tabellen auf TOP n Ergebnisse mit LEFT JOIN Funktionalität

Betrachten, wenn wir auswählen müssen, um die Id und den Namen aus Master und die letzten beiden Terminen für jede Id aus Details Tabelle.

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
LEFT JOIN
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D  
    ORDER BY CAST(PERIOD AS DATE)DESC
)D
ON M.ID=D.ID

welche Formen das folgende Ergebnis

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     |   NULL       |  NULL |
|   3  |   C     |   NULL       |  NULL |
x------x---------x--------------x-------x

Dieser bringt falsche Ergebnisse, dh, es wird nur die Letzte zwei Termine Daten aus Details Tabelle unabhängig von Id obwohl wir gemeinsam mit Id. So die richtige Lösung ist OUTER APPLY.

SELECT M.ID,M.NAME,D.PERIOD,D.QTY
FROM MASTER M
OUTER APPLY
(
    SELECT TOP 2 ID, PERIOD,QTY 
    FROM DETAILS D  
    WHERE M.ID=D.ID
    ORDER BY CAST(PERIOD AS DATE)DESC
)D

welche Formen Sie die folgenden gewünschten Ergebnis

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   |
|   3  |   C     |   NULL       |  NULL |
x------x---------x--------------x-------x

2. Wenn wir Sie brauchen LEFT JOIN Funktionalität mit functions.

OUTER APPLY können als Ersatz verwendet werden mit LEFT JOIN wenn wir brauchen, um Ergebnis aus Master Tisch und ein function.

SELECT M.ID,M.NAME,C.PERIOD,C.QTY
FROM MASTER M
OUTER APPLY dbo.FnGetQty(M.ID) C

Und die Funktion geht hier.

CREATE FUNCTION FnGetQty 
(   
    @Id INT 
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT ID,PERIOD,QTY 
    FROM DETAILS
    WHERE ID=@Id
)

generiert das folgende Ergebnis

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   |
|   3  |   C     |   NULL       |  NULL |
x------x---------x--------------x-------x

Gemeinsames Merkmal der CROSS APPLY und OUTER APPLY

CROSS APPLY oder OUTER APPLY können sein verwendet, um behalten NULL Werte, wenn unpivoting, die austauschbar sind.

Betrachten Sie die unten stehende Tabelle

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

Wenn Sie UNPIVOT zu bringen FROMDATE UND TODATE auf eine Spalte, es beseitigt NULL Werte standardmäßig.

SELECT ID,DATES
FROM MYTABLE
UNPIVOT (DATES FOR COLS IN (FROMDATE,TODATE)) P

generiert das unten führen. Beachten Sie, dass wir verpasst haben, die Aufzeichnung der Id Anzahl 3

  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 |
  x------x-------------x

In solchen Fällen eine CROSS APPLY oder OUTER APPLY wird nützlich sein,

SELECT DISTINCT ID,DATES
FROM MYTABLE 
OUTER APPLY(VALUES (FROMDATE),(TODATE))
COLUMNNAMES(DATES)

welche Formen das folgende Ergebnis und behält Id wo Ihr Wert ist 3

  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

InformationsquelleAutor der Antwort Sarath Avanavu

Einem real-life Beispiel wäre, wenn Sie hatte einen scheduler und wollte sehen, was die letzten log-Eintrag für jede geplante task.

select t.taskName, lg.logResult, lg.lastUpdateDate
from task t
cross apply (select top 1 taskID, logResult, lastUpdateDate
             from taskLog l
             where l.taskID = t.taskID
             order by lastUpdateDate desc) lg

InformationsquelleAutor der Antwort BJury

Antwort auf die weiter oben klopfen ein Beispiel:

create table #task (taskID int identity primary key not null, taskName varchar(50) not null)
create table #log (taskID int not null, reportDate datetime not null, result varchar(50) not null, primary key(reportDate, taskId))

insert #task select 'Task 1'
insert #task select 'Task 2'
insert #task select 'Task 3'
insert #task select 'Task 4'
insert #task select 'Task 5'
insert #task select 'Task 6'

insert  #log
select  taskID, 39951 + number, 'Result text...'
from    #task
        cross join (
            select top 1000 row_number() over (order by a.id) as number from syscolumns a cross join syscolumns b cross join syscolumns c) n

Und führen Sie nun die beiden Abfragen mit einem Ausführungsplan.

select  t.taskID, t.taskName, lg.reportDate, lg.result
from    #task t
        left join (select taskID, reportDate, result, rank() over (partition by taskID order by reportDate desc) rnk from #log) lg
            on lg.taskID = t.taskID and lg.rnk = 1

select  t.taskID, t.taskName, lg.reportDate, lg.result
from    #task t
        outer apply (   select  top 1 l.*
                        from    #log l
                        where   l.taskID = t.taskID
                        order   by reportDate desc) lg

Können Sie sehen, dass die äußeren gelten die Abfrage effizienter zu gestalten. (Konnte es nicht Anhängen, das der plan ich bin ein neuer Benutzer... Doh.)

InformationsquelleAutor der Antwort BJury

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.