SQL Recursion 2. @ Slo-Tech

Forum » Programiranje »
SQL Recursion 2.

SQL Recursion 2.

korenje3 :: 30. mar 2020, 15:57

Torej imam tak ukaz:

-- filter: 0 ms
), [RecursionSource] AS (
SELECT
	[Kosovnica].[KoStZapisa] AS [KoStZapisa],
	[Kosovnica].[KoNadSifMp] AS [Parent],
	CAST('' AS VARCHAR) AS [RecID1-Parent],
	[Kosovnica].[KoSifStat] AS [KoSifStat],
	[Kosovnica].[KoPodSifMp] AS [Ident],
	CAST('Key' AS VARCHAR) AS [RecID2-Ident],
	[Kosovnica].[KoKolMateriala] AS [Kos.],
	[Kosovnica].[KoVarianta] AS [VariantaParent],
	[Kosovnica].[KoVariantaPod] AS [VariantaChild]
FROM 	[Kosovnica]
	FULL JOIN [MaticniPodatki] ON [Kosovnica].[KoPodSifMp] = [MaticniPodatki].[MpSifra]
WHERE	([Kosovnica].[KoPodSifMp] = PARSE('17893' AS INT USING 'Sl-SI'))
AND	([Kosovnica].[KoVarianta] LIKE CAST('%*%' AS VARCHAR(3)))
UNION ALL
SELECT
	[Kosovnica].[KoStZapisa] AS [KoStZapisa],
	[Kosovnica].[KoNadSifMp] AS [Parent],
	[RecursionSource].[RecID2-Ident] AS [RecID1-Parent],
	[Kosovnica].[KoSifStat] AS [KoSifStat],
	[Kosovnica].[KoPodSifMp] AS [Ident],
	CAST(CONCAT([RecursionSource].[RecID2-Ident], '-', [Kosovnica].[KoPodSifMp]) AS VARCHAR) AS [RecID2-Ident],
	[Kosovnica].[KoKolMateriala] * [RecursionSource].[Kos.] AS [Kos.],
	[Kosovnica].[KoVarianta] AS [VariantaParent],
	[Kosovnica].[KoVariantaPod] AS [VariantaChild]
FROM 	[Kosovnica]
	INNER JOIN [RecursionSource] ON [RecursionSource].[Ident] = [Kosovnica].[KoNadSifMp]
WHERE	([RecursionSource].[VariantaChild] = [Kosovnica].[KoVarianta])
)
-- recursion: 0 ms

Izstopni podatki so pa taki:

[RecID1-Parent]	[RecID2-Ident]
		Key	
		Key
Key		Key-13639
Key-13639	Key-13639-12137
Key-13639	Key-13639-12203
Key-13639	Key-13639-21339
Key-13639	Key-13639-21605
Key-13639-21605	Key-13639-21605-12203
Key-13639	Key-13639-29462
Key-13639	Key-13639-29467
Key-13639	Key-13639-29718
Key-13639-29718	Key-13639-29718-21120
Key-13639	Key-13639-29956
Key		Key-17610
Key-17610	Key-17610-12137
Key-17610	Key-17610-12203
Key-17610	Key-17610-21339
Key-17610	Key-17610-21605
Key-17610-21605	Key-17610-21605-12203
Key-17610	Key-17610-29461
Key-17610	Key-17610-29466
Key-17610	Key-17610-29718
Key-17610-29718	Key-17610-29718-21120
Key-17610	Key-17610-29956

Kako bi naredil, da mi [RecID2-Ident] izpiše samo 1 vrstico. Torej če je večkrat "Key" da bo samo prva vrstica.

i9-12900k; 32GB DDR5-6000 CL36; Nvidia RTX 3080 ti;
Gigabyte Aorus z690 master; Be Quiet Dark Power 12 1000W

prozac :: 30. mar 2020, 20:46

predvidevam, da ti prvi del poizvedbe vrne več zapisov. Je rešitev, da v prvem delu union all uporabiš distinct?

SELECT distinct
	[Kosovnica].[KoStZapisa] AS [KoStZapisa],
	[Kosovnica].[KoNadSifMp] AS [Parent],
	CAST('' AS VARCHAR) AS [RecID1-Parent],
	[Kosovnica].[KoSifStat] AS [KoSifStat],
	[Kosovnica].[KoPodSifMp] AS [Ident],
	CAST('Key' AS VARCHAR) AS [RecID2-Ident],
	[Kosovnica].[KoKolMateriala] AS [Kos.],
	[Kosovnica].[KoVarianta] AS [VariantaParent],
	[Kosovnica].[KoVariantaPod] AS [VariantaChild]
FROM 	[Kosovnica]
	FULL JOIN [MaticniPodatki] ON [Kosovnica].[KoPodSifMp] = [MaticniPodatki].[MpSifra]
WHERE	([Kosovnica].[KoPodSifMp] = PARSE('17893' AS INT USING 'Sl-SI'))
AND	([Kosovnica].[KoVarianta] LIKE CAST('%*%' AS VARCHAR(3)))

druga manj elegantna varianta, ki morda niti ni pravilna, da na koncu nad izhodom narediš poizvedbo in tam uporabiš distinct (tega bi se izogibal)

Zgodovina sprememb…

spremenilo: prozac (30. mar 2020 ob 20:48)

prozac :: 30. mar 2020, 20:59

lahko morda namesto rekurzije uporabiš connect by prior

select koVarianta as "RecID1-Parent", koVariantaPod as "RecID2-Ident"
from kosovnica
START WITH koVarianta IS NULL
CONNECT BY PRIOR koVarianta = koVariantaPod

sedaj nimam dostopa do baze, da bi sprobal če napisano res dela

korenje3 :: 30. mar 2020, 22:24

Torej imam [RecursionSource] samo za drevesno strukturo in pa [FilteredSource] za vse idente.
Filteredsource in recursionsource nato inner joinam, tako da dobim imena za posamične idente.

Aja imam pa tsql.

i9-12900k; 32GB DDR5-6000 CL36; Nvidia RTX 3080 ti;
Gigabyte Aorus z690 master; Be Quiet Dark Power 12 1000W

Zgodovina sprememb…

spremenil: korenje3 (30. mar 2020 ob 23:03)

korenje3 :: 30. mar 2020, 23:07

Opcija je da namesto

WHERE	([Kosovnica].[KoPodSifMp] = PARSE('17893' AS INT USING 'Sl-SI'))

uporabim

WHERE	([Kosovnica].[KoNadSifMp] = PARSE('17893' AS INT USING 'Sl-SI'))

vendar bo potem iskani ident v celoti izvzet iz queryja.

i9-12900k; 32GB DDR5-6000 CL36; Nvidia RTX 3080 ti;
Gigabyte Aorus z690 master; Be Quiet Dark Power 12 1000W

Vazelin :: 30. mar 2020, 23:13

korenje: en nasvet. nauč se vejce med stolpci pisat v novi vrstici na začetku. sigurno bolj pregledno kot na koncu.

I got 99 problems but 4 usd XTZ ain't one...

korenje3 :: 30. mar 2020, 23:19

torej vejica samostojno v novi vrstici?
vejice dajam spredaj sicer samo kadar testiram. sql stavek je pa avtomatsko generiran.

i9-12900k; 32GB DDR5-6000 CL36; Nvidia RTX 3080 ti;
Gigabyte Aorus z690 master; Be Quiet Dark Power 12 1000W

Zgodovina sprememb…

spremenil: korenje3 (30. mar 2020 ob 23:22)

Vazelin :: 30. mar 2020, 23:22

SELECT
[Kosovnica].[KoStZapisa] AS [KoStZapisa]
, [Kosovnica].[KoNadSifMp] AS [Parent]
, XYZ

Mah ne, sam men se zdi bolj pregledno tako. In manj možnosti za napake je, da kaj pozabiš, če se vejica tlači na koncu :P Nisem profi samo meni je bolje tako in nisem edini. Če pozabiš kje vejco maš samo eno stolpec na koncu manj v rezultatih

I got 99 problems but 4 usd XTZ ain't one...

Zgodovina sprememb…

spremenilo: Vazelin (30. mar 2020 ob 23:23)

korenje3 :: 30. mar 2020, 23:23

Imam program ki avtomatsko generira stavek, tako da do napak ne more pridet.

i9-12900k; 32GB DDR5-6000 CL36; Nvidia RTX 3080 ti;
Gigabyte Aorus z690 master; Be Quiet Dark Power 12 1000W

Zgodovina sprememb…

spremenil: korenje3 (30. mar 2020 ob 23:25)

Vazelin :: 30. mar 2020, 23:26

a ta program si ti naredu? izgleda ogromno opcij

I got 99 problems but 4 usd XTZ ain't one...

korenje3 :: 30. mar 2020, 23:29

Jap. Excel vba program za iskanje in izpis direktno v excel.

Načeloma sem hotel neko hitro skriptico narest v parih dneh, pa se je potem razvleklo... in na koncu 100x več dela.
Dejansko je program tako nasičen, da sem moral nova okna delat ker sem prišel do same omejitve vba. Pri 200 vtičnikih/okno začne program crkovat.

V glavnem sedaj iščem rešitev, kako bi prikazal še iskani ident v enem rowu, ki bi mu ročno nastavil količino na 1.

Problem je pa ta:
Če iščem po ident v KoPodSifMp namesto KoNadSifMp bo prikazal vse nadrejene, ki vsebujejo iskani ident, tako da se ta lahko večkrat ponovi, količina je pa lahko kar nekaj.

Celoten sql stavek je pa tak:

WITH [FilteredSource] AS (
-- partition: 0 ms
SELECT
	[MaticniPodatki].[MpSifra] AS [Šifra],
	RTRIM([MaticniPodatki].[MpNaziv]) AS [Naziv],
	RTRIM([MaticniPodatki].[MpDoNaziv]) AS [Dolgi naziv],
	[MaticniPodatki].[MpOznKlas] AS [Klasifikacija],
	[MaticniPodatki].[MpRisbaFName] AS [Datoteka],
	RTRIM(CASE 
		WHEN REPLACE([MaticniPodatki].[MpSifKarKlj], CAST('A' AS CHAR(1)), CAST('Faza' AS CHAR(4))) <> [MaticniPodatki].[MpSifKarKlj] THEN REPLACE([MaticniPodatki].[MpSifKarKlj], CAST('A' AS CHAR(1)), CAST('Faza' AS CHAR(4)))
		WHEN REPLACE([MaticniPodatki].[MpSifKarKlj], CAST('B' AS CHAR(1)), CAST('Trgovsko blago' AS CHAR(14))) <> [MaticniPodatki].[MpSifKarKlj] THEN REPLACE([MaticniPodatki].[MpSifKarKlj], CAST('B' AS CHAR(1)), CAST('Trgovsko blago' AS CHAR(14)))
		WHEN REPLACE([MaticniPodatki].[MpSifKarKlj], CAST('D' AS CHAR(1)), CAST('Drobni inventar' AS CHAR(15))) <> [MaticniPodatki].[MpSifKarKlj] THEN REPLACE([MaticniPodatki].[MpSifKarKlj], CAST('D' AS CHAR(1)), CAST('Drobni inventar' AS CHAR(15)))
		WHEN REPLACE([MaticniPodatki].[MpSifKarKlj], CAST('F' AS CHAR(1)), CAST('Fantom' AS CHAR(6))) <> [MaticniPodatki].[MpSifKarKlj] THEN REPLACE([MaticniPodatki].[MpSifKarKlj], CAST('F' AS CHAR(1)), CAST('Fantom' AS CHAR(6)))
		WHEN REPLACE([MaticniPodatki].[MpSifKarKlj], CAST('I' AS CHAR(1)), CAST('Izdelek' AS CHAR(7))) <> [MaticniPodatki].[MpSifKarKlj] THEN REPLACE([MaticniPodatki].[MpSifKarKlj], CAST('I' AS CHAR(1)), CAST('Izdelek' AS CHAR(7)))
		WHEN REPLACE([MaticniPodatki].[MpSifKarKlj], CAST('M' AS CHAR(1)), CAST('Material' AS CHAR(8))) <> [MaticniPodatki].[MpSifKarKlj] THEN REPLACE([MaticniPodatki].[MpSifKarKlj], CAST('M' AS CHAR(1)), CAST('Material' AS CHAR(8)))
		WHEN REPLACE([MaticniPodatki].[MpSifKarKlj], CAST('O' AS CHAR(1)), CAST('Osnovno sredstvo' AS CHAR(16))) <> [MaticniPodatki].[MpSifKarKlj] THEN REPLACE([MaticniPodatki].[MpSifKarKlj], CAST('O' AS CHAR(1)), CAST('Osnovno sredstvo' AS CHAR(16)))
		WHEN REPLACE([MaticniPodatki].[MpSifKarKlj], CAST('P' AS CHAR(1)), CAST('Polizdelek' AS CHAR(10))) <> [MaticniPodatki].[MpSifKarKlj] THEN REPLACE([MaticniPodatki].[MpSifKarKlj], CAST('P' AS CHAR(1)), CAST('Polizdelek' AS CHAR(10)))
		WHEN REPLACE([MaticniPodatki].[MpSifKarKlj], CAST('R' AS CHAR(1)), CAST('Repromaterial' AS CHAR(13))) <> [MaticniPodatki].[MpSifKarKlj] THEN REPLACE([MaticniPodatki].[MpSifKarKlj], CAST('R' AS CHAR(1)), CAST('Repromaterial' AS CHAR(13)))
		WHEN REPLACE([MaticniPodatki].[MpSifKarKlj], CAST('S' AS CHAR(1)), CAST('Sklop' AS CHAR(5))) <> [MaticniPodatki].[MpSifKarKlj] THEN REPLACE([MaticniPodatki].[MpSifKarKlj], CAST('S' AS CHAR(1)), CAST('Sklop' AS CHAR(5)))
		WHEN REPLACE([MaticniPodatki].[MpSifKarKlj], CAST('T' AS CHAR(1)), CAST('Storitev' AS CHAR(8))) <> [MaticniPodatki].[MpSifKarKlj] THEN REPLACE([MaticniPodatki].[MpSifKarKlj], CAST('T' AS CHAR(1)), CAST('Storitev' AS CHAR(8)))
		WHEN REPLACE([MaticniPodatki].[MpSifKarKlj], CAST('X' AS CHAR(1)), CAST('Modul' AS CHAR(5))) <> [MaticniPodatki].[MpSifKarKlj] THEN REPLACE([MaticniPodatki].[MpSifKarKlj], CAST('X' AS CHAR(1)), CAST('Modul' AS CHAR(5)))
	 END) AS [KK],
	[MaticniPodatki].[MpStatus] AS [MpStatus],
	[MaticniPodatki].[MpSifEnoteMere1] AS [Enota 1],
	[NFakP].[NFaPSifra] AS [NFaPSifra],
	[NFakP].[NFaPStNFak] AS [NFaPStNFak],
	CASE
		WHEN [NFakP].[NFaPEM] IS NULL THEN [MaticniPodatki].[MpSifEnoteMere1]
		ELSE [NFakP].[NFaPEM]
	END AS [Enota],
	[NFakP].[NFaPZnesekSkupaj] AS [Znesek z DDV],
	[NFakP].[NFaPZnesekDav] AS [DDV],
	[NFakP].[NFaPKol] AS [Količina],
	[NFaktura].[Datum] AS [Datum],
	[NFaktura].[NFaSifPart] AS [NFaSifPart],
	[NFaktura].[NFaRefer] AS [NFaRefer],
	RTRIM([Partnerji].[PaNaziv]) AS [PaNaziv],
	RTRIM([Partnerji].[PaUlicaHisnaSt]) AS [PaUlicaHisnaSt],
	RTRIM([Partnerji].[PaKraj]) AS [PaKraj],
	RTRIM([Partnerji].[PaPostnaSt]) AS [PaPostnaSt],
	[Partnerji].[PaSifDrzave] AS [PaSifDrzave],
	RTRIM([Partnerji].[PaTelefon1]) AS [PaTelefon1],
	RTRIM([Partnerji].[PaTelefon2]) AS [PaTelefon2],
	[Partnerji].[PaSifra] AS [PaSifra],
	[Delavci].[DeSifra] AS [DeSifra],
	RTRIM([Delavci].[DeIme]) AS [DeIme],
	RTRIM([Delavci].[DePriimek]) AS [DePriimek],
	RTRIM([Drzave].[DrSifra]) AS [DrSifra],
	RTRIM([Drzave].[DrNaziv]) AS [DrNaziv],
	RTRIM([Drzave].[DrKoda]) AS [DrKoda],
	ROW_NUMBER() OVER (PARTITION BY [MaticniPodatki].[MpSifra] ORDER BY [NFaktura].[Datum] DESC) AS [Column1]
-- select: 0 ms
FROM 	[NFakP]
	FULL JOIN [MaticniPodatki] ON [NFakP].[NFaPSifra] = [MaticniPodatki].[MpSifra]
	FULL JOIN [NFaktura] ON [NFakP].[NFaPStNFak] = [NFaktura].[NFaZapSt]
	FULL JOIN [Partnerji] ON [NFaktura].[NFaSifPart] = [Partnerji].[PaSifra]
	FULL JOIN [Delavci] ON [NFaktura].[NFaRefer] = [Delavci].[DeSifra]
	FULL JOIN [Drzave] ON [Partnerji].[PaSifDrzave] = [Drzave].[DrSifra]
-- join: 0 ms
WHERE	([MaticniPodatki].[MpStatus] = PARSE('0' AS INT USING 'Sl-SI'))
-- filter: 0 ms
), [RecursionSource] AS (
SELECT distinct
	[Kosovnica].[KoStZapisa] AS [KoStZapisa],
	[Kosovnica].[KoNadSifMp] AS [Parent],
	CAST('' AS VARCHAR) AS [RecID1-Parent],
	[Kosovnica].[KoSifStat] AS [KoSifStat],
	[Kosovnica].[KoPodSifMp] AS [Ident],
	CAST('Key' AS VARCHAR) AS [RecID2-Ident],
	[Kosovnica].[KoKolMateriala] AS [Kos.],
	[Kosovnica].[KoVarianta] AS [VariantaParent],
	[Kosovnica].[KoVariantaPod] AS [VariantaChild]
FROM 	[Kosovnica]
	FULL JOIN [MaticniPodatki] ON [Kosovnica].[KoPodSifMp] = [MaticniPodatki].[MpSifra]
WHERE	([Kosovnica].[KoNadSifMp] = PARSE('17893' AS INT USING 'Sl-SI'))
AND	([Kosovnica].[KoVarianta] LIKE CAST('%*%' AS VARCHAR(3)))
UNION ALL
SELECT
	[Kosovnica].[KoStZapisa] AS [KoStZapisa],
	[Kosovnica].[KoNadSifMp] AS [Parent],
	[RecursionSource].[RecID2-Ident] AS [RecID1-Parent],
	[Kosovnica].[KoSifStat] AS [KoSifStat],
	[Kosovnica].[KoPodSifMp] AS [Ident],
	CAST(CONCAT([RecursionSource].[RecID2-Ident], '-', [Kosovnica].[KoPodSifMp]) AS VARCHAR) AS [RecID2-Ident],
	[Kosovnica].[KoKolMateriala] * [RecursionSource].[Kos.] AS [Kos.],
	[Kosovnica].[KoVarianta] AS [VariantaParent],
	[Kosovnica].[KoVariantaPod] AS [VariantaChild]
FROM 	[Kosovnica]
	INNER JOIN [RecursionSource] ON [RecursionSource].[Ident] = [Kosovnica].[KoNadSifMp]
WHERE	([RecursionSource].[VariantaChild] = [Kosovnica].[KoVarianta])
)
-- recursion: 0 ms
SELECT TOP 500
	[RecursionSource].[Ident] AS [Ident],
	[FilteredSource].[KK] AS [KK],
	[FilteredSource].[Klasifikacija] AS [Klasifikacija],
	[FilteredSource].[Naziv] AS [Naziv],
	[FilteredSource].[Dolgi naziv] AS [Dolgi naziv],
	(SUM([FilteredSource].[Znesek z DDV]) - SUM([FilteredSource].[DDV])) / NULLIF(SUM([FilteredSource].[Količina]), 0) AS [C brez DDV],
	[RecursionSource].[Kos.] AS [Kos.],
	[FilteredSource].[Enota] AS [Enota],
	[FilteredSource].[Datoteka] AS [Datoteka],
	[FilteredSource].[PaNaziv] AS [PaNaziv],
	[FilteredSource].[DeIme] AS [DeIme],
	[FilteredSource].[DePriimek] AS [DePriimek],
	[FilteredSource].[Datum] AS [Datum],
	[RecursionSource].[RecID1-Parent] AS [RecID1-Parent],
	[RecursionSource].[RecID2-Ident] AS [RecID2-Ident]
-- selectouter: 0 ms
FROM 	[RecursionSource]
	INNER JOIN [FilteredSource] ON [RecursionSource].[Ident] = [FilteredSource].[Šifra]
-- join: 0 ms
WHERE	([FilteredSource].[Column1] <= 1)
-- filterafter: 0 ms
GROUP BY
	[FilteredSource].[Šifra],
	[FilteredSource].[Naziv],
	[FilteredSource].[Dolgi naziv],
	[FilteredSource].[Klasifikacija],
	[FilteredSource].[Datoteka],
	[FilteredSource].[KK],
	[FilteredSource].[MpStatus],
	[FilteredSource].[Enota 1],
	[FilteredSource].[NFaPSifra],
	[FilteredSource].[NFaPStNFak],
	[FilteredSource].[Enota],
	[FilteredSource].[Znesek z DDV],
	[FilteredSource].[DDV],
	[FilteredSource].[Količina],
	[FilteredSource].[Datum],
	[FilteredSource].[NFaSifPart],
	[FilteredSource].[NFaRefer],
	[FilteredSource].[PaNaziv],
	[FilteredSource].[PaUlicaHisnaSt],
	[FilteredSource].[PaKraj],
	[FilteredSource].[PaPostnaSt],
	[FilteredSource].[PaSifDrzave],
	[FilteredSource].[PaTelefon1],
	[FilteredSource].[PaTelefon2],
	[FilteredSource].[PaSifra],
	[FilteredSource].[DeSifra],
	[FilteredSource].[DeIme],
	[FilteredSource].[DePriimek],
	[RecursionSource].[KoStZapisa],
	[RecursionSource].[RecID1-Parent],
	[RecursionSource].[Parent],
	[RecursionSource].[KoSifStat],
	[RecursionSource].[RecID2-Ident],
	[RecursionSource].[Ident],
	[RecursionSource].[Kos.],
	[RecursionSource].[VariantaParent],
	[RecursionSource].[VariantaChild],
	[FilteredSource].[DrSifra],
	[FilteredSource].[DrNaziv],
	[FilteredSource].[DrKoda],
	[FilteredSource].[Column1]
-- groupby: 0 ms
-- having: 0 ms
ORDER BY
	[RecursionSource].[RecID2-Ident] ASC,
	[FilteredSource].[KK]
-- orderby: 0 ms
;

i9-12900k; 32GB DDR5-6000 CL36; Nvidia RTX 3080 ti;
Gigabyte Aorus z690 master; Be Quiet Dark Power 12 1000W

Zgodovina sprememb…

spremenil: korenje3 (30. mar 2020 ob 23:36)

korenje3 :: 30. mar 2020, 23:40

Opcija je seveda še en filteredsource, ki se ga joina ali naredi union, vendar bi bilo to programsko preveč balasta.

i9-12900k; 32GB DDR5-6000 CL36; Nvidia RTX 3080 ti;
Gigabyte Aorus z690 master; Be Quiet Dark Power 12 1000W

korenje3 :: 31. mar 2020, 01:09

Ok uspelo mi je. Dodal row_number samo za 1. nivo, za ostale "null AS [Column1]"

WITH [RecursionSource] AS (
SELECT
	[Kosovnica].[KoStZapisa] AS [KoStZapisa],
	[Kosovnica].[KoNadSifMp] AS [Parent],
	CAST('' AS VARCHAR) AS [RecID1-Parent],
	[Kosovnica].[KoSifStat] AS [KoSifStat],
	[Kosovnica].[KoPodSifMp] AS [Ident],
	CAST('Key' AS VARCHAR) AS [RecID2-Ident],
	CAST('1' as float) AS [Kos.],
	[Kosovnica].[KoVarianta] AS [VariantaParent],
	[Kosovnica].[KoVariantaPod] AS [VariantaChild],
	ROW_NUMBER() OVER (PARTITION BY [Kosovnica].[KoPodSifMp] ORDER BY [Kosovnica].[KoPodSifMp] DESC) AS [Column1]
FROM 	[Kosovnica]
	FULL JOIN [MaticniPodatki] ON [Kosovnica].[KoPodSifMp] = [MaticniPodatki].[MpSifra]
WHERE	([Kosovnica].[KoPodSifMp] = PARSE('17893' AS INT USING 'Sl-SI'))
AND	([Kosovnica].[KoVarianta] LIKE CAST('%*%' AS VARCHAR(3)))
UNION ALL
SELECT
	[Kosovnica].[KoStZapisa] AS [KoStZapisa],
	[Kosovnica].[KoNadSifMp] AS [Parent],
	[RecursionSource].[RecID2-Ident] AS [RecID1-Parent],
	[Kosovnica].[KoSifStat] AS [KoSifStat],
	[Kosovnica].[KoPodSifMp] AS [Ident],
	CAST(CONCAT([RecursionSource].[RecID2-Ident], '-', [Kosovnica].[KoPodSifMp]) AS VARCHAR) AS [RecID2-Ident],
	[Kosovnica].[KoKolMateriala] * [RecursionSource].[Kos.] AS [Kos.],
	[Kosovnica].[KoVarianta] AS [VariantaParent],
	[Kosovnica].[KoVariantaPod] AS [VariantaChild],
	null AS [Column1]
FROM 	[Kosovnica]
	INNER JOIN [RecursionSource] ON [RecursionSource].[Ident] = [Kosovnica].[KoNadSifMp]
WHERE	([RecursionSource].[VariantaChild] = [Kosovnica].[KoVarianta])
)
-- recursion: 0 ms
SELECT TOP 500
	[RecursionSource].[Ident] AS [Ident],
	[RecursionSource].[Kos.] AS [Kos.],
	[RecursionSource].[RecID1-Parent] AS [RecID1-Parent],
	[RecursionSource].[RecID2-Ident] AS [RecID2-Ident], [Column1]
-- selectouter: 0 ms
FROM 	[RecursionSource]
WHERE 
column1 IS NULL or column1 = 1
-- join: 0 ms
-- filterafter: 0 ms
GROUP BY
	[RecursionSource].[KoStZapisa],
	[RecursionSource].[RecID1-Parent],
	[RecursionSource].[Parent],
	[RecursionSource].[KoSifStat],
	[RecursionSource].[RecID2-Ident],
	[RecursionSource].[Ident],
	[RecursionSource].[Kos.],
	[RecursionSource].[VariantaParent],
	[RecursionSource].[VariantaChild], [Column1]
-- groupby: 0 ms
-- having: 0 ms
ORDER BY
	[RecursionSource].[RecID2-Ident] ASC
-- orderby: 0 ms
;

i9-12900k; 32GB DDR5-6000 CL36; Nvidia RTX 3080 ti;
Gigabyte Aorus z690 master; Be Quiet Dark Power 12 1000W

Vredno ogleda ...

	Tema	Sporočila	Ogledi	Zadnje sporočilo
	Tema	Sporočila	Ogledi	Zadnje sporočilo
»	tsql kolona iz vrednosti druge kolone korenje3 Oddelek: Programiranje	5	558 (425)	Lonsarg 10. mar 2020 23:13:27
»	TSQL rekurzivno korenje3 Oddelek: Programiranje	11	918 (657)	hamez66 11. feb 2020 22:15:28
»	[SQL] Filtriranje korenje3 Oddelek: Programiranje	7	914 (669)	korenje3 21. jan 2020 16:28:58
»	SQL vprašanje korenje3 Oddelek: Programiranje	8	1120 (719)	MrStein 14. jul 2019 17:15:15
»	Unattended install for Windows - beginner Klemenn Oddelek: Operacijski sistemi	6	1228 (1181)	TitanSLO 22. dec 2005 13:02:24

Več podobnih tem

Zadnje novice

Zadnji članki

Išči:

Forum » Programiranje »
SQL Recursion 2.

SQL Recursion 2.

SQL Recursion 2.

korenje3 :: 30. mar 2020, 15:57

prozac :: 30. mar 2020, 20:46

prozac :: 30. mar 2020, 20:59

korenje3 :: 30. mar 2020, 22:24

korenje3 :: 30. mar 2020, 23:07

Vazelin :: 30. mar 2020, 23:13

korenje3 :: 30. mar 2020, 23:19

Vazelin :: 30. mar 2020, 23:22

korenje3 :: 30. mar 2020, 23:23

Vazelin :: 30. mar 2020, 23:26

korenje3 :: 30. mar 2020, 23:29

korenje3 :: 30. mar 2020, 23:40

korenje3 :: 31. mar 2020, 01:09

Vredno ogleda ...

tsql kolona iz vrednosti druge kolone

TSQL rekurzivno

[SQL] Filtriranje

SQL vprašanje

Unattended install for Windows - beginner

Forum » Programiranje » SQL Recursion 2.

SQL Recursion 2.

SQL Recursion 2.

korenje3 :: 30. mar 2020, 15:57

prozac :: 30. mar 2020, 20:46

prozac :: 30. mar 2020, 20:59

korenje3 :: 30. mar 2020, 22:24

korenje3 :: 30. mar 2020, 23:07

Vazelin :: 30. mar 2020, 23:13

korenje3 :: 30. mar 2020, 23:19

Vazelin :: 30. mar 2020, 23:22

korenje3 :: 30. mar 2020, 23:23

Vazelin :: 30. mar 2020, 23:26

korenje3 :: 30. mar 2020, 23:29

korenje3 :: 30. mar 2020, 23:40

korenje3 :: 31. mar 2020, 01:09

Vredno ogleda ...

Forum » Programiranje »
SQL Recursion 2.