我使用的是Oracle SQL Developer,执行此命令后,只显示以K
和L
开头的名称。为什么不显示以M
开头的名称?
SELECT DISTINCT(names) FROM STUDENTS WHERE names BETWEEN 'K%' AND 'M%' ORDER BY 1 DESC;
当我执行时:
SELECT DISTINCT(names) FROM STUDENTS WHERE names BETWEEN 'K%' AND 'N%' ORDER BY 1 DESC;
出现了K
、L
、M
,但以N
开头的名称没有出现。BETWEEN
是包容性的,那么问题出在哪里呢?
这非常有效:
SELECT * FROM STUDENTS WHERE year BETWEEN 1 AND 3;
between谓词并不意味着like
,因此您只是在比较字符串。写这个代替
-- Names with starting letters K, L, M, N
names >= 'K' AND names < 'O'
或者,使用正则表达式
-- Names with starting letters K, L, M
REGEXP_LIKE (names, '^[K-M]')
BETWEEN是包容性的,那么问题出在哪里呢?
通配符"%"与此处的LIKE
不起作用,字典顺序应用
SELECT CASE WHEN 'Mango' BETWEEN 'K%' AND 'M%' THEN 'true' ELSE 'false' END AS result
FROM dual
-- false
SELECT CASE WHEN 'Mango' >= 'K%' AND 'Mango' <= 'M%' THEN 'true' ELSE 'false' END
AS result
FROM dual
--false
SELECT CASE WHEN 'Mango' >= 'K%' THEN 'true' ELSE 'false' END AS result
FROM dual
-- true
SELECT CASE WHEN 'Mango' <= 'M%' THEN 'true' ELSE 'false' END AS result
FROM dual
-- false
db<gt;小提琴演示
这非常有效:
SELECT * FROM STUDENTS WHERE year BETWEEN 1 AND 3;
以类似方式重写(尽管它不是SARGable(:
SELECT DISTINCT(names)
FROM STUDENTS
WHERE SUBSTR(names,1,1) BETWEEN 'K' AND 'M'
ORDER BY 1 DESC;
Lukas解释了这个问题——将LIKE
模式与字符串混淆。另一种解决方案是:
WHERE SUBSTR(names, 1, 1) BETWEEN 'K' AND 'M'
注意,在提到的解决方案中,只有直接比较:
WHERE names >= 'K' AND names < 'O'
将使用索引。但是,由于值的范围如此之广,索引可能对查询毫无用处。