{"id":1920,"date":"2015-01-28T23:47:18","date_gmt":"2015-01-28T21:47:18","guid":{"rendered":"http:\/\/soci.hu\/blog\/?p=1920"},"modified":"2015-01-28T23:47:18","modified_gmt":"2015-01-28T21:47:18","slug":"sql-fejtoro-6-megoldas","status":"publish","type":"post","link":"https:\/\/soci.hu\/blog\/index.php\/2015\/01\/28\/sql-fejtoro-6-megoldas\/","title":{"rendered":"SQL fejt\u00f6r\u0151 6. megold\u00e1s"},"content":{"rendered":"<p><a href=\"http:\/\/soci.hu\/blog\/index.php\/2015\/01\/16\/sql-fejtoro-6\/\">Ebben<\/a> a feladatban meg kellett hat\u00e1rozni azokat a sorokat, amelyek \u00f6sszege kiad egy sort egy m\u00e1sik t\u00e1bl\u00e1ban. Mivel nem lehet tudni, h\u00e1ny sort kell szumm\u00e1zni, ez\u00e9rt kombin\u00e1ci\u00f3kat kellett k\u00e9pezni az elemekb\u0151l.<br \/>\nA feladat nehezebb r\u00e9sze teh\u00e1t, hogyan kell SQL-b\u0151l kombin\u00e1ci\u00f3kat k\u00e9pezni?<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\n;WITH Combinations AS\r\n(\r\n\tSELECT  &#x5B;Level]=1,\r\n\t\t\tCombinationId = CONVERT(VARCHAR(MAX),id)+',',\r\n\t\t\tSzamlaszam,\r\n\t\t\tTotalPrice = CONVERT(DECIMAL(10,2), Ar),\r\n\t\t\tLastId = Id\r\n\tFROM  Szamlatetel\r\n\r\n\tUNION ALL\r\n\r\n\tSELECT  &#x5B;Level]=&#x5B;Level]+1,\r\n\t\t\tCombinationId = CombinationId + CONVERT(VARCHAR(3),id) + ',',\r\n\t\t\tc.Szamlaszam,\r\n\t\t\tTotalPrice = CONVERT(DECIMAL(10,2),TotalPrice + Ar),\r\n\t\t\tLastId = Id\r\n\tFROM  Combinations c  \r\n\tINNER JOIN Szamlatetel i \r\n\t\t    ON  i.Szamlaszam = c.Szamlaszam AND i.ID &gt; LastId \r\n)\r\nselect * from Combinations;\r\n<\/pre>\n<p>Ennek kimenete:<\/p>\n<pre class=\"brush: plain; title: ; notranslate\" title=\"\">\r\n1\t1,\tSzamla1\t20.00\t1\r\n1\t2,\tSzamla1\t111.00\t2\r\n1\t3,\tSzamla1\t250.00\t3\r\n1\t4,\tSzamla1\t20.00\t4\r\n1\t5,\tSzamla1\t15.00\t5\r\n1\t6,\tSzamla1\t189.00\t6\r\n1\t7,\tSzamla1\t100.00\t7\r\n1\t8,\tSzamla1\t95.00\t8\r\n1\t9,\tSzamla2\t1000.00\t9\r\n1\t10,\tSzamla2\t3000.00\t10\r\n2\t9,10,\tSzamla2\t4000.00\t10\r\n2\t7,8,\tSzamla1\t195.00\t8\r\n2\t6,7,\tSzamla1\t289.00\t7\r\n2\t6,8,\tSzamla1\t284.00\t8\r\n3\t6,7,8,\tSzamla1\t384.00\t8\r\n2\t5,6,\tSzamla1\t204.00\t6\r\n2\t5,7,\tSzamla1\t115.00\t7\r\n2\t5,8,\tSzamla1\t110.00\t8\r\n3\t5,7,8,\tSzamla1\t210.00\t8\r\n3\t5,6,7,\tSzamla1\t304.00\t7\r\n3\t5,6,8,\tSzamla1\t299.00\t8\r\n4\t5,6,7,8,\tSzamla1\t399.00\t8\r\n2\t4,5,\tSzamla1\t35.00\t5\r\n2\t4,6,\tSzamla1\t209.00\t6\r\n2\t4,7,\tSzamla1\t120.00\t7\r\n2\t4,8,\tSzamla1\t115.00\t8\r\n3\t4,7,8,\tSzamla1\t215.00\t8\r\n3\t4,6,7,\tSzamla1\t309.00\t7\r\n3\t4,6,8,\tSzamla1\t304.00\t8\r\n4\t4,6,7,8,\tSzamla1\t404.00\t8\r\n3\t4,5,6,\tSzamla1\t224.00\t6\r\n3\t4,5,7,\tSzamla1\t135.00\t7\r\n3\t4,5,8,\tSzamla1\t130.00\t8\r\n4\t4,5,7,8,\tSzamla1\t230.00\t8\r\n4\t4,5,6,7,\tSzamla1\t324.00\t7\r\n4\t4,5,6,8,\tSzamla1\t319.00\t8\r\n5\t4,5,6,7,8,\tSzamla1\t419.00\t8\r\n2\t3,4,\tSzamla1\t270.00\t4\r\n2\t3,5,\tSzamla1\t265.00\t5\r\n2\t3,6,\tSzamla1\t439.00\t6\r\n2\t3,7,\tSzamla1\t350.00\t7\r\n2\t3,8,\tSzamla1\t345.00\t8\r\n3\t3,7,8,\tSzamla1\t445.00\t8\r\n3\t3,6,7,\tSzamla1\t539.00\t7\r\n3\t3,6,8,\tSzamla1\t534.00\t8\r\n4\t3,6,7,8,\tSzamla1\t634.00\t8\r\n3\t3,5,6,\tSzamla1\t454.00\t6\r\n3\t3,5,7,\tSzamla1\t365.00\t7\r\n3\t3,5,8,\tSzamla1\t360.00\t8\r\n4\t3,5,7,8,\tSzamla1\t460.00\t8\r\n4\t3,5,6,7,\tSzamla1\t554.00\t7\r\n4\t3,5,6,8,\tSzamla1\t549.00\t8\r\n5\t3,5,6,7,8,\tSzamla1\t649.00\t8\r\n3\t3,4,5,\tSzamla1\t285.00\t5\r\n3\t3,4,6,\tSzamla1\t459.00\t6\r\n3\t3,4,7,\tSzamla1\t370.00\t7\r\n3\t3,4,8,\tSzamla1\t365.00\t8\r\n4\t3,4,7,8,\tSzamla1\t465.00\t8\r\n4\t3,4,6,7,\tSzamla1\t559.00\t7\r\n4\t3,4,6,8,\tSzamla1\t554.00\t8\r\n5\t3,4,6,7,8,\tSzamla1\t654.00\t8\r\n4\t3,4,5,6,\tSzamla1\t474.00\t6\r\n4\t3,4,5,7,\tSzamla1\t385.00\t7\r\n4\t3,4,5,8,\tSzamla1\t380.00\t8\r\n5\t3,4,5,7,8,\tSzamla1\t480.00\t8\r\n5\t3,4,5,6,7,\tSzamla1\t574.00\t7\r\n5\t3,4,5,6,8,\tSzamla1\t569.00\t8\r\n6\t3,4,5,6,7,8,\tSzamla1\t669.00\t8\r\n2\t2,3,\tSzamla1\t361.00\t3\r\n2\t2,4,\tSzamla1\t131.00\t4\r\n2\t2,5,\tSzamla1\t126.00\t5\r\n2\t2,6,\tSzamla1\t300.00\t6\r\n2\t2,7,\tSzamla1\t211.00\t7\r\n2\t2,8,\tSzamla1\t206.00\t8\r\n3\t2,7,8,\tSzamla1\t306.00\t8\r\n3\t2,6,7,\tSzamla1\t400.00\t7\r\n3\t2,6,8,\tSzamla1\t395.00\t8\r\n4\t2,6,7,8,\tSzamla1\t495.00\t8\r\n3\t2,5,6,\tSzamla1\t315.00\t6\r\n3\t2,5,7,\tSzamla1\t226.00\t7\r\n3\t2,5,8,\tSzamla1\t221.00\t8\r\n4\t2,5,7,8,\tSzamla1\t321.00\t8\r\n4\t2,5,6,7,\tSzamla1\t415.00\t7\r\n4\t2,5,6,8,\tSzamla1\t410.00\t8\r\n5\t2,5,6,7,8,\tSzamla1\t510.00\t8\r\n3\t2,4,5,\tSzamla1\t146.00\t5\r\n3\t2,4,6,\tSzamla1\t320.00\t6\r\n3\t2,4,7,\tSzamla1\t231.00\t7\r\n3\t2,4,8,\tSzamla1\t226.00\t8\r\n4\t2,4,7,8,\tSzamla1\t326.00\t8\r\n4\t2,4,6,7,\tSzamla1\t420.00\t7\r\n4\t2,4,6,8,\tSzamla1\t415.00\t8\r\n5\t2,4,6,7,8,\tSzamla1\t515.00\t8\r\n4\t2,4,5,6,\tSzamla1\t335.00\t6\r\n4\t2,4,5,7,\tSzamla1\t246.00\t7\r\n4\t2,4,5,8,\tSzamla1\t241.00\t8\r\n5\t2,4,5,7,8,\tSzamla1\t341.00\t8\r\n5\t2,4,5,6,7,\tSzamla1\t435.00\t7\r\n5\t2,4,5,6,8,\tSzamla1\t430.00\t8\r\n6\t2,4,5,6,7,8,\tSzamla1\t530.00\t8\r\n3\t2,3,4,\tSzamla1\t381.00\t4\r\n3\t2,3,5,\tSzamla1\t376.00\t5\r\n3\t2,3,6,\tSzamla1\t550.00\t6\r\n3\t2,3,7,\tSzamla1\t461.00\t7\r\n3\t2,3,8,\tSzamla1\t456.00\t8\r\n4\t2,3,7,8,\tSzamla1\t556.00\t8\r\n4\t2,3,6,7,\tSzamla1\t650.00\t7\r\n4\t2,3,6,8,\tSzamla1\t645.00\t8\r\n5\t2,3,6,7,8,\tSzamla1\t745.00\t8\r\n4\t2,3,5,6,\tSzamla1\t565.00\t6\r\n4\t2,3,5,7,\tSzamla1\t476.00\t7\r\n4\t2,3,5,8,\tSzamla1\t471.00\t8\r\n5\t2,3,5,7,8,\tSzamla1\t571.00\t8\r\n5\t2,3,5,6,7,\tSzamla1\t665.00\t7\r\n5\t2,3,5,6,8,\tSzamla1\t660.00\t8\r\n6\t2,3,5,6,7,8,\tSzamla1\t760.00\t8\r\n4\t2,3,4,5,\tSzamla1\t396.00\t5\r\n4\t2,3,4,6,\tSzamla1\t570.00\t6\r\n4\t2,3,4,7,\tSzamla1\t481.00\t7\r\n4\t2,3,4,8,\tSzamla1\t476.00\t8\r\n5\t2,3,4,7,8,\tSzamla1\t576.00\t8\r\n5\t2,3,4,6,7,\tSzamla1\t670.00\t7\r\n5\t2,3,4,6,8,\tSzamla1\t665.00\t8\r\n6\t2,3,4,6,7,8,\tSzamla1\t765.00\t8\r\n5\t2,3,4,5,6,\tSzamla1\t585.00\t6\r\n5\t2,3,4,5,7,\tSzamla1\t496.00\t7\r\n5\t2,3,4,5,8,\tSzamla1\t491.00\t8\r\n6\t2,3,4,5,7,8,\tSzamla1\t591.00\t8\r\n6\t2,3,4,5,6,7,\tSzamla1\t685.00\t7\r\n6\t2,3,4,5,6,8,\tSzamla1\t680.00\t8\r\n7\t2,3,4,5,6,7,8,\tSzamla1\t780.00\t8\r\n2\t1,2,\tSzamla1\t131.00\t2\r\n2\t1,3,\tSzamla1\t270.00\t3\r\n2\t1,4,\tSzamla1\t40.00\t4\r\n2\t1,5,\tSzamla1\t35.00\t5\r\n2\t1,6,\tSzamla1\t209.00\t6\r\n2\t1,7,\tSzamla1\t120.00\t7\r\n2\t1,8,\tSzamla1\t115.00\t8\r\n3\t1,7,8,\tSzamla1\t215.00\t8\r\n3\t1,6,7,\tSzamla1\t309.00\t7\r\n3\t1,6,8,\tSzamla1\t304.00\t8\r\n4\t1,6,7,8,\tSzamla1\t404.00\t8\r\n3\t1,5,6,\tSzamla1\t224.00\t6\r\n3\t1,5,7,\tSzamla1\t135.00\t7\r\n3\t1,5,8,\tSzamla1\t130.00\t8\r\n4\t1,5,7,8,\tSzamla1\t230.00\t8\r\n4\t1,5,6,7,\tSzamla1\t324.00\t7\r\n4\t1,5,6,8,\tSzamla1\t319.00\t8\r\n5\t1,5,6,7,8,\tSzamla1\t419.00\t8\r\n3\t1,4,5,\tSzamla1\t55.00\t5\r\n3\t1,4,6,\tSzamla1\t229.00\t6\r\n3\t1,4,7,\tSzamla1\t140.00\t7\r\n3\t1,4,8,\tSzamla1\t135.00\t8\r\n4\t1,4,7,8,\tSzamla1\t235.00\t8\r\n4\t1,4,6,7,\tSzamla1\t329.00\t7\r\n4\t1,4,6,8,\tSzamla1\t324.00\t8\r\n5\t1,4,6,7,8,\tSzamla1\t424.00\t8\r\n4\t1,4,5,6,\tSzamla1\t244.00\t6\r\n4\t1,4,5,7,\tSzamla1\t155.00\t7\r\n4\t1,4,5,8,\tSzamla1\t150.00\t8\r\n5\t1,4,5,7,8,\tSzamla1\t250.00\t8\r\n5\t1,4,5,6,7,\tSzamla1\t344.00\t7\r\n5\t1,4,5,6,8,\tSzamla1\t339.00\t8\r\n6\t1,4,5,6,7,8,\tSzamla1\t439.00\t8\r\n3\t1,3,4,\tSzamla1\t290.00\t4\r\n3\t1,3,5,\tSzamla1\t285.00\t5\r\n3\t1,3,6,\tSzamla1\t459.00\t6\r\n3\t1,3,7,\tSzamla1\t370.00\t7\r\n3\t1,3,8,\tSzamla1\t365.00\t8\r\n4\t1,3,7,8,\tSzamla1\t465.00\t8\r\n4\t1,3,6,7,\tSzamla1\t559.00\t7\r\n4\t1,3,6,8,\tSzamla1\t554.00\t8\r\n5\t1,3,6,7,8,\tSzamla1\t654.00\t8\r\n4\t1,3,5,6,\tSzamla1\t474.00\t6\r\n4\t1,3,5,7,\tSzamla1\t385.00\t7\r\n4\t1,3,5,8,\tSzamla1\t380.00\t8\r\n5\t1,3,5,7,8,\tSzamla1\t480.00\t8\r\n5\t1,3,5,6,7,\tSzamla1\t574.00\t7\r\n5\t1,3,5,6,8,\tSzamla1\t569.00\t8\r\n6\t1,3,5,6,7,8,\tSzamla1\t669.00\t8\r\n4\t1,3,4,5,\tSzamla1\t305.00\t5\r\n4\t1,3,4,6,\tSzamla1\t479.00\t6\r\n4\t1,3,4,7,\tSzamla1\t390.00\t7\r\n4\t1,3,4,8,\tSzamla1\t385.00\t8\r\n5\t1,3,4,7,8,\tSzamla1\t485.00\t8\r\n5\t1,3,4,6,7,\tSzamla1\t579.00\t7\r\n5\t1,3,4,6,8,\tSzamla1\t574.00\t8\r\n6\t1,3,4,6,7,8,\tSzamla1\t674.00\t8\r\n5\t1,3,4,5,6,\tSzamla1\t494.00\t6\r\n5\t1,3,4,5,7,\tSzamla1\t405.00\t7\r\n5\t1,3,4,5,8,\tSzamla1\t400.00\t8\r\n6\t1,3,4,5,7,8,\tSzamla1\t500.00\t8\r\n6\t1,3,4,5,6,7,\tSzamla1\t594.00\t7\r\n6\t1,3,4,5,6,8,\tSzamla1\t589.00\t8\r\n7\t1,3,4,5,6,7,8,\tSzamla1\t689.00\t8\r\n3\t1,2,3,\tSzamla1\t381.00\t3\r\n3\t1,2,4,\tSzamla1\t151.00\t4\r\n3\t1,2,5,\tSzamla1\t146.00\t5\r\n3\t1,2,6,\tSzamla1\t320.00\t6\r\n3\t1,2,7,\tSzamla1\t231.00\t7\r\n3\t1,2,8,\tSzamla1\t226.00\t8\r\n4\t1,2,7,8,\tSzamla1\t326.00\t8\r\n4\t1,2,6,7,\tSzamla1\t420.00\t7\r\n4\t1,2,6,8,\tSzamla1\t415.00\t8\r\n5\t1,2,6,7,8,\tSzamla1\t515.00\t8\r\n4\t1,2,5,6,\tSzamla1\t335.00\t6\r\n4\t1,2,5,7,\tSzamla1\t246.00\t7\r\n4\t1,2,5,8,\tSzamla1\t241.00\t8\r\n5\t1,2,5,7,8,\tSzamla1\t341.00\t8\r\n5\t1,2,5,6,7,\tSzamla1\t435.00\t7\r\n5\t1,2,5,6,8,\tSzamla1\t430.00\t8\r\n6\t1,2,5,6,7,8,\tSzamla1\t530.00\t8\r\n4\t1,2,4,5,\tSzamla1\t166.00\t5\r\n4\t1,2,4,6,\tSzamla1\t340.00\t6\r\n4\t1,2,4,7,\tSzamla1\t251.00\t7\r\n4\t1,2,4,8,\tSzamla1\t246.00\t8\r\n5\t1,2,4,7,8,\tSzamla1\t346.00\t8\r\n5\t1,2,4,6,7,\tSzamla1\t440.00\t7\r\n5\t1,2,4,6,8,\tSzamla1\t435.00\t8\r\n6\t1,2,4,6,7,8,\tSzamla1\t535.00\t8\r\n5\t1,2,4,5,6,\tSzamla1\t355.00\t6\r\n5\t1,2,4,5,7,\tSzamla1\t266.00\t7\r\n5\t1,2,4,5,8,\tSzamla1\t261.00\t8\r\n6\t1,2,4,5,7,8,\tSzamla1\t361.00\t8\r\n6\t1,2,4,5,6,7,\tSzamla1\t455.00\t7\r\n6\t1,2,4,5,6,8,\tSzamla1\t450.00\t8\r\n7\t1,2,4,5,6,7,8,\tSzamla1\t550.00\t8\r\n4\t1,2,3,4,\tSzamla1\t401.00\t4\r\n4\t1,2,3,5,\tSzamla1\t396.00\t5\r\n4\t1,2,3,6,\tSzamla1\t570.00\t6\r\n4\t1,2,3,7,\tSzamla1\t481.00\t7\r\n4\t1,2,3,8,\tSzamla1\t476.00\t8\r\n5\t1,2,3,7,8,\tSzamla1\t576.00\t8\r\n5\t1,2,3,6,7,\tSzamla1\t670.00\t7\r\n5\t1,2,3,6,8,\tSzamla1\t665.00\t8\r\n6\t1,2,3,6,7,8,\tSzamla1\t765.00\t8\r\n5\t1,2,3,5,6,\tSzamla1\t585.00\t6\r\n5\t1,2,3,5,7,\tSzamla1\t496.00\t7\r\n5\t1,2,3,5,8,\tSzamla1\t491.00\t8\r\n6\t1,2,3,5,7,8,\tSzamla1\t591.00\t8\r\n6\t1,2,3,5,6,7,\tSzamla1\t685.00\t7\r\n6\t1,2,3,5,6,8,\tSzamla1\t680.00\t8\r\n7\t1,2,3,5,6,7,8,\tSzamla1\t780.00\t8\r\n5\t1,2,3,4,5,\tSzamla1\t416.00\t5\r\n5\t1,2,3,4,6,\tSzamla1\t590.00\t6\r\n5\t1,2,3,4,7,\tSzamla1\t501.00\t7\r\n5\t1,2,3,4,8,\tSzamla1\t496.00\t8\r\n6\t1,2,3,4,7,8,\tSzamla1\t596.00\t8\r\n6\t1,2,3,4,6,7,\tSzamla1\t690.00\t7\r\n6\t1,2,3,4,6,8,\tSzamla1\t685.00\t8\r\n7\t1,2,3,4,6,7,8,\tSzamla1\t785.00\t8\r\n6\t1,2,3,4,5,6,\tSzamla1\t605.00\t6\r\n6\t1,2,3,4,5,7,\tSzamla1\t516.00\t7\r\n6\t1,2,3,4,5,8,\tSzamla1\t511.00\t8\r\n7\t1,2,3,4,5,7,8,\tSzamla1\t611.00\t8\r\n7\t1,2,3,4,5,6,7,\tSzamla1\t705.00\t7\r\n7\t1,2,3,4,5,6,8,\tSzamla1\t700.00\t8\r\n8\t1,2,3,4,5,6,7,8,\tSzamla1\t800.00\t8\r\n<\/pre>\n<p>Mivel minden sor egy kombin\u00e1ci\u00f3t kell le\u00edrjon, k\u00e9nytelenek vagyunk valahogy t\u00f6m\u00f6r\u00edteni sok \u00e9rt\u00e9ket egy oszlopba, ez\u00e9rt ez a varchar(max)-os pakol\u00e1s.<br \/>\nA m\u00e1sodik r\u00e9sz m\u00e1r egyszer\u0171bb, a kombin\u00e1ci\u00f3k mindegyik\u00e9re kisz\u00e1molt \u00f6sszeget kell \u00f6sszeveti a fej t\u00e1bl\u00e1val:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\n;WITH Combinations AS\r\n(\r\n\tSELECT  &#x5B;Level]=1,\r\n\t\t\tCombinationId = CONVERT(VARCHAR(MAX),id)+',',\r\n\t\t\tSzamlaszam,\r\n\t\t\tTotalPrice = CONVERT(DECIMAL(10,2), Ar),\r\n\t\t\tLastId = Id\r\n\tFROM  Szamlatetel\r\n\r\n\tUNION ALL\r\n\r\n\tSELECT  &#x5B;Level]=&#x5B;Level]+1,\r\n\t\t\tCombinationId = CombinationId + CONVERT(VARCHAR(3),id) + ',',\r\n\t\t\tc.Szamlaszam,\r\n\t\t\tTotalPrice = CONVERT(DECIMAL(10,2),TotalPrice + Ar),\r\n\t\t\tLastId = Id\r\n\tFROM  Combinations c  \r\n\tINNER JOIN Szamlatetel i \r\n\t\t    ON  i.Szamlaszam = c.Szamlaszam AND i.ID &gt; LastId \r\n)\r\nSELECT i.id, i.Szamlaszam, i.Ar, s.Ado \r\nFROM Combinations c\r\nINNER JOIN Szamlafej s\r\n\t    ON s.OsszAr = c.TotalPrice\r\nINNER JOIN Szamlatetel i \r\n\t    ON CombinationId LIKE '%' + CONVERT(VARCHAR(3),i.id) + ',%'\r\nORDER BY s.Ado, s.Szamlaszam, s.ID;\r\n<\/pre>\n<p>K\u00e9t megold\u00e1s is \u00e9rkezett, mindkett\u0151 j\u00f3, gratul\u00e1lok a megfejt\u0151knek, ez t\u00e9nyleg neh\u00e9z feladat volt.<\/p>\n<p>Az egyik megold\u00e1s hasonl\u00edt az eny\u00e9mre:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\n;WITH CTE as\r\n( \r\n\tSELECT OsszAr, Ado, t.ID, Ar, cast(Ar as decimal(18,2)) as CSum,\r\n\tright('0' + CAST(t.Id as Varchar(max)),2) as path\r\n\tfrom Szamlatetel t inner join Szamlafej f on t.Szamlaszam=f.Szamlaszam\r\n\r\n\tUNION all\r\n\r\n\tSELECT OsszAr,\r\n\tAdo,\r\n\tSzamlatetel.ID,\r\n\tSzamlatetel.Ar,\r\n\tcast(Szamlatetel.Ar+CTE.CSum as decimal(18,2)) as CSum,\r\n\tCTE.path+','+ right('0' + CAST(Szamlatetel.Id as Varchar(max)),2) as path\r\n\tfrom Szamlatetel\r\n\tJOIN CTE on Szamlatetel.Ar+CTE.CSum&lt;=OsszAr and CTE.ID&lt;Szamlatetel.ID\r\n)\r\nselect Szamlatetel.ID, Szamlaszam, Szamlatetel.Ar, Ado\r\nfrom Szamlatetel, CTE\r\nwhere CTE.path like '%' + right('0' + CAST(Szamlatetel.ID as Varchar(max)),2) + '%' and CTE.CSum=OsszAr\r\norder by Ado, Szamlaszam, Szamlatetel.ID\r\n<\/pre>\n<p>A m\u00e1sik megold\u00e1s is hasonl\u00f3, de ebben a kombin\u00e1ci\u00f3kat a szerz\u0151 t\u00e1bla t\u00edpus\u00fa v\u00e1ltoz\u00f3ban t\u00e1rolja \u00e1tmenetileg:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\ndeclare @Osszeg decimal(18,2), @SzamlaTetelId int\r\n\r\ndeclare @Kombinacio table\r\n(\r\n\tId int identity(1,1) not null primary key,\r\n\tElozoId int null,\r\n\tOsszAr decimal(18,2) not null,\r\n\tSzamlaTetelId int not null\r\n)\r\n\r\nset @SzamlaTetelId = 0\r\n\r\nwhile 1 = 1\r\nbegin\r\n\tselect top 1 @Osszeg = T.Ar, @SzamlaTetelId = T.ID\r\n\tfrom\r\n\tSzamlatetel T\r\n\twhere\r\n\tT.ID &gt; @SzamlaTetelId order by T.ID\r\n\r\n\tif @@rowcount = 0\r\n\tbreak\r\n\r\n\tinsert into @Kombinacio(ElozoId, OsszAr, SzamlaTetelId)\r\n\tselect S.Id, S.OsszAr + @Osszeg, @SzamlaTetelId\r\n\tfrom\r\n\t(select OsszAr, Id from @Kombinacio union select 0, null) S\r\n\twhere\r\n\tS.OsszAr + @Osszeg not in (select OsszAr from @Kombinacio)\r\nend\r\n\r\n;with Elozmeny as\r\n(\r\n\tselect\r\n\tK.Id, K.ElozoId, K.SzamlaTetelId, K.OsszAr\r\n\tfrom\r\n\t@Kombinacio K\r\n\tinner join Szamlafej F on F.OsszAr = K.OsszAr\r\n\r\n\tunion all\r\n\r\n\tselect\r\n\tK.Id, K.ElozoId, K.SzamlaTetelId, E.OsszAr\r\n\tfrom\r\n\tElozmeny E\r\n\tinner join @Kombinacio K on E.ElozoId = K.Id\r\n)\r\nselect SzamlaTetelId = T.ID ,Szamlaszam = F.Szamlaszam ,Ar = T.Ar ,Ado = F.Ado\r\nfrom Elozmeny E\r\ninner join Szamlafej F on F.OsszAr = E.OsszAr\r\ninner join Szamlatetel T on T.ID = E.SzamlaTetelId\r\norder by\r\nE.OsszAr, T.ID\r\n<\/pre>\n<p>Ez egy olyan feladat, amin \u00e9rdemes p\u00e1r percig gondolkodni, \u00e9s r\u00e1csod\u00e1lkozni, hogy m\u00e9g egy ilyen ciklus\u00e9rt ord\u00edt\u00f3 feladatot is meg lehet oldani CTE-kkel (a hat\u00e9konys\u00e1g persze m\u00e1s k\u00e9rd\u00e9s).<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Ebben a feladatban meg kellett hat\u00e1rozni azokat a sorokat, amelyek \u00f6sszege kiad egy sort egy m\u00e1sik t\u00e1bl\u00e1ban. Mivel nem lehet tudni, h\u00e1ny&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,4,30],"tags":[],"class_list":["post-1920","post","type-post","status-publish","format-standard","hentry","category-adatbazisok","category-szakmai-elet","category-sql-server"],"_links":{"self":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1920","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=1920"}],"version-history":[{"count":1,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1920\/revisions"}],"predecessor-version":[{"id":1922,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1920\/revisions\/1922"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=1920"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=1920"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=1920"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}