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

问题描述

正如我的问题所述，我想知道我们在 WHERE 子句中使用该类型的条件调用的查询类型，即：

  SELECT * FROM mytable 
 WHERE（col1，col2）< （1,2）; 
  

换句话说：

给我所有记录其中 col1 小于'1'或者等于'1'，那么 col2 必须小于'2' - 并且这些值都不为NULL。

我非常喜欢这种类型的语法，但不知道命名约定是什么关于如何引用这种类型的条件。它看起来像一个条件元组，但这个名字并没有给我任何东西从我的搜索。

我的问题源于需要知道什么语法被称为研究如何使用 Criteria API 使用Hibernate和JPA2以及Postgres编写此代码。

编辑

我可以使用Criteria API使用

  //我们的左表达式（date，id） 
表达式leftVal = criteriaBuilder.function（ROW，Tuple.class，
 from.get（date）。as（java.util.Date.class），
 from.get （ ID）作为（Long.class））; 
 
 //我们的正确表达式ex：（'2015-09-15'，32450）
 ParameterExpression< Date> dateParam = criteriaBuilder.parameter（Date.class）; 
 ParameterExpression< Long> idParam = criteriaBuilder.parameter（Long.class）; 
表达式rightVal = criteriaBuilder.function（ROW，Tuple.class，dateParam，idParam）
 
 //使用ROW表达式构建第一个谓词
谓词predicate = criteriaBuilder.greaterThan （leftVal，rightVal）; 
 
 //更多查询构建发生
 ... 
 
 //构造最终查询并将参数添加到我们的参数表达式
 TypedQuery< MyEntity> typedQuery = em.createQuery（criteriaQuery）; 
 typedQuery.setParameter（dateParam，current.getDate（））; 
 typedQuery.setParameter（idParam，current.getId（））; 
  

当前在这种情况下是记录I检索作为我们想要得到记录之前或之后的记录。在这个例子中，我按照 greaterThan 函数调用的指示完成了。

p>常见的短期内容只是行值。或者行值比较以显示您的操作。自 SQL-92 （！）开始，该功能已处于SQL标准中。 Postgres是目前唯一支持它的主要RDBMS，尤其是在支持最佳索引的情况下。特别是，表达式（col1， col2） （1，2）只是 ROW（col1，col2）的简写< ROW（1，2）在Postgres中。
表达式 ROW（col1，col2）也被称为行构造函数 - 就像 ARRAY [col1，col2] 是一个< a href =http://www.postgresql.org/docs/current/interactive/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS =nofollow>数组构造函数。

以下是Markus Winand的介绍，详细讨论了分页功能：

分页完成PostgreSQL的方式关于use-the-index-luke。

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

_{我不属于我引用的任何消息来源。}

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);

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.

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.

EDIT

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 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.

解决方案

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.

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.

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

"Pagination done the PostgreSQL way" on use-the-index-luke.com.

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

_{I am in no way affiliated to any of the sources I quoted.}

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