Ahoj,
potreboval by som pomoct s nasledujucim:
mam tabulku:
CREATE TABLE test
(number INTEGER NOT NULL,
string VARCHAR(255) NOT NULL,
count INTEGER,
UNIQUE(number), UNIQUE(string))
a potrebujem vytvorit sql query, ktora vrati:
- 1, ak je tabulka prazdna,
- number z riadka, v ktorom sa zhoduje nejaky retazec s retazcom zo stlpca string
- number z riadka, v ktorom je count == 0
- max(count) + 1 inak
moj pokus:
SELECT CASE
WHEN (COUNT(*) = 0) THEN 1
WHEN (string = ?) THEN (SELECT number FROM test WHERE string = ?)
WHEN (MIN(count) = 0) THEN (SELECT number FROM test WHERE count = 0)
ELSE (MAX(number) + 1) END
FROM test
pre tabulku s hodnotami:
1 | aaa | 1
2 | bbb | 1
3 | ccc | 1
4 | ddd | 1
nefunguje spravne, ked pre retazec 'aaa' vrati 5 namiesto 1, ale pre retazec 'ddd' uz vrati spravny vysledok (4)
Dakujem za rady
SQL select case
Pre pridávanie komentárov sa musíte prihlásiť.
Neviem, ci sa trafim, ale asi to chces takto
SELECT CASE WHEN (SELECT count(*) FROM "TEST") = 0 THEN 1 WHEN "STRING" = 'aaa' THEN (SELECT "NUMBER" FROM "TEST" WHERE "STRING" = 'aaa') WHEN (SELECT min("COUNT") FROM "TEST") = 0 THEN (SELECT "NUMBER" FROM "TEST" WHERE "COUNT" = 0) ELSE (SELECT max("NUMBER") FROM "TEST") END AS "COLUMN" FROM "TEST"BTW, pomenovavat stlpce v tabulke tzv. reserved words nie je dobra prax (preto davam TEST, NUMBER, STRING do zatvoriek)