'WHERE (col1, col2) < 的 SQL 语法术语(val1, val2)' [英] SQL syntax term for 'WHERE (col1, col2) < (val1, val2)'

问题描述

正如我的问题所述，我想知道在 WHERE 子句中我们将具有该类型条件的查询类型称为什么，即:

As my question states, I would like to know what we call types of queries with that type of condition in the WHERE clause, i.e.:

SELECT * FROM mytable
WHERE (col1, col2) < (1, 2);

换句话说:
给我所有 col1 小于 '1' 或如果它等于 '1' 的记录，那么 col2 必须小于 '2' - 并且没有一个值为 NULL.

In other words:
Give me all records where col1 is less than '1' or if it equals '1' then col2 must be less than '2' - and none of the values are NULL.

我真的很喜欢这种类型的语法，但不知道如何引用这种类型的条件的命名约定是什么.它看起来像一个条件元组，但该名称并没有从我的搜索中给我任何信息.

I really like this type of syntax, but don't know what the naming convention is on how to refer to this type of condition. It looks like a tuple conditional but that name is not giving me anything from my searches.

我的问题源于需要知道这个语法叫什么，以便研究如何使用 Criteria API 与 Hibernate、JPA2 和 Postgres.

My question stems from needing to know what this syntax is called in order to research how to write this using Criteria API with Hibernate and JPA2 and Postgres.

我能够使用 Criteria API 编写这个，使用 CriteriaBuilder 的函数() 调用:

I was able to write this using Criteria API using CriteriaBuilder's function() call:

//Our left expression (date, id)
Expression leftVal = criteriaBuilder.function("ROW", Tuple.class,     
        from.get("date").as(java.util.Date.class),
        from.get("id").as(Long.class));

//Our right expression ex: ('2015-09-15', 32450)
ParameterExpression<Date> dateParam = criteriaBuilder.parameter(Date.class);
ParameterExpression<Long> idParam = criteriaBuilder.parameter(Long.class);
Expression rightVal = criteriaBuilder.function("ROW", Tuple.class, dateParam, idParam)

//build the first predicate using ROW expressions
Predicate predicate = criteriaBuilder.greaterThan(leftVal, rightVal);

//more query building happens
... 

//construct final query and add parameters to our param expressions
TypedQuery<MyEntity> typedQuery = em.createQuery(criteriaQuery);
typedQuery.setParameter(dateParam, current.getDate());
typedQuery.setParameter(idParam, current.getId());

在这种情况下，

current 是我检索的记录，作为我们想要在 BEFORE 或 AFTER 之前获取记录的行.在这个例子中，我按照 greaterThan 函数调用的说明进行操作.

current in this case is the record I retrieve as the row we want to get records BEFORE or AFTER. In this example I do after as noted by the greaterThan function call.

推荐答案

常见的短期术语就是 行值".或行值比较"为您演示的操作.自 SQL-92 (!) 以来，该功能一直在 SQL 标准中.Postgres 是目前唯一在各个方面都支持它的主要 RDBMS - 特别是在优化索引支持方面.

The common short term is just "Row values". Or "Row value comparison" for the operation you demonstrate. That feature has been in the SQL standard since SQL-92 (!). Postgres is currently the only major RDBMS that supports it in all aspects - especially also with optimal index support.

特别是表达式 (col1, col2) <(1, 2) 只是 ROW(col1, col2) < 的简写Postgres 中的 ROW(1, 2).表达式 ROW(col1, col2) 也称为 行构造函数 - 就像 ARRAY[col1, col2] 是一个 数组构造函数.

In particular, the expression (col1, col2) < (1, 2) is just shorthand for ROW(col1, col2) < ROW(1, 2) in Postgres. The expression ROW(col1, col2) is also called row constructor - just like ARRAY[col1, col2] is an array constructor.

它是更冗长的等效表达式的缩写:

It is conveniently short for the more verbose, equivalent expression:

col1 < 1 OR (col1 = 1 AND col2 < 2)

... 并且 Postgres 可以为此使用 (col1, col2) 或 (col1 DESC, col2 DESC) 上的索引.

... and Postgres can use an index on (col1, col2) or (col1 DESC, col2 DESC) for this.

并且明显区别于 (!)

And notably distinct from (!)

col1 < 1 AND  AND col2 < 2

考虑示例:(1,1) ...

这是 Markus Winand 的演示文稿，详细讨论了分页功能:

Here is a presentation by Markus Winand that discusses the feature for pagination in detail:

"PostgreSQL 分页完成方式"在 use-the-index-luke.com 上.

行值比较从第 20 页开始.我提到的支持矩阵在第 45 页.

Row value comparison starts on page 20. The support matrix I have been referring to is on page 45.

_{我绝不隶属于我引用的任何来源.}

这篇关于'WHERE (col1, col2) < 的 SQL 语法术语(val1, val2)'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！