Keskustelu: Nettisivujen teko: MySQL: Järjestys tarjoushinnan mukaan, jos on

Osaisiko joku fiksumpi kertoa mikähän mahtaisi olla paras ja ennen kaikkea käytössä nopein koodi seuraavaan ongelmaan? Itselläni ei ole kovin hyviä ratkaisuja syntynyt.

Eli yksinkertaistettuna, taulussa on tuotteen nimi, normaalihinta ja tarjoushinta, esim:

nimike       norm.   tarjous
tuote1       40.00    0.00
tuote2       40.00   30.00
tuote3       60.00    0.00
tuote4       30.00   20.00
tuote5       20.00   10.00
tuote6       10.00    5.00

Tuotteen kohdalla vertailussa valitaan tarjoushinta jos se ei ole tyhjä, muuten normaalihinta. (tarjoushinta ei voi olla suurempi kuin norm.hinta) Kutsun tätä valinnan tulosta tosihinnaksi. Merkitsen ne tähän, mutta varsinaisessa taulussa ne siis eivät ole.

nimike       norm.   tarjous  tosihinta
tuote1       40.00    0.00     40.00
tuote2       40.00   30.00     30.00
tuote3       15.00    0.00     15.00
tuote4       30.00   20.00     20.00
tuote5       20.00   10.00     10.00
tuote6       10.00    5.00      5.00

Tuotteista tehdään hakua tosihinnan suuruuden mukaan, esimerkiksi tuotteet joiden tosihinta on 20 euroa tai yli.

Tähän asti olen saanut toimivan mySQL-kyselyn tehtyä. Mutta kun tuotteet sitten pitäisi järjestää tosihinnan mukaan, tulee ongelma. Tosihinta ei ole siis taulussa oikeasti käytössä oleva arvo, en tiedä pitäisikö kyselyssä jotenkin luoda se ja järjestää sen mukaan? Tavalliset Order by ja sarakenimi ei käy tässä.

tämä toimii ainaki MS SQL:ssä, pitäisi toimia sellaisenaan myös mySQL:ssä

select *
from (	select
			nimike,
			norm,
			tarjous,
			case when tarjous = 0 then norm when tarjous < norm then tarjous else norm end as tosihinta
		from
			taulu
	) as tosihinnat
where
	tosihinta >= 20
order by
	tosihinta desc

slowhand kirjoitti:

Tuotteen kohdalla vertailussa valitaan tarjoushinta jos se ei ole tyhjä

Esimerkeissä ei näy yhtään tyhjää (null) hintaa. Sen sijaan montaa tuotetta näyttäisi saavan tarjouksessa ilmaiseksi (hinnalla 0).

Periaatteessa jos taulu olisi kuvauksen mukainen, niin Min(Coalesce(tarjous,norm),norm) toimisi myös tuon Hycken mainitseman case -rakenteen lisäksi.

Jos näitä tarvitaan monessa paikassa, niin itse tekisin ehkä näkymän:

CREATE VIEW Tosihinnat AS
SELECT nimike, Min(Coalesce(tarjous,norm),norm) tosihinta from taulu

Sitten vaan

SELECT *
FROM Tosihinnat
WHERE tosihinta >= 20
ORDER BY tosihinta desc

Tähän sopii helposti IF-funktio:

SELECT * FROM tuotteet WHERE IF(tarjous, tarjous, norm) >= 20

Jos vielä tehdään oikein eli laitetaan tarjoushinnaksi NULL silloin, kun tarjousta ei ole, sopii selvemmin IFNULL-funktio:

SELECT * FROM tuotteet WHERE IFNULL(tarjous, norm) >= 20

NULL kannattaa sekä semanttisista syistä (tulee selvä ero, onko tarjousta vai ei) että käytännön syistä (voi tehdä 0-hintaisen tarjouksen).

Samaa lauseketta voi käyttää ORDER BY -osassa, tai jos hinta haetaan kyselyssä, voi käyttää sille annettua nimeä:

SELECT IFNULL(tarjous, norm) AS tosihinta FROM tuotteet ORDER BY tosihinta

Itse suosin COALESCEa IFNULL asemesta koska se on standardi (toimii lähes jos ei kaikissa SQL-pohjaisissa) ja siihen voi laittaa useampiakin kuin 2 kenttää.