SQL mit Unterabfrage in gleicher Tabelle

Mitglied: GBN-WS2

GBN-WS2 (Level 1) - Jetzt verbinden

Jun 10, 2021, aktualisiert Jun 11, 2021, 605 Aufrufe, 13 Kommentare

Folgender Fall liegt vor. Es gibt eine Tabelle mit den Spalten:
- Auftrag
- Kürzel
- Licht
- Farbe
- Status.

In die Tabelle kommen jetzt Werte, die Messungen betreffen. Teile werden vor Bearbeitung gemessen und danach. Beide Messungen kommen in eine Tabell. Das heißt, es gibt für das gleiche Teil (über Auftrag und Kürzel eindeutig identifizierbar) jeweils 2 Einträge in der Tabelle. Diese werden über den Status gekennzeichnet (1 für vorher und 2 für nachher).
Für die Begutachtung der Bearbeitung sollen die 2 über Auftrag und Kürzel zusammengehörenden Datensätze in einer neuen Tabelle dargestellt werden. Dazu versuche ich im SQL Server Management Studio eine User Definde Function zu erstellen, denen ich die Parameter für den Auftrag und das Kürzel übergebe und die dann eine Tabelle zurückgibt. Leider führt bei mir dem Umweg über die UDF, weil ich den Fall nicht mit einem SQL Statement hinbekommen habe. Die Unterabfragen auf den 2. dazugehörigen Datensatz kontte ich nicht zusammenbasteln, da die Übergabeparameter für die WHERE-Klausel sich nicht auf den Datensatz der Abfrage beziehen konnten.

Beispiel Tabelle jetzt:
1000; A; 11; 2;1 (für vor der Bearbeitung)
1000; A; 9; 3; 2 (nach der Bearbeitung)

Inhalt der der gewünschen Zieltabelle sollte sein:
1000; A; 11; 2; 9; 3
Vielleich noch eine Spalte am Ende für den Anteil vom Licht vor und nach der Bearbeitung (9 zu 11 = 81,8%)

Derzeit bin ich so weit, dass die UDF wie folgt aussieht:

CREATE FUNCTION [dbo].[fnLichtwerte_Klar_B0] (@BID Int, @SANr Int, @EANr Int)
RETURNS @tblLichtwerte TABLE
(Auftrag char(24),
Kuerzel char(1),
Datum_K smalldatetime,
P_K real,
L_K real,
x_K real,
y_K real,
Datum_B smalldatetime,
P_B real,
L_B real,
x_B real,
y_B real,
Absorption real)
AS
BEGIN
DECLARE @Auftrag char(24)
DECLARE @Datum_K datetime2, @Datum_B datetime
DECLARE @P_K real, @L_K real, @x_K real, @y_K real
DECLARE @P_B real, @L_B real, @x_B real, @y_B real
DECLARE @KB int, @Kuerzel char(1), @B_ID int, @verw int
DECLARE @Absorption real



DECLARE Messwerte_Klar CURSOR LOCAL STATIC

-- Schleife durch die Klarmessungen
FOR
SELECT Auftrag.Produktion_Vorgangsnummer,
Messwerte.Produktion_Kuerzel,
Messung.Produktion_Datum,
Messwerte.Produktion_Leistung,
Messwerte.Produktion_Korr_Lichtstrom,
Messwerte.Produktion_Korr_Farbort_X,
Messwerte.Produktion_Korr_Farbort_Y,
'1900-01-01', 0, 0, 0, 0, 0
FROM dbo.tblProduktion_BrennerMessung AS Messwerte
INNER JOIN dbo.tblProduktion_Messung AS Messung ON Messwerte.Produktion_Verweis_Messung_ID = Messung.Produktion_Messung_ID
INNER JOIN dbo.tblProduktion_Messauftrag AS Auftrag ON Messung.Produktion_Verweis_Messauftrag_ID = Auftrag.Produktion_Messauftrag_ID
WHERE (Auftrag.Produktion_Verweis_Brennertyp_ID = @BID) AND Messwerte.Produktion_MessungVerworfen = 0 AND Messung.Produktion_Messungstyp_Klar_Beschichtet = 1 AND (Auftrag.Produktion_Vorgangsnummer BETWEEN @SANr AND @EANr);

OPEN Messwerte_Klar

FETCH NEXT FROM Messwerte_Klar INTO @Auftrag, @Kuerzel, @Datum_K, @P_K, @L_K, @x_K, @y_K, @Datum_B, @P_B, @L_B, @x_B, @y_B, @Absorption

WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN
INSERT INTO @tblLichtwerte
VALUES (@Auftrag, @Kuerzel, @Datum_K, @P_K, @L_K, @x_K, @y_K, @Datum_B, @P_B, @L_B, @x_B, @y_B, @Absorption);
END
FETCH NEXT FROM Messwerte_Klar INTO @Auftrag, @Kuerzel, @Datum_K, @P_K, @L_K, @x_K, @y_K, @Datum_B, @P_B, @L_B, @x_B, @y_B, @Absorption
END

CLOSE Messwerte_Klar
DEALLOCATE Messwerte_Klar
RETURN
END

Leider kenne ich den Weg nicht, wie ich die Nullen für den Nachbearbeitungszustand durch Unterabfragen ersetzen kann.
DIE UDF wäre nur meine 2t-liebste Lösung. Am besten wäre es mit einem SQL-Statement.

Bin gespannt, ob mir jemand helfen kann.

Danke
Mitglied: GBN-WS2
Jun 10, 2021 um 10:56 Uhr
Es muss "SQL mit Unterabfrage" heißen
Bitte warten ..
Mitglied: ukulele-7
Jun 10, 2021, aktualisiert um 11:02 Uhr
Klingt für mich nach viel zu kompliziert gedacht. Ist doch einfach ein Join in einem Select (den man dann auch als View verfügbar machen kann), in etwa so:

PS: Ich gehe davon aus das sichergestellt ist, das Auftrag + Kürzel immer eindeutig ist und es immer mind. 1x Status = 1 gibt und 0 bis 1 mal Status 2.
Bitte warten ..
Mitglied: GBN-WS2
Jun 10, 2021 um 11:08 Uhr
Interessanter Ansatz. Leider sind mit nur die Inner Joins so richtig bekannt. Ich probieren das mal mit dem Left Join. Danke vorerst.
Bitte warten ..
Mitglied: GBN-WS2
Jun 10, 2021 um 11:20 Uhr
Hab etwas genauer hingesehen. Die Daten über den Status sowie andere Informationen kommen aus insgesamt 3 Tabellen, die jetzt schon innergejoint sind:
SELECT Auftrag.Produktion_Vorgangsnummer,
Messwerte.Produktion_Kuerzel,
Messung.Produktion_Datum,
Messwerte.Produktion_Leistung,
Messwerte.Produktion_Korr_Lichtstrom,
Messwerte.Produktion_Korr_Farbort_X,
Messwerte.Produktion_Korr_Farbort_Y,
'1900-01-01', 0, 0, 0, 0, 0
FROM dbo.tblProduktion_BrennerMessung AS Messwerte
INNER JOIN dbo.tblProduktion_Messung AS Messung ON Messwerte.Produktion_Verweis_Messung_ID = Messung.Produktion_Messung_ID
INNER JOIN dbo.tblProduktion_Messauftrag AS Auftrag ON Messung.Produktion_Verweis_Messauftrag_ID = Auftrag.Produktion_Messauftrag_ID
WHERE (Auftrag.Produktion_Verweis_Brennertyp_ID = @BID) AND Messwerte.Produktion_MessungVerworfen = 0 AND Messung.Produktion_Messungstyp_Klar_Beschichtet = 1 AND (Auftrag.Produktion_Vorgangsnummer BETWEEN @SANr AND @EANr);

Wie kann ich hier zusätzlich den LEFT JOIN mitverwenden?
Bitte warten ..
Mitglied: em-pie
Jun 10, 2021, aktualisiert um 12:00 Uhr
Moin,

eine Bitte: verwende unbedingt die code-Tags (Ohne die Leerzeichen): < code > MEIN CODE < /code >
Die kannst du auch nachträglich noch in deine Posts einbinden.

Ein LEFT JOIN (oder auch Right oder Outer Join) nutzt man exakt wie ein INNER JOIN, nur das Ergebnis ist jedes mal "anders".

Hier ist das ganz gut erklärt, insbesondere auch durch die Grafik:
https://stackoverflow.com/questions/406294/left-join-vs-left-outer-join- ...

Gruß
em-pie
Bitte warten ..
Mitglied: ukulele-7
LÖSUNG Jun 10, 2021, aktualisiert um 14:03 Uhr
LEFT JOIN ist genauso wie der INNER JOIN absolute Basisfunktionalität. Dabei enthält das Ergebnis auch Datensätze der ersten Tabelle (die im FROM-Teil) zu denen keine passenden Datensätze in der 2ten Tabelle gefunden wurden. Das dürfte in deinem Fall vor kommen wenn Messung 1 statt gefunden hat aber Messung 2 noch aus steht.

Was die "Ursprungstabelle" angeht: Man macht es sich gern einfach und in deinem Szenario wäre das wohl auch übersichtlicher. Daher fasse ich dein Query gar nicht an sondern packe das entweder in eine vorgelagerte View oder in eine WITH-clause. Der Vorteil ist dabei ganz klar das man gar nicht das Query verstehen muss um es mit sich selbst zu joinen sondern als eine Ausgangstabelle behandelt. Nachteil ist wohl das es anders theoretisch performanter sein kann.
Deine Eingangs erwähnte Spalte Status findet sich allerdings noch nicht in deinem Ausgangsselect wieder. Das * sollte dann auch durch explizite Spaltennamen ersetzt werden.
Bitte warten ..
Mitglied: GBN-WS2
Jun 11, 2021 um 11:35 Uhr
Danke bis hier her. Ich hab das Status-Feld noch eingebaut und etwas aufgeräumt. Die Abfrage geht ohne Fehlermeldung und liefert Daten. Jetzt muss ich nur noch sehen, dass ich das Ganze in eine UDF auf dem SQL-SERVER hinterlegt bekomme, die eine Tabelle ausgibt und der ich die Parameter BID, SANr und SENr übergeben kann.
Bitte warten ..
Mitglied: ukulele-7
Jun 11, 2021 um 11:53 Uhr
Wie wäre es mit einer View anstelle einer UDF? https://www.w3schools.com/SQL/sql_view.asp
Bitte warten ..
Mitglied: GBN-WS2
Jun 11, 2021 um 12:22 Uhr
Ich denke, an eine View kann man keine Parameter übergeben. Ich muss aber die BID, SANr und SENr dynamisch aus einem Frontend-Formular auslesen und übergeben.
Bitte warten ..
Mitglied: ukulele-7
LÖSUNG Jun 11, 2021 um 12:58 Uhr
Die View fragst du mit einem Select ab, der Select beinhaltet im WHERE-Teil die Parameter...
Bitte warten ..
Mitglied: GrueneSosseMitSpeck
Jun 11, 2021 um 18:42 Uhr
auch wenns nach schwarzer Magie klingt - schreib das als verschachtelte Select Statements hin, alle gängigen SQL Server optimieren das später so daß ein Join im Abfrageplan auftaucht. Weil ansosnten die clientseitig definierten Joins per SQL tlw die serverseitige Optimierung (speziell beim MS SQL Server) manchmal in die Irre treibt und der dann extrem lange für triviale Abfragen braucht.
Bitte warten ..
Mitglied: GBN-WS2
Jun 14, 2021, aktualisiert um 15:38 Uhr


Bitte warten ..
Mitglied: em-pie
Jun 14, 2021 um 15:22 Uhr
Nutze, wie oben schon geschrieben, doch bitte die Code-Tags.
Das kann hier doch keiner ordentlich lesen!
Bitte warten ..
Heiß diskutierte Inhalte
Internet
Sehr große Dateien über das Internet versenden
solved pd.edv1 day agoQuestionInternet113 Comments

Hallo, ich muss immer wieder große Daten über das Internet übertragen - da werden viele am WeTransfer & Co. denken aber mit den winzigen ...

Hardware
MiniPC Empfehlung AliExpress
winlin1 day agoGeneralHardware23 Comments

Hallo Zusammen, Hat schon mal jemand hier einen Mini pc bei AliExpress gekauft und kann einen empfehlen??? Suche etwas wo ich 2vms problemlos virtualisieren ...

Windows 10
Custom Windows-10-ISO bauen (in kontinuierlicher Verbesserung) - Ausgabe 2021
beidermachtvongreyscull1 day agoTutorialWindows 102 Comments

Editorial kleines Vorwort Ich hoffe, ich habe hier eine für den ein oder anderen Kollegen interessante Lösung zusammengestellt. Alles, was Ihr hier lest, ist ...

Microsoft
User Aktivitäten
Roadmax1 day agoQuestionMicrosoft10 Comments

Hallo Zusammen, ich möchte gerne automatisiert auf jedem Windows 10 PC im Netzwerk prüfen lassen, was der User dort live treibt. Konkret geht es ...

Cloud services
Server mieten - wo?
ZZaaiiggaa10 hours agoQuestionCloud services9 Comments

Hallo zusammen, suche einen Windows Server für SQL zum mieten, mit mindestens: 256GB SSD 32GB Ram Welche Anbieter eignen sich am besten? Und muss ...

Notebook & Accessories
Lenovo Dockingstation - Kompatibilität?
solved Visucius1 day agoQuestionNotebook & Accessories10 Comments

Guten Morgen, wir habens ja gerade mit antiquierter Technik ;-) Vor mir steht ein T440s Lenovo-Laptop, der nen frisches Windows 7 ähh Quatsch Windows ...

LAN, WAN, Wireless
Suche fuer unser Reihenmittelhaus eine "gute" WLAN-Abdeckung
homenet1 day agoQuestionLAN, WAN, Wireless3 Comments

Hallo, suche fuer unser Reihenmittelhaus eine "gute" WLAN-Abdeckung. Benoetigt fuer: Smartphones, Laptops, Radio - max. ca. 10 Geraete gleichzeitig, ueblicherweise ca. 4 Geraete dauernd ...

Windows 10
Dokumentenmanagement-System für den Heimgebrauch
Pineapple2711 hours agoQuestionWindows 104 Comments

Hallo zusammen, kennt jemand ein gutes Dokumentenmanagement-System für den Heimgebrauch und hat eventuell auch schon Erfahrungen damit gemacht? Da ich es rein privat nutzen ...