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
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)