Could you hire me? Contact me if you like what I’ve done in this article and think I can create value for your company with my skills.

January 28, 2015 / by Zsolt Soczó

SQL fejtörő 6. megoldás

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).

Could you hire me? Contact me if you like what I’ve done in this article and think I can create value for your company with my skills.