SELECT
parameter
FROM simple
WHERE 25 BETWEEN
CASE
WHEN instr(condition_value,'List(')
THEN CAST(substr(condition_value,length('List(')+1,(instr(condition_value,',')-length('List('))-1) AS INTEGER)
ELSE CAST(condition_value AS INTEGER)
END
AND
CASE
WHEN instr(condition_value,'List(')
THEN CAST(substr(condition_value,instr(condition_value,',')+ 1,instr(condition_value,')')-instr(condition_value,',')-1) AS INTEGER)
ELSE CAST(condition_value AS INTEGER)
END
;
但是,更好、更灵活、更简单的解决方案是引入另一列,以便可以指定一系列值。 例如:
CREATE TABLE IF NOT EXISTS better (id INTEGER PRIMARY KEY, cvlow, cvhigh, parameter);
以下是两者的演示:
DROP TABLE IF EXISTS simple;
CREATE TABLE IF NOT EXISTS simple (id INTEGER PRIMARY KEY, condition_value TEXT, parameter INTEGER);
INSERT INTO simple (condition_value,parameter) VALUES
('List(1,30)',40),
('List(25,25)',41),
('List(-100,25)',42),
('List(-1,-25)',43),
('List(1,2500000)',44),
(44,50)
;
SELECT
parameter
FROM simple
WHERE 25 BETWEEN
CASE
WHEN instr(condition_value,'List(')
THEN CAST(substr(condition_value,length('List(')+1,(instr(condition_value,',')-length('List('))-1) AS INTEGER)
ELSE CAST(condition_value AS INTEGER)
END
AND
CASE
WHEN instr(condition_value,'List(')
THEN CAST(substr(condition_value,instr(condition_value,',')+ 1,instr(condition_value,')')-instr(condition_value,',')-1) AS INTEGER)
ELSE CAST(condition_value AS INTEGER)
END
;
DROP TABLE IF EXISTS simple;
/* BETTER simpler and with greater flexibility*/
CREATE TABLE IF NOT EXISTS better (id INTEGER PRIMARY KEY, cvlow, cvhigh, parameter);
INSERT INTO better (cvlow,cvhigh,parameter) VALUES
(1,30,40),
(25,25,41),
(-100,25,42),
(-1,-25,43),
(1,2500000,44),
(44,44,50) /* the equivalent of the single value */
;
SELECT parameter FROM better WHERE 25 BETWEEN cvlow AND cvhigh;
DROP TABLE IF EXISTS better;
1条答案
按热度按时间ds97pgxw1#
您可以使用*(见下文更好的内容)*:-
但是,更好、更灵活、更简单的解决方案是引入另一列,以便可以指定一系列值。
例如:
以下是两者的演示:
结果是相同的:
和