sql - 使用 3 个不同的索引选择一系列行

Question

我的数据库是这样的:

Book,Chapter,Verse,Scripture
"1","1","1","1text1"
"1","1","2","1text2"
"1","1","3","1text3"
"1","1","4","1text4"
"1","2","1","2text1"
"1","2","2","2text2"
"1","2","3","2text3"

我想选择从 1,1,1 到 1,2,3 的所有行。

但是我当前的查询不会返回第 1、1、4 行，因为 4 大于 3。

SELECT * FROM my_table WHERE 
Book >= 1 AND Book <= 1 AND 
Chapter >= 1 AND Chapter <= 2 AND
Verse >= 1 AND Verse <= 3

Answer 1

MySQL 也支持 row constructors .如果您想要(例如)1 1:3 到 1 2:2，请使用:

SELECT * FROM bible
    WHERE (1,1,3) <= (book, chapter, verse) AND (book, chapter, verse) <= (1,2,2)

对于 58 1:3 到 62 4:2，

SELECT * FROM bible
    WHERE (58,1,3) <= (book, chapter, verse) AND (book, chapter, verse) <= (62,4,2)

将包括 58 1:4，59 1:1 和 60 10:10，但不包括 62 5:1。

我找不到太多文档，但 MySQL 遵循自 SQL-92 以来为行比较设置的行为。 (注意:链接是草案版本)，特别是第 8.2 节“一般规则”7):

Let R^x and R^y be the two row value constructors of the comparison predicate and let R^x_i and R^y_i be the i-th row value constructor elements of R^x and R^y, respectively. "R^x [comp op] R^y" is true, false, or unknown as follows:
[...]
c) "R^x < R^y" is true if and only if R^x_i = R^y_i for all i < n and R^x_n < R^y_n for some n.

d) "R^x > R^y" is true if and only if R^x_i = R^y_i for all i < n and R^x_n > R^y_n for some n.

e) "R^x <= R^y" is true if and only if R^x = R^y or R^x < R^y.

f) "R^x >= R^y" is true if and only if R^x = R^y or R^x > R^y.

行比较在第 9.2 节中介绍，Joe Celko 的 SQL For Smarties (链接指向第 3 版，但同一主题存在于早期版本中)。