Forum » Programiranje » [SQL] Filtriranje
[SQL] Filtriranje
korenje3 ::
A kdo ve kako bi lahko odfiltriral izpis tako, da mi izpiše zadnjo vrstico po datumu tako, da je [Jezik] po defaultu 'SLO' in če ni da izpiše 'ANG'.
Zaželeno pa da se ne segmentira v dodaten select.
Je možno da pred prvim "WHERE" preuredi vrstice tako, da je SLO pred ANG ali obratno?
Zaželeno pa da se ne segmentira v dodaten select.
Je možno da pred prvim "WHERE" preuredi vrstice tako, da je SLO pred ANG ali obratno?
WITH [FilteredSource] AS (
SELECT TOP 1000 [MaticniPodatki].[MpSifra] AS [Šifra],
[MaticniPodatki].[MpNaziv] AS [Naziv],
[MaticniPodatki].[MpDoNaziv] AS [Dolgi naziv],
[MaticniPodatki].[MpRisbaFName] AS [Image],
REPLACE([MaticniPodatki].[MpSifEnoteMere1], CAST('KOS' AS CHAR(3)), CAST('PCS' AS CHAR(3))) AS [Unit],
[NFaktura].[Datum] AS [Datum],
[NNalP].[NNaPStNar] AS [Nabavni nalog],
[NNalP].[NNaPStPos] AS [Posx],
[NNalP].[NNaPSifra] AS [KosId],
[NNalP].[NNaPKolNar] AS [QTY],
[KomOpisMat].[KOMJezik] AS [Jezik],
[KomOpisMat].[KOMSifMp] AS [KOMSifMp],
ISNULL([KomOpisMat].[KOMOpis], [MaticniPodatki].[MpNaziv]) AS [Kom. opis],
ISNULL([KomOpisMat].[KOMDaljsiOpis], [MaticniPodatki].[MpDoNaziv]) AS [Kom. daljši opis],
[NFakP].[NFaPKol] AS [NFaPKol],
[NFakP].[NFaPZnesekDav] AS [NFaPZnesekDav],
[NFakP].[NFaPZnesekSkupaj] AS [NFaPZnesekSkupaj],
ROW_NUMBER() OVER (PARTITION BY [MaticniPodatki].[MpSifra] ORDER BY [NFaktura].[Datum] DESC) AS [Partition_MaticniPodatki_MpSifra]
FROM [NNalP]
FULL JOIN [MaticniPodatki] ON [NNalP].[NNaPSifra] = [MaticniPodatki].[MpSifra]
FULL JOIN [KomOpisMat] ON [MaticniPodatki].[MpSifra] = [KomOpisMat].[KOMSifMp]
FULL JOIN [NFakP] ON [MaticniPodatki].[MpSifra] = [NFakP].[NFaPSifra]
FULL JOIN [NFaktura] ON [NFakP].[NFaPStNFak] = [NFaktura].[NFaZapSt]
WHERE ([NNalP].[NNaPStNar] = PARSE('200044' AS INT USING 'Sl-SI'))
AND ([KomOpisMat].[KOMJezik] LIKE CAST('%SLO%' AS CHAR(5))
OR [KomOpisMat].[KOMJezik] LIKE CAST('%ANG%' AS CHAR(5))
OR [KomOpisMat].[KOMJezik] IS NULL
)
ORDER BY [KomOpisMat].[KOMJezik] DESC
)
SELECT TOP 1000
[FilteredSource].[Partition_MaticniPodatki_MpSifra] AS [Part],
[FilteredSource].[Šifra] AS [Šifra],
[FilteredSource].[Kom. opis] AS [Kom. opis],
[FilteredSource].[Kom. daljši opis] AS [Kom. daljši opis],
[FilteredSource].[Image] AS [Image],
[FilteredSource].[QTY] AS [QTY],
[FilteredSource].[Unit] AS [Unit],
( SUM([FilteredSource].[NFaPZnesekSkupaj]) - SUM([FilteredSource].[NFaPZnesekDav]) ) / NULLIF( SUM([FilteredSource].[NFaPKol]) , 0) AS [Price],
[FilteredSource].[Jezik] AS [Jezik]
FROM [FilteredSource]
--WHERE [FilteredSource].[Partition_MaticniPodatki_MpSifra] <= 1
GROUP BY
[FilteredSource].[Partition_MaticniPodatki_MpSifra],
[FilteredSource].[Šifra],
[FilteredSource].[Naziv],
[FilteredSource].[Dolgi naziv],
[FilteredSource].[Image],
[FilteredSource].[Unit],
[FilteredSource].[Datum],
[FilteredSource].[Nabavni nalog],
[FilteredSource].[Posx],
[FilteredSource].[KosId],
[FilteredSource].[QTY],
[FilteredSource].[Jezik],
[FilteredSource].[KOMSifMp],
[FilteredSource].[Kom. opis],
[FilteredSource].[Kom. daljši opis]
ORDER BY
[Posx] ASC
;
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
- spremenil: korenje3 ()
Vazelin ::
Sortiraš po jeziku. V tem primer descending in vzameš prvega. Torej če je SLO bo SLO, če ne ANG.
Problems?
Problems?
I got 99 problems but 4 usd XTZ ain't one...
HotBurek ::
Mogoče je ena opcija, da imaš dve [FilteredSource] tabeli:
- prva [FilteredSourceSLO], v kateri imaš tiste s slovenskim jezikom
- potem druga pa [FilteredSourceENG], kjer v where pogoju napišeš, da se ID ne sme nahaja v tabeli "SLO".
Se pravi, prvo zlistaj vse IDje z SLO (FilteredSourceSLO), ter potem zlistaj vse IDje z ENG, ki niso v FilteredSourceSLO.
Potem pa te dve tabele združiš:
SELECT * FROM [FilteredSourceSLO] AS `slo`
UNION
SELECT * FROM [FilteredSourceENG] AS `eng`
- prva [FilteredSourceSLO], v kateri imaš tiste s slovenskim jezikom
- potem druga pa [FilteredSourceENG], kjer v where pogoju napišeš, da se ID ne sme nahaja v tabeli "SLO".
Se pravi, prvo zlistaj vse IDje z SLO (FilteredSourceSLO), ter potem zlistaj vse IDje z ENG, ki niso v FilteredSourceSLO.
Potem pa te dve tabele združiš:
SELECT * FROM [FilteredSourceSLO] AS `slo`
UNION
SELECT * FROM [FilteredSourceENG] AS `eng`
root@debian:/# iptraf-ng
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
fatal: This program requires a screen size of at least 80 columns by 24 lines
Please resize your window
ejresnevem ::
korenje3 ::
Torej naredim novo kolono, kjer v select oštevilčim po jeziku ter nato to kolono sortiram?
Bo potem obrnlo 138 in 139 vrstico ter izpisalo SLO pred ANG?
Bo potem obrnlo 138 in 139 vrstico ter izpisalo SLO pred ANG?
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 ::
Ok če to naredim in sortiram mi razvrsti SLO pred ANG.
Problem je pa če uporabim
Ta partition mi izpiše zadnje N število cen po datumu. Je možno sočasno s tem sortirat na tak način kot želim?
Edit:
Ok tole dejansko deluje. Mislim da sem na dobri poti.
Problem je pa če uporabim
WHERE [FilteredSource].[Partition_MaticniPodatki_MpSifra] <= 1
Ta partition mi izpiše zadnje N število cen po datumu. Je možno sočasno s tem sortirat na tak način kot želim?
Edit:
ROW_NUMBER() OVER (PARTITION BY [MaticniPodatki].[MpSifra] ORDER BY [NFaktura].[Datum], iif([KomOpisMat].[KOMJezik] = 'SLO', '2', '1') DESC) AS [Partition_MaticniPodatki_MpSifra]
Ok tole dejansko deluje. Mislim da sem na dobri poti.
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 ()
klemenSLO ::
Morda bi šlo tudi tako:
ROW_NUMBER() OVER (PARTITION BY [MaticniPodatki].[MpSifra] ORDER BY [NFaktura].[Datum] as ST : s tem dobiš številšenje po datumu
dodaš tole vrstico:
count( ?ime polja?) over (partition by [MaticniPodatki].[MpSifra]) as TOTAL :s tem dobiš total glede na mp sifra
pol pa uporabiš CASE: to ti zamenja jezik le na zadnjem zapisu
case
when st=total then 'SLO'
else 'ANG'
end as jezik
ROW_NUMBER() OVER (PARTITION BY [MaticniPodatki].[MpSifra] ORDER BY [NFaktura].[Datum] as ST : s tem dobiš številšenje po datumu
dodaš tole vrstico:
count( ?ime polja?) over (partition by [MaticniPodatki].[MpSifra]) as TOTAL :s tem dobiš total glede na mp sifra
pol pa uporabiš CASE: to ti zamenja jezik le na zadnjem zapisu
case
when st=total then 'SLO'
else 'ANG'
end as jezik
Life is not measured in minutes, but in MOMENTS...
korenje3 ::
Fora je ta, da imam program, ki mi avtomatsko generira sql stavke in bi bila tvoja rešitev malo težja za vdelat v program.
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 rekurzivnoOddelek: Programiranje | 924 (663) | hamez66 |
» | SQL vprašanjeOddelek: Programiranje | 1129 (728) | MrStein |
⊘ | radiostudent.si is downOddelek: Loža | 1871 (1485) | para! |
» | Ima še kdo težave s stranjo www.zzzs.si?Oddelek: Omrežja in internet | 1531 (1308) | NoName |
» | Ubuntu 8.04 upgradeOddelek: Operacijski sistemi | 1180 (973) | R33D3M33R |