Benutzer mit den meisten Antworten
Cursor Als View erstellen

Frage
-
Hallo,
ich hoffe Ihr könnt mir mit euren geballten Fachwissen weiterhelfen. Ich habe folgendes Problem. Ich möchte in einer View die letzten drei Rechnungszeilen pro Artikel und pro Kunde darstellen. Ich habe dies schonmal mittels Cursor umgesetzt. Hier der entsprechende Code:
CREATE TABLE #TEMP ( [Belegnummer] int, [Zeilennummer] int, [Kundennummer] int, [Artikelnummer] int ); DECLARE @Artikel varchar(20) DECLARE @Kunde varchar(20) DECLARE Kunde CURSOR FOR SELECT [ID] FROM [dbo].[Kundenzabelle]; OPEN Kunde; FETCH NEXT FROM Kunde INTO @Kunde; WHILE @@FETCH_STATUS = 0 BEGIN DECLARE Artikel CURSOR FOR SELECT [ID] FROM [dbo].[Artikeltabelle]; OPEN Artikel; FETCH NEXT FROM Artikel INTO @Artikel; WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO #TEMP SELECT TOP 3 [Rechnungsnummer], [Zeilennummer], [KundenID], [ArtikelID] FROM [dbo].[Rechnungszeilen] WHERE [KundenID] = @Kunde AND [ArtikelID] = @Artikel ORDER BY [Buchungsdatum] FETCH NEXT FROM Artikel INTO @Artikel; END CLOSE Artikel; DEALLOCATE Artikel; FETCH NEXT FROM Kunde INTO @Kunde; END; CLOSE Kunde; DEALLOCATE Kunde; SELECT * FROM #TEMP DROP TABLE #TEMP
Allerdings kann man einen Cursor nicht zur Erstellung einer View nutzen. Zumindest ist mir kein Weg bekannt. Daher meine Frage, kann an den Code noch anders darstellen. Vielleicht mit Hilfe eine CTE oder ähnlichen. Vielen Dank für Eure Mühen.
Antworten
-
Hallo Gary,
so sollte es gehen,
Gruss Uli
WITH CTE_Top3_artikel AS ( SELECT [Rechnungsnummer], [Zeilennummer], [KundenID], [ArtikelID], ROW_NUMBER() OVER (PARTITION BY KundenID, ArtikelID ORDER BY Buchungsdatum DESC) AS 'number'FROM [dbo].[Rechnungszeilen] ) SELECT * FROM CTE_Top3_artikel WHERE number<4 ORDER BY 3,4
- Als Antwort markiert Gary Hawk Sonntag, 15. Juli 2012 20:20
-
Hallo Gary,
zunächst einmal, Du sortierst in Deinem Script nach Datum auf aufsteigend und erhälst Du die ersten 3 Rechnungen, nicht die letzten 3; Du müsstest absteigend (DESC) sortieren. Und Cursor sollte man immer möglichst vermeiden, wenn es geht.
Das kann man in der Tat einfach mit einer CTE und der ROW_NUMBER() Funktion erledigt. Hier partitionierst Du nach Kunde und lässt nach Datum absteigend sortieren, so erhälst Du je Kunde eine fortlaufende Nummer; auf die Filterst Du dann die ersten 3 heraus.
Am Beispiel AdventureWorsk Sales sieht es so aus.
CREATE VIEW dbo.Last3Sales AS WITH cust AS (SELECT AccountNumber ,OrderDate ,ROW_NUMBER() OVER (PARTITION BY AccountNumber ORDER BY OrderDate DESC ,SalesOrderID DESC) AS RowNum FROM Sales.SalesOrderHeader) SELECT RowNum, AccountNumber FROM cust WHERE RowNum <= 3;
Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing- Bearbeitet Olaf HelperMVP Sonntag, 15. Juli 2012 08:01
- Als Antwort markiert Gary Hawk Sonntag, 15. Juli 2012 20:20
-
Hallo,
das lässt sich mit einer Abfrage erledigen. Eins zu eins wäre z. B.:
SELECT rz.Rechnungsnummer, rz.Zeilennummer, ka.KundenID, ka.ArtikelID FROM (SELECT k.ID AS KundenID, a.ID AS ArtikelID FROM dbo.Kundenzabelle AS k CROSS JOIN dbo.Artikeltabelle AS a) AS ka CROSS APPLY(SELECT TOP(3) r.Rechnungsnummer, r.Zeilennummer FROM [dbo].[Rechnungszeilen] WHERE r.KundenID = ka.KundenID AND r.ArtikelID = ka.ArtikelID ORDER BY Buchungsdatum) AS rz
Siehe Verwenden von APPLY falls Dir das Konstrukt noch nicht bekannt sein sollte.
Allerdings würde ich das ebensowenig wie Deinen Cursor auf eine größere Rechungstabelle loslassen.
Der CROSS JOIN - auch durch den Cursor implementiert - ist eine absolute Spaßbremse.Wenn es zu Deinen Rechnungszeilen Rechnungsköpfe oder ähnliches gibt, wie zu vermuten,
so sollte man diese als Basis für die Abfrage verwenden.
Das CROSS APPLY hätte damit eine reduzierte Ausgangsbasis,
und der SQL Server mit Glück ;-) einige Indizes, die nutzen kann.Und wenn die spätere Verwendung nicht darin besteht, alle Zeilen aufzulisten,
sondern nur einzelne Kunden bzw. Artikel, so könnte eine Tabellenwertfunktion besser als eine Sicht sein.Gruß Elmar
- Als Antwort markiert Gary Hawk Sonntag, 15. Juli 2012 20:20
Alle Antworten
-
Hallo Gary,
so sollte es gehen,
Gruss Uli
WITH CTE_Top3_artikel AS ( SELECT [Rechnungsnummer], [Zeilennummer], [KundenID], [ArtikelID], ROW_NUMBER() OVER (PARTITION BY KundenID, ArtikelID ORDER BY Buchungsdatum DESC) AS 'number'FROM [dbo].[Rechnungszeilen] ) SELECT * FROM CTE_Top3_artikel WHERE number<4 ORDER BY 3,4
- Als Antwort markiert Gary Hawk Sonntag, 15. Juli 2012 20:20
-
Hallo Gary,
zunächst einmal, Du sortierst in Deinem Script nach Datum auf aufsteigend und erhälst Du die ersten 3 Rechnungen, nicht die letzten 3; Du müsstest absteigend (DESC) sortieren. Und Cursor sollte man immer möglichst vermeiden, wenn es geht.
Das kann man in der Tat einfach mit einer CTE und der ROW_NUMBER() Funktion erledigt. Hier partitionierst Du nach Kunde und lässt nach Datum absteigend sortieren, so erhälst Du je Kunde eine fortlaufende Nummer; auf die Filterst Du dann die ersten 3 heraus.
Am Beispiel AdventureWorsk Sales sieht es so aus.
CREATE VIEW dbo.Last3Sales AS WITH cust AS (SELECT AccountNumber ,OrderDate ,ROW_NUMBER() OVER (PARTITION BY AccountNumber ORDER BY OrderDate DESC ,SalesOrderID DESC) AS RowNum FROM Sales.SalesOrderHeader) SELECT RowNum, AccountNumber FROM cust WHERE RowNum <= 3;
Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing- Bearbeitet Olaf HelperMVP Sonntag, 15. Juli 2012 08:01
- Als Antwort markiert Gary Hawk Sonntag, 15. Juli 2012 20:20
-
Hallo,
das lässt sich mit einer Abfrage erledigen. Eins zu eins wäre z. B.:
SELECT rz.Rechnungsnummer, rz.Zeilennummer, ka.KundenID, ka.ArtikelID FROM (SELECT k.ID AS KundenID, a.ID AS ArtikelID FROM dbo.Kundenzabelle AS k CROSS JOIN dbo.Artikeltabelle AS a) AS ka CROSS APPLY(SELECT TOP(3) r.Rechnungsnummer, r.Zeilennummer FROM [dbo].[Rechnungszeilen] WHERE r.KundenID = ka.KundenID AND r.ArtikelID = ka.ArtikelID ORDER BY Buchungsdatum) AS rz
Siehe Verwenden von APPLY falls Dir das Konstrukt noch nicht bekannt sein sollte.
Allerdings würde ich das ebensowenig wie Deinen Cursor auf eine größere Rechungstabelle loslassen.
Der CROSS JOIN - auch durch den Cursor implementiert - ist eine absolute Spaßbremse.Wenn es zu Deinen Rechnungszeilen Rechnungsköpfe oder ähnliches gibt, wie zu vermuten,
so sollte man diese als Basis für die Abfrage verwenden.
Das CROSS APPLY hätte damit eine reduzierte Ausgangsbasis,
und der SQL Server mit Glück ;-) einige Indizes, die nutzen kann.Und wenn die spätere Verwendung nicht darin besteht, alle Zeilen aufzulisten,
sondern nur einzelne Kunden bzw. Artikel, so könnte eine Tabellenwertfunktion besser als eine Sicht sein.Gruß Elmar
- Als Antwort markiert Gary Hawk Sonntag, 15. Juli 2012 20:20