Forum » Programiranje » SQL Recursion 2.
SQL Recursion 2.
korenje3 ::
Torej imam tak ukaz:
Izstopni podatki so pa taki:
Kako bi naredil, da mi [RecID2-Ident] izpiše samo 1 vrstico. Torej če je večkrat "Key" da bo samo prva vrstica.
-- 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
Gigabyte Aorus z690 master; Be Quiet Dark Power 12 1000W
prozac ::
predvidevam, da ti prvi del poizvedbe vrne več zapisov. Je rešitev, da v prvem delu union all uporabiš distinct?
druga manj elegantna varianta, ki morda niti ni pravilna, da na koncu nad izhodom narediš poizvedbo in tam uporabiš distinct (tega bi se izogibal)
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 ()
prozac ::
lahko morda namesto rekurzije uporabiš connect by prior
sedaj nimam dostopa do baze, da bi sprobal če napisano res dela
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 ::
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.
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
Gigabyte Aorus z690 master; Be Quiet Dark Power 12 1000W
Zgodovina sprememb…
- spremenil: korenje3 ()
korenje3 ::
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
Gigabyte Aorus z690 master; Be Quiet Dark Power 12 1000W
Vazelin ::
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 ::
torej vejica samostojno v novi vrstici?
vejice dajam spredaj sicer samo kadar testiram. sql stavek je pa avtomatsko generiran.
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
Gigabyte Aorus z690 master; Be Quiet Dark Power 12 1000W
Zgodovina sprememb…
- spremenil: korenje3 ()
Vazelin ::
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
[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 ()
korenje3 ::
i9-12900k; 32GB DDR5-6000 CL36; Nvidia RTX 3080 ti;
Gigabyte Aorus z690 master; Be Quiet Dark Power 12 1000W
Gigabyte Aorus z690 master; Be Quiet Dark Power 12 1000W
Zgodovina sprememb…
- spremenil: korenje3 ()
Vazelin ::
a ta program si ti naredu? izgleda ogromno opcij
I got 99 problems but 4 usd XTZ ain't one...
korenje3 ::
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:
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
Gigabyte Aorus z690 master; Be Quiet Dark Power 12 1000W
Zgodovina sprememb…
- spremenil: korenje3 ()
korenje3 ::
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
Gigabyte Aorus z690 master; Be Quiet Dark Power 12 1000W
korenje3 ::
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
Gigabyte Aorus z690 master; Be Quiet Dark Power 12 1000W
Vredno ogleda ...
Tema | Ogledi | Zadnje sporočilo | |
---|---|---|---|
Tema | Ogledi | Zadnje sporočilo | |
» | tsql kolona iz vrednosti druge koloneOddelek: Programiranje | 547 (414) | Lonsarg |
» | TSQL rekurzivnoOddelek: Programiranje | 902 (641) | hamez66 |
» | [SQL] FiltriranjeOddelek: Programiranje | 901 (656) | korenje3 |
» | SQL vprašanjeOddelek: Programiranje | 1107 (706) | MrStein |
» | Unattended install for Windows - beginnerOddelek: Operacijski sistemi | 1210 (1163) | TitanSLO |