SQL:元组比较 [英] SQL: tuple comparison
问题描述
在我当前的应用程序中,我需要能够执行这种类型的查询:
In my current application, I need to be able to do this type of query:
SELECT MIN((colA, colB, colC))
FROM mytable
WHERE (colA, colB, colC) BETWEEN (200, 'B', 'C') AND (1000, 'E', 'F')
,并在给出以下数据的情况下获得(333, 'B', 'B')
的答案:
and get the answer of (333, 'B', 'B')
, given this data:
+------+------+------+
| colA | colB | colC |
+------+------+------+
| 99 | A | A |
| 200 | A | Z |
| 200 | B | B |
| 333 | B | B |
| 333 | C | D |
| 333 | C | E |
| 333 | D | C |
| 1000 | E | G |
| 1000 | F | A |
+------+------+------+
在真正的SQL中最有效的方法是什么?请记住,这是一个示例,我的实际应用程序中的表具有不同的列和数据类型以及数亿行.我使用MySQL,如果有帮助的话.您还可以假定这些列上具有PRIMARY或UNIQUE索引.
What is the most efficient way to accomplish this in real SQL? Please keep in mind that this is a toy example, and that my actual application has tables with varying columns and data types, and hundreds of million of rows. I use MySQL, if that helps. You can also assume that these columns have a PRIMARY or UNIQUE index on them.
如果该解决方案可以轻松扩展到更多/更少的列,那就更好了.
If the solution is easily extensible to more/less columns, that's even better.
元组比较:
几个人问过,所以我应该把这个放在问题中.元组按字典顺序排序,这意味着序列与其第一个不同元素的排序相同.例如,(1,2,x)< (1,2,y)返回的值与x< y.
Several have asked so I should put this in the question. Tuples are ordered lexicographically, meaning that the sequences are ordered the same as their first differing elements. For example, (1,2,x) < (1,2,y) returns the same as x < y.
值得注意的是,SQL(或至少是mysql)正确地实现了这一点:
It's worth noting that SQL (or at least mysql) implements this correctly:
mysql> select (200, 'B', 'C') < (333, 'B', 'B') and (333, 'B', 'B') < (1000, 'E', 'F');
+--------------------------------------------------------------------------+
| (200, 'B', 'C') < (333, 'B', 'B') and (333, 'B', 'B') < (1000, 'E', 'F') |
+--------------------------------------------------------------------------+
| 1 |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)
以下是创建示例的必要SQL:
Here's the necessary SQL to create the example:
create table mytable select 333 colA, 'B' colB, 'B' colC;
insert into mytable values (200, 'B', 'B'), (333, 'C', 'D'), (1000, 'E', 'G'),
(200, 'A', 'Z'), (1000, 'F', 'A'), (333, 'C', 'E'), (333, 'D', 'C'),
(99, 'A', 'A');
alter table mytable add unique index myindex (colA, colB, colC);
添加此索引似乎会使表按字典顺序排序,这很有趣.在我们的生产系统中并非如此.
Adding this index seems to cause the table to be sorted lexicographically, which is interesting. This isn't true in our production system.
推荐答案
只需:
SELECT colA
, colB
, colC
FROM mytable
WHERE ( ('A', 'B', 'C') <= (colA, colB, colC ) )
AND ( (colA, colB, colC) <= ('D', 'E', 'F' ) )
ORDER BY colA, colB, colC
LIMIT 1
;
效果很好.而且我怀疑它也应该很快.
It works just fine. And I suspect is should be pretty fast, too.
这是等效的,但它可能具有更好的性能,具体取决于您的表:
This is equivalent but it may have better performance, depending on your tables:
SELECT m.colA
, m.colB
, m.colC
FROM mytable m
WHERE ( ('A', 'B', 'C') <= (m.colA, m.colB, m.colC) )
AND ( (m.colA, m.colB, m.colC) <= ('D', 'E', 'F') )
AND NOT EXISTS
( SELECT 1
FROM mytable b
WHERE (b.colA, b.colB, b.colC) < (m. colA, m.colB, m.colC)
AND ( ('A', 'B', 'C') <= (b.colA, b.colB, b.colC) )
);
这篇关于SQL:元组比较的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!