{"id":478,"date":"2019-11-05T15:38:36","date_gmt":"2019-11-05T15:38:36","guid":{"rendered":"http:\/\/flace.de\/?p=478"},"modified":"2019-11-11T13:29:35","modified_gmt":"2019-11-11T13:29:35","slug":"sql-beispiel-um-aus-einer-1n-tabelle-die-jeweils-hoechste-nr-der-detailtabelle-zu-ziehen","status":"publish","type":"post","link":"https:\/\/flace.de\/?p=478","title":{"rendered":"SQL Beispiel um aus einer 1:N Tabelle die jeweils h\u00f6chste Nr der Detailtabelle zu ziehen"},"content":{"rendered":"<p>&#8211;Holt sich die Netto Angebotssumme aus dem letzten (h\u00f6chste Angebotsnr) zum Projekt<\/p>\n<p>select ANR, BRUTTOWERT-USTWERT as Netto, PROJEKTNR from<br \/>\n(<br \/>\nSELECT MAX(PA.ANGEBOTSNR) as ANR, PA.ProjNr FROM (select Angebotsnr, Projnr from Angebot A inner JOIN Projekt P ON P.ProjNr = A.PROJEKTNR) as PA GROUP BY PA.ProjNr<br \/>\n) as MAXA inner join Angebot A on A.ANGEBOTSNR = MAXA.ANR<\/p>\n<p><span style=\"color: #0000ff;\">Alternativ k\u00f6nnte man das jetzt \u00fcber eine Hilfstabelle (projekte) in ein gesondertes Datenfeld schreiben.<\/span><\/p>\n<p>declare @projekte table (ANGEBOTSNR nvarchar(max), PROJEKTNR nvarchar(max), netto float)<br \/>\ninsert into @projekte<br \/>\nselect ANR, PROJEKTNR, BRUTTOWERT-USTWERT as Netto from<br \/>\n(<br \/>\nSELECT MAX(PA.ANGEBOTSNR) as ANR, PA.ProjNr FROM (select Angebotsnr, Projnr from Angebot A inner JOIN Projekt P ON P.ProjNr = A.PROJEKTNR) as PA GROUP BY PA.ProjNr<br \/>\n) as MAXA inner join Angebot A on A.ANGEBOTSNR = MAXA.ANR<\/p>\n<p>Update Projekt set Benutzer9 = PR.netto from Projekt P inner join @projekte PR on PR.PROJEKTNR = P.ProjNr<\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8211;Holt sich die Netto Angebotssumme aus dem letzten (h\u00f6chste Angebotsnr) zum Projekt select ANR, BRUTTOWERT-USTWERT as Netto, PROJEKTNR from ( SELECT MAX(PA.ANGEBOTSNR) as ANR, PA.ProjNr FROM (select Angebotsnr, Projnr from Angebot A inner JOIN Projekt P ON P.ProjNr = A.PROJEKTNR) as PA GROUP BY PA.ProjNr ) as MAXA inner join Angebot A on A.ANGEBOTSNR = &hellip; <\/p>\n<p class=\"link-more\"><a href=\"https:\/\/flace.de\/?p=478\" class=\"more-link\"><span class=\"screen-reader-text\">\u201eSQL Beispiel um aus einer 1:N Tabelle die jeweils h\u00f6chste Nr der Detailtabelle zu ziehen\u201c<\/span> weiterlesen<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,16],"tags":[115,116,117,31],"class_list":["post-478","post","type-post","status-publish","format-standard","hentry","category-allgemein","category-sql","tag-angebot","tag-angebotssumme","tag-max","tag-sql"],"_links":{"self":[{"href":"https:\/\/flace.de\/index.php?rest_route=\/wp\/v2\/posts\/478","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/flace.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/flace.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/flace.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/flace.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=478"}],"version-history":[{"count":3,"href":"https:\/\/flace.de\/index.php?rest_route=\/wp\/v2\/posts\/478\/revisions"}],"predecessor-version":[{"id":487,"href":"https:\/\/flace.de\/index.php?rest_route=\/wp\/v2\/posts\/478\/revisions\/487"}],"wp:attachment":[{"href":"https:\/\/flace.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=478"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/flace.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=478"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/flace.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=478"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}