Ebben a feladatban meg kellett határozni azokat a sorokat, amelyek összege kiad egy sort egy másik táblában. Mivel nem lehet tudni, hány sort kell szummázni, ezért kombinációkat kellett képezni az elemekből.
A feladat nehezebb része tehát, hogyan kell SQL-ből kombinációkat képezni?
;WITH Combinations AS
(
SELECT [Level]=1,
CombinationId = CONVERT(VARCHAR(MAX),id)+',',
Szamlaszam,
TotalPrice = CONVERT(DECIMAL(10,2), Ar),
LastId = Id
FROM Szamlatetel
UNION ALL
SELECT [Level]=[Level]+1,
CombinationId = CombinationId + CONVERT(VARCHAR(3),id) + ',',
c.Szamlaszam,
TotalPrice = CONVERT(DECIMAL(10,2),TotalPrice + Ar),
LastId = Id
FROM Combinations c
INNER JOIN Szamlatetel i
ON i.Szamlaszam = c.Szamlaszam AND i.ID > LastId
)
select * from Combinations;
Ennek kimenete:
1 1, Szamla1 20.00 1
1 2, Szamla1 111.00 2
1 3, Szamla1 250.00 3
1 4, Szamla1 20.00 4
1 5, Szamla1 15.00 5
1 6, Szamla1 189.00 6
1 7, Szamla1 100.00 7
1 8, Szamla1 95.00 8
1 9, Szamla2 1000.00 9
1 10, Szamla2 3000.00 10
2 9,10, Szamla2 4000.00 10
2 7,8, Szamla1 195.00 8
2 6,7, Szamla1 289.00 7
2 6,8, Szamla1 284.00 8
3 6,7,8, Szamla1 384.00 8
2 5,6, Szamla1 204.00 6
2 5,7, Szamla1 115.00 7
2 5,8, Szamla1 110.00 8
3 5,7,8, Szamla1 210.00 8
3 5,6,7, Szamla1 304.00 7
3 5,6,8, Szamla1 299.00 8
4 5,6,7,8, Szamla1 399.00 8
2 4,5, Szamla1 35.00 5
2 4,6, Szamla1 209.00 6
2 4,7, Szamla1 120.00 7
2 4,8, Szamla1 115.00 8
3 4,7,8, Szamla1 215.00 8
3 4,6,7, Szamla1 309.00 7
3 4,6,8, Szamla1 304.00 8
4 4,6,7,8, Szamla1 404.00 8
3 4,5,6, Szamla1 224.00 6
3 4,5,7, Szamla1 135.00 7
3 4,5,8, Szamla1 130.00 8
4 4,5,7,8, Szamla1 230.00 8
4 4,5,6,7, Szamla1 324.00 7
4 4,5,6,8, Szamla1 319.00 8
5 4,5,6,7,8, Szamla1 419.00 8
2 3,4, Szamla1 270.00 4
2 3,5, Szamla1 265.00 5
2 3,6, Szamla1 439.00 6
2 3,7, Szamla1 350.00 7
2 3,8, Szamla1 345.00 8
3 3,7,8, Szamla1 445.00 8
3 3,6,7, Szamla1 539.00 7
3 3,6,8, Szamla1 534.00 8
4 3,6,7,8, Szamla1 634.00 8
3 3,5,6, Szamla1 454.00 6
3 3,5,7, Szamla1 365.00 7
3 3,5,8, Szamla1 360.00 8
4 3,5,7,8, Szamla1 460.00 8
4 3,5,6,7, Szamla1 554.00 7
4 3,5,6,8, Szamla1 549.00 8
5 3,5,6,7,8, Szamla1 649.00 8
3 3,4,5, Szamla1 285.00 5
3 3,4,6, Szamla1 459.00 6
3 3,4,7, Szamla1 370.00 7
3 3,4,8, Szamla1 365.00 8
4 3,4,7,8, Szamla1 465.00 8
4 3,4,6,7, Szamla1 559.00 7
4 3,4,6,8, Szamla1 554.00 8
5 3,4,6,7,8, Szamla1 654.00 8
4 3,4,5,6, Szamla1 474.00 6
4 3,4,5,7, Szamla1 385.00 7
4 3,4,5,8, Szamla1 380.00 8
5 3,4,5,7,8, Szamla1 480.00 8
5 3,4,5,6,7, Szamla1 574.00 7
5 3,4,5,6,8, Szamla1 569.00 8
6 3,4,5,6,7,8, Szamla1 669.00 8
2 2,3, Szamla1 361.00 3
2 2,4, Szamla1 131.00 4
2 2,5, Szamla1 126.00 5
2 2,6, Szamla1 300.00 6
2 2,7, Szamla1 211.00 7
2 2,8, Szamla1 206.00 8
3 2,7,8, Szamla1 306.00 8
3 2,6,7, Szamla1 400.00 7
3 2,6,8, Szamla1 395.00 8
4 2,6,7,8, Szamla1 495.00 8
3 2,5,6, Szamla1 315.00 6
3 2,5,7, Szamla1 226.00 7
3 2,5,8, Szamla1 221.00 8
4 2,5,7,8, Szamla1 321.00 8
4 2,5,6,7, Szamla1 415.00 7
4 2,5,6,8, Szamla1 410.00 8
5 2,5,6,7,8, Szamla1 510.00 8
3 2,4,5, Szamla1 146.00 5
3 2,4,6, Szamla1 320.00 6
3 2,4,7, Szamla1 231.00 7
3 2,4,8, Szamla1 226.00 8
4 2,4,7,8, Szamla1 326.00 8
4 2,4,6,7, Szamla1 420.00 7
4 2,4,6,8, Szamla1 415.00 8
5 2,4,6,7,8, Szamla1 515.00 8
4 2,4,5,6, Szamla1 335.00 6
4 2,4,5,7, Szamla1 246.00 7
4 2,4,5,8, Szamla1 241.00 8
5 2,4,5,7,8, Szamla1 341.00 8
5 2,4,5,6,7, Szamla1 435.00 7
5 2,4,5,6,8, Szamla1 430.00 8
6 2,4,5,6,7,8, Szamla1 530.00 8
3 2,3,4, Szamla1 381.00 4
3 2,3,5, Szamla1 376.00 5
3 2,3,6, Szamla1 550.00 6
3 2,3,7, Szamla1 461.00 7
3 2,3,8, Szamla1 456.00 8
4 2,3,7,8, Szamla1 556.00 8
4 2,3,6,7, Szamla1 650.00 7
4 2,3,6,8, Szamla1 645.00 8
5 2,3,6,7,8, Szamla1 745.00 8
4 2,3,5,6, Szamla1 565.00 6
4 2,3,5,7, Szamla1 476.00 7
4 2,3,5,8, Szamla1 471.00 8
5 2,3,5,7,8, Szamla1 571.00 8
5 2,3,5,6,7, Szamla1 665.00 7
5 2,3,5,6,8, Szamla1 660.00 8
6 2,3,5,6,7,8, Szamla1 760.00 8
4 2,3,4,5, Szamla1 396.00 5
4 2,3,4,6, Szamla1 570.00 6
4 2,3,4,7, Szamla1 481.00 7
4 2,3,4,8, Szamla1 476.00 8
5 2,3,4,7,8, Szamla1 576.00 8
5 2,3,4,6,7, Szamla1 670.00 7
5 2,3,4,6,8, Szamla1 665.00 8
6 2,3,4,6,7,8, Szamla1 765.00 8
5 2,3,4,5,6, Szamla1 585.00 6
5 2,3,4,5,7, Szamla1 496.00 7
5 2,3,4,5,8, Szamla1 491.00 8
6 2,3,4,5,7,8, Szamla1 591.00 8
6 2,3,4,5,6,7, Szamla1 685.00 7
6 2,3,4,5,6,8, Szamla1 680.00 8
7 2,3,4,5,6,7,8, Szamla1 780.00 8
2 1,2, Szamla1 131.00 2
2 1,3, Szamla1 270.00 3
2 1,4, Szamla1 40.00 4
2 1,5, Szamla1 35.00 5
2 1,6, Szamla1 209.00 6
2 1,7, Szamla1 120.00 7
2 1,8, Szamla1 115.00 8
3 1,7,8, Szamla1 215.00 8
3 1,6,7, Szamla1 309.00 7
3 1,6,8, Szamla1 304.00 8
4 1,6,7,8, Szamla1 404.00 8
3 1,5,6, Szamla1 224.00 6
3 1,5,7, Szamla1 135.00 7
3 1,5,8, Szamla1 130.00 8
4 1,5,7,8, Szamla1 230.00 8
4 1,5,6,7, Szamla1 324.00 7
4 1,5,6,8, Szamla1 319.00 8
5 1,5,6,7,8, Szamla1 419.00 8
3 1,4,5, Szamla1 55.00 5
3 1,4,6, Szamla1 229.00 6
3 1,4,7, Szamla1 140.00 7
3 1,4,8, Szamla1 135.00 8
4 1,4,7,8, Szamla1 235.00 8
4 1,4,6,7, Szamla1 329.00 7
4 1,4,6,8, Szamla1 324.00 8
5 1,4,6,7,8, Szamla1 424.00 8
4 1,4,5,6, Szamla1 244.00 6
4 1,4,5,7, Szamla1 155.00 7
4 1,4,5,8, Szamla1 150.00 8
5 1,4,5,7,8, Szamla1 250.00 8
5 1,4,5,6,7, Szamla1 344.00 7
5 1,4,5,6,8, Szamla1 339.00 8
6 1,4,5,6,7,8, Szamla1 439.00 8
3 1,3,4, Szamla1 290.00 4
3 1,3,5, Szamla1 285.00 5
3 1,3,6, Szamla1 459.00 6
3 1,3,7, Szamla1 370.00 7
3 1,3,8, Szamla1 365.00 8
4 1,3,7,8, Szamla1 465.00 8
4 1,3,6,7, Szamla1 559.00 7
4 1,3,6,8, Szamla1 554.00 8
5 1,3,6,7,8, Szamla1 654.00 8
4 1,3,5,6, Szamla1 474.00 6
4 1,3,5,7, Szamla1 385.00 7
4 1,3,5,8, Szamla1 380.00 8
5 1,3,5,7,8, Szamla1 480.00 8
5 1,3,5,6,7, Szamla1 574.00 7
5 1,3,5,6,8, Szamla1 569.00 8
6 1,3,5,6,7,8, Szamla1 669.00 8
4 1,3,4,5, Szamla1 305.00 5
4 1,3,4,6, Szamla1 479.00 6
4 1,3,4,7, Szamla1 390.00 7
4 1,3,4,8, Szamla1 385.00 8
5 1,3,4,7,8, Szamla1 485.00 8
5 1,3,4,6,7, Szamla1 579.00 7
5 1,3,4,6,8, Szamla1 574.00 8
6 1,3,4,6,7,8, Szamla1 674.00 8
5 1,3,4,5,6, Szamla1 494.00 6
5 1,3,4,5,7, Szamla1 405.00 7
5 1,3,4,5,8, Szamla1 400.00 8
6 1,3,4,5,7,8, Szamla1 500.00 8
6 1,3,4,5,6,7, Szamla1 594.00 7
6 1,3,4,5,6,8, Szamla1 589.00 8
7 1,3,4,5,6,7,8, Szamla1 689.00 8
3 1,2,3, Szamla1 381.00 3
3 1,2,4, Szamla1 151.00 4
3 1,2,5, Szamla1 146.00 5
3 1,2,6, Szamla1 320.00 6
3 1,2,7, Szamla1 231.00 7
3 1,2,8, Szamla1 226.00 8
4 1,2,7,8, Szamla1 326.00 8
4 1,2,6,7, Szamla1 420.00 7
4 1,2,6,8, Szamla1 415.00 8
5 1,2,6,7,8, Szamla1 515.00 8
4 1,2,5,6, Szamla1 335.00 6
4 1,2,5,7, Szamla1 246.00 7
4 1,2,5,8, Szamla1 241.00 8
5 1,2,5,7,8, Szamla1 341.00 8
5 1,2,5,6,7, Szamla1 435.00 7
5 1,2,5,6,8, Szamla1 430.00 8
6 1,2,5,6,7,8, Szamla1 530.00 8
4 1,2,4,5, Szamla1 166.00 5
4 1,2,4,6, Szamla1 340.00 6
4 1,2,4,7, Szamla1 251.00 7
4 1,2,4,8, Szamla1 246.00 8
5 1,2,4,7,8, Szamla1 346.00 8
5 1,2,4,6,7, Szamla1 440.00 7
5 1,2,4,6,8, Szamla1 435.00 8
6 1,2,4,6,7,8, Szamla1 535.00 8
5 1,2,4,5,6, Szamla1 355.00 6
5 1,2,4,5,7, Szamla1 266.00 7
5 1,2,4,5,8, Szamla1 261.00 8
6 1,2,4,5,7,8, Szamla1 361.00 8
6 1,2,4,5,6,7, Szamla1 455.00 7
6 1,2,4,5,6,8, Szamla1 450.00 8
7 1,2,4,5,6,7,8, Szamla1 550.00 8
4 1,2,3,4, Szamla1 401.00 4
4 1,2,3,5, Szamla1 396.00 5
4 1,2,3,6, Szamla1 570.00 6
4 1,2,3,7, Szamla1 481.00 7
4 1,2,3,8, Szamla1 476.00 8
5 1,2,3,7,8, Szamla1 576.00 8
5 1,2,3,6,7, Szamla1 670.00 7
5 1,2,3,6,8, Szamla1 665.00 8
6 1,2,3,6,7,8, Szamla1 765.00 8
5 1,2,3,5,6, Szamla1 585.00 6
5 1,2,3,5,7, Szamla1 496.00 7
5 1,2,3,5,8, Szamla1 491.00 8
6 1,2,3,5,7,8, Szamla1 591.00 8
6 1,2,3,5,6,7, Szamla1 685.00 7
6 1,2,3,5,6,8, Szamla1 680.00 8
7 1,2,3,5,6,7,8, Szamla1 780.00 8
5 1,2,3,4,5, Szamla1 416.00 5
5 1,2,3,4,6, Szamla1 590.00 6
5 1,2,3,4,7, Szamla1 501.00 7
5 1,2,3,4,8, Szamla1 496.00 8
6 1,2,3,4,7,8, Szamla1 596.00 8
6 1,2,3,4,6,7, Szamla1 690.00 7
6 1,2,3,4,6,8, Szamla1 685.00 8
7 1,2,3,4,6,7,8, Szamla1 785.00 8
6 1,2,3,4,5,6, Szamla1 605.00 6
6 1,2,3,4,5,7, Szamla1 516.00 7
6 1,2,3,4,5,8, Szamla1 511.00 8
7 1,2,3,4,5,7,8, Szamla1 611.00 8
7 1,2,3,4,5,6,7, Szamla1 705.00 7
7 1,2,3,4,5,6,8, Szamla1 700.00 8
8 1,2,3,4,5,6,7,8, Szamla1 800.00 8
Mivel minden sor egy kombinációt kell leírjon, kénytelenek vagyunk valahogy tömöríteni sok értéket egy oszlopba, ezért ez a varchar(max)-os pakolás.
A második rész már egyszerűbb, a kombinációk mindegyikére kiszámolt összeget kell összeveti a fej táblával:
;WITH Combinations AS
(
SELECT [Level]=1,
CombinationId = CONVERT(VARCHAR(MAX),id)+',',
Szamlaszam,
TotalPrice = CONVERT(DECIMAL(10,2), Ar),
LastId = Id
FROM Szamlatetel
UNION ALL
SELECT [Level]=[Level]+1,
CombinationId = CombinationId + CONVERT(VARCHAR(3),id) + ',',
c.Szamlaszam,
TotalPrice = CONVERT(DECIMAL(10,2),TotalPrice + Ar),
LastId = Id
FROM Combinations c
INNER JOIN Szamlatetel i
ON i.Szamlaszam = c.Szamlaszam AND i.ID > LastId
)
SELECT i.id, i.Szamlaszam, i.Ar, s.Ado
FROM Combinations c
INNER JOIN Szamlafej s
ON s.OsszAr = c.TotalPrice
INNER JOIN Szamlatetel i
ON CombinationId LIKE '%' + CONVERT(VARCHAR(3),i.id) + ',%'
ORDER BY s.Ado, s.Szamlaszam, s.ID;
Két megoldás is érkezett, mindkettő jó, gratulálok a megfejtőknek, ez tényleg nehéz feladat volt.
Az egyik megoldás hasonlít az enyémre:
;WITH CTE as
(
SELECT OsszAr, Ado, t.ID, Ar, cast(Ar as decimal(18,2)) as CSum,
right('0' + CAST(t.Id as Varchar(max)),2) as path
from Szamlatetel t inner join Szamlafej f on t.Szamlaszam=f.Szamlaszam
UNION all
SELECT OsszAr,
Ado,
Szamlatetel.ID,
Szamlatetel.Ar,
cast(Szamlatetel.Ar+CTE.CSum as decimal(18,2)) as CSum,
CTE.path+','+ right('0' + CAST(Szamlatetel.Id as Varchar(max)),2) as path
from Szamlatetel
JOIN CTE on Szamlatetel.Ar+CTE.CSum<=OsszAr and CTE.ID<Szamlatetel.ID
)
select Szamlatetel.ID, Szamlaszam, Szamlatetel.Ar, Ado
from Szamlatetel, CTE
where CTE.path like '%' + right('0' + CAST(Szamlatetel.ID as Varchar(max)),2) + '%' and CTE.CSum=OsszAr
order by Ado, Szamlaszam, Szamlatetel.ID
A másik megoldás is hasonló, de ebben a kombinációkat a szerző tábla típusú változóban tárolja átmenetileg:
declare @Osszeg decimal(18,2), @SzamlaTetelId int
declare @Kombinacio table
(
Id int identity(1,1) not null primary key,
ElozoId int null,
OsszAr decimal(18,2) not null,
SzamlaTetelId int not null
)
set @SzamlaTetelId = 0
while 1 = 1
begin
select top 1 @Osszeg = T.Ar, @SzamlaTetelId = T.ID
from
Szamlatetel T
where
T.ID > @SzamlaTetelId order by T.ID
if @@rowcount = 0
break
insert into @Kombinacio(ElozoId, OsszAr, SzamlaTetelId)
select S.Id, S.OsszAr + @Osszeg, @SzamlaTetelId
from
(select OsszAr, Id from @Kombinacio union select 0, null) S
where
S.OsszAr + @Osszeg not in (select OsszAr from @Kombinacio)
end
;with Elozmeny as
(
select
K.Id, K.ElozoId, K.SzamlaTetelId, K.OsszAr
from
@Kombinacio K
inner join Szamlafej F on F.OsszAr = K.OsszAr
union all
select
K.Id, K.ElozoId, K.SzamlaTetelId, E.OsszAr
from
Elozmeny E
inner join @Kombinacio K on E.ElozoId = K.Id
)
select SzamlaTetelId = T.ID ,Szamlaszam = F.Szamlaszam ,Ar = T.Ar ,Ado = F.Ado
from Elozmeny E
inner join Szamlafej F on F.OsszAr = E.OsszAr
inner join Szamlatetel T on T.ID = E.SzamlaTetelId
order by
E.OsszAr, T.ID
Ez egy olyan feladat, amin érdemes pár percig gondolkodni, és rácsodálkozni, hogy még egy ilyen ciklusért ordító feladatot is meg lehet oldani CTE-kkel (a hatékonyság persze más kérdés).