为什么SQL中没有“第一个大于/小于[或等于]”的比较运算符？

时间：2022-09-18 13:00:35

I am considering the proposal of 4 new comparison operators in SQL. These are similar to the >, <, >= and <= operators, but are true only if the value of each operand is, among all values satisfying the inequality, the one closest to the value of the other operand. Since one value is almost at the other value, I have come to the conclusion (after realizing that there is no first keyword, and after discarding the unique keyword) that a good choice would be to define these 4 new operators:

我正在考虑在SQL中提出4个新的比较运算符。它们类似于>，<，> =和<=运算符，但只有当每个操作数的值在满足不等式的所有值中最接近另一个操作数的值时才为真。由于一个值​​几乎处于另一个值，我得出结论（在意识到没有第一个关键字，并且在丢弃唯一关键字之后），一个好的选择是定义这4个新运算符：

  • a @> b : true if a > b and no a' < a satisfies a' > b and no b' > b satisfies a > b'
  • a @> b：如果a> b且没有' b而没有b'> b满足a> b'，则为真 满足'>
  • a @< b : true if b @> a
  • a @ a，则为true ：如果b>
  • a @>= b : true if a ≥ b and no a' < a satisfies a' ≥ b and no b' > b satisfies a ≥ b'
  • a @> = b：如果a≥b且没有a' b满足a≥b'，则为真 满足'≥b且没有b'>
  • a @<= b : true if b @>= a
  • a @ <= b：如果b @> = a，则为true

The question is: is there some good reason why operators like these don't exist already?

问题是：是否有一些很好的理由说明为什么这样的运营商不存在？

(2014-03-20) I reformulate the question, because the above formulation apparently isn't clear enough:

（2014-03-20）我重新提出这个问题，因为上述表述显然不够明确：

Is there a reason why operators like these should not exist?

这样的运营商不应该存在的原因是什么？

The following examples are intended as a starting point for finding out where the problems with the @... operators could lie. I will use 3 MySQL tables:

以下示例旨在找出@ ...运算符的问题所在的起点。我将使用3个MySQL表：

create table ta (id int auto_increment, ca char, primary key(id), unique index(ca));
create table tb (id int auto_increment, cb char, primary key(id), index(cb));
create table tc (id int auto_increment, cc char, primary key(id));
insert into ta (ca) values ('A'),('E'),('I'),('O'),('U');
insert into tb (cb) values ('C'),('D'),('E'),('F'),('F'),('M'),('N'),('O'),('Z');
insert into tc (cc) values ('C'),('D'),('E'),('F'),('F'),('M'),('N'),('O'),('Z');

Example #1

When columns have unique values, the effect of the @... operators can be obtained by limiting the output of queries or subqueries to 1 row, although with a somewhat more clumsy syntax:

当列具有唯一值时，可以通过将查询或子查询的输出限制为1行来获得@ ...运算符的效果，尽管语法稍微笨拙：

?> select * from ta where ca @> 'B'; -- currently not valid, equivalent to:
!> select * from ta where ca > 'B' order by ca limit 1;
+----+------+
| id | ca   |
+----+------+
|  2 | E    |
+----+------+

(limit 1 is specific to MySQL, MariaDB, PostgreSQL, etc., other RDBMSs have select top 1, where rownum = 1, etc.)

（限制1特定于MySQL，MariaDB，PostgreSQL等，其他RDBMS选择前1，其中rownum = 1等）

In table ta we have a unique index on column ca. This index can be exploited to get to the selected value at the same speed as for ca = 'E'. The optimizer may realize this, but if it doesn't, data structures may be set up for an unneeded scan starting at the selected value (MySQL's explain says that this is a range type query).

在表ta中，我们在列ca上有一个唯一的索引。可以利用该索引以与ca ='E'相同的速度获得所选值。优化器可能会意识到这一点，但如果没有，则可以为从所选值开始的不需要的扫描设置数据结构（MySQL的解释说这是范围类型查询）。

Example #2

When columns have non-unique values, limiting output rows is useless, and the syntax becomes even more clumsy:

当列具有非唯一值时，限制输出行是无用的，语法变得更加笨拙：

?> select * from tb where cb @> 'E'; -- currently not valid, equivalent to:
!> select * from tb where cb = (select min(cb) from tb where cb > 'E');
+----+------+
| id | cb   |
+----+------+
|  4 | F    |
|  5 | F    |
+----+------+

Luckily, if I correctly read the output of explain, MySQL is smart enough to optimize the subquery away, but if it weren't, the index would be used twice instead of once.

幸运的是，如果我正确地阅读了解释的输出，那么MySQL足够聪明以优化子查询，但如果不是，那么索引将被使用两次而不是一次。

In the case of the table tc, which has no index on column cc, MySQL makes two table scans. This is understandable, since a single table scan would mean using an unknown amount of storage for the temporary result.

在表tc的情况下，MySQL没有列cc的索引，MySQL进行了两次表扫描。这是可以理解的，因为单个表扫描意味着对临时结果使用未知量的存储。

Example #3

Suppose you need all pairs consisting of a value and its successor value:

假设您需要包含值及其后继值的所有对：

?> select t1.ca as c1, t2.ca as c2
   from ta t1
   join ta t2 on t1.ca @< t2.ca; -- currently not valid, equivalent to:
!> select t1.ca as c1, t2.ca as c2
   from ta t1
   join ta t2 on t2.ca = (select min(ca) from ta where ca > t1.ca);
+------+------+
| c1   | c2   |
+------+------+
| A    | E    |
| E    | I    |
| I    | O    |
| O    | U    |
+------+------+

If I read the output of explain correctly, the MySQL optimizer is not able to do without the correlated subquery, while we humans would know better. Maybe with the help of special handling of the @... operators wired in, the optimizer would do a single scan?

如果我正确地阅读了解释的输出，那么MySQL优化器不能没有相关子查询，而我们人类会更清楚。也许借助特殊处理的@ ...运算符连接，优化器会进行单次扫描吗？

Example #4

This is similar, but across two tables, one of which has a non-unique index:

这是类似的，但是在两个表中，其中一个表具有非唯一索引：

?> select * from ta join tb on ca @< cb; -- currently not valid, equivalent to:
!> select * from ta join tb on cb = (select min(cb) from tb where cb > ca);
+----+------+----+------+
| id | ca   | id | cb   |
+----+------+----+------+
|  1 | A    |  1 | C    |
|  2 | E    |  4 | F    |
|  2 | E    |  5 | F    |
|  3 | I    |  6 | M    |
|  4 | O    |  9 | Z    |
|  5 | U    |  9 | Z    |
+----+------+----+------+

Here too, the MySQL optimizer does not optimize away the subquery, although (maybe with a hint by the @<) it could.

在这里，MySQL优化器也没有优化掉子查询，尽管（也许是@ <的提示）它可以。< p>

Example #5

(Added on 2014-03-20.) The @... operators seem to make sense wherever their @-less counterparts do. Here is a contrived example of an expression in a where condition:

（在2014-03-20上添加。）@ ...运算符似乎在他们的@ -less同行所做的任何地方都有意义。这是一个在where条件中表达式的人为例子：

?> select * from ta join tb
   where round((ascii(ca)+ascii(cb))/2) @> ascii('E');
   -- currently not valid, equivalent to:
!> select * from ta join tb
   where round((ascii(ca)+ascii(cb))/2) = (
      select min(round((ascii(ca)+ascii(cb))/2)) from ta, tb
      where      round((ascii(ca)+ascii(cb))/2) > ascii('E')
   );
+----+------+----+------+
| id | ca   | id | cb   |
+----+------+----+------+
|  3 | I    |  1 | C    |
|  2 | E    |  4 | F    |
|  2 | E    |  5 | F    |
+----+------+----+------+

Example #6

... and this is another example, this time of a select expression:

...这是另一个例子，这次选择表达式：

?> select *, cb @< ca
   from tb, ta; -- currently not valid, equivalent to:
!> select *, ifnull(cb = (select max(cb) from tb where cb < ca), 0) as 'cb @< ca'
   from tb, ta;
+----+------+----+------+----------+
| id | cb   | id | ca   | cb @< ca |
+----+------+----+------+----------+
|  1 | C    |  1 | A    |        0 |
|  1 | C    |  2 | E    |        0 |
|  1 | C    |  3 | I    |        0 |
|  1 | C    |  4 | O    |        0 |
|  1 | C    |  5 | U    |        0 |
|  2 | D    |  1 | A    |        0 |
|  2 | D    |  2 | E    |        1 |
| -- (omitting rows with cb @< ca equal to 0 from here on)
|  4 | F    |  3 | I    |        1 |
|  5 | F    |  3 | I    |        1 |
|  7 | N    |  4 | O    |        1 |
|  8 | O    |  5 | U    |        1 |

I am aware of the following caveats:

我知道以下警告：

Caveat #1

The @... operators are "non-local", because they need the knowledge of all possible values of their operands. This seems not to be a problem in all conditions of the kinds shown in the above examples, but could be a problem in other places (although I haven't found an example yet that could not be solved by an additional subquery).

@ ...运算符是“非本地的”，因为它们需要了解其操作数的所有可能值。这似乎不是上述示例中所示类型的所有条件下的问题，但在其他地方可能是一个问题（尽管我还没有找到一个其他子查询无法解决的例子）。

Caveat #2

The @... operators, unlike their @-less counterparts, are not transitive. They share this property with the <> operator, though.

@ ...运算符与@ -less对应运算符不同，不具有传递性。但是，它们与<>运算符共享此属性。

Caveat #3

Fully exploiting the @... operators could mean introducing new index and table access types (as discussed in the examples).

完全利用@ ...运算符可能意味着引入新的索引和表访问类型（如示例中所讨论的）。

Please note that this question is not meant as a starting point for a discussion. I am looking for the reasons why something like the @... operators is not in the standard nor in any SQL dialect known to me - I expect these reasons to be related to some problem with the definition and/or implementation of these operators that I have overlooked.

请注意，这个问题不是讨论的起点。我正在寻找为什么类似@ ...运算符不在标准中，也不在我所知的任何SQL方言中的原因 - 我希望这些原因与这些运算符的定义和/或实现的某些问题有关。我忽略了。

I know that one reason is "Occam's razor" (pluralitas non est ponenda sine necessitate), but, as I have tried to show above, pluralitas here also brings some advantages (conciseness and ease of optimization). I am looking for stronger reasons.

我知道其中一个原因是“奥卡姆的剃刀”（多元化的非正式的必要条件），但是，正如我试图在上面展示的那样，这里的多元化也带来了一些优点（简洁和易于优化）。我正在寻找更有力的理由。

(2014-03-31) @>, @<, @>= and @<= could become |>, |<, |>= and |<= or similar (read: first greater/less [equal]), in order not to collide with established usages of the @ identifier prefix.

（2014-03-31）@>，@ <，@> =和@ <=可以成为|>，| <，|> =和| <=或类似（读：先大于/小[等于]），为了不与@ identifier前缀的既定用法冲突。

3 个解决方案

#1

12  

I am confused as to whether this is an appropriate forum for this question. But, the reason these operators do not exist is that they are not particularly useful and other ANSI SQL functionality takes their place.

我很困惑这是否是这个问题的适当论坛。但是，这些运算符不存在的原因是它们不是特别有用，而其他ANSI SQL功能取而代之。

First, the comparison operators in the on clause are, in every database I know, available in the where and case clauses. It is unclear how these operators would be used in these contexts.

首先，在我知道的每个数据库中，on子句中的比较运算符在where和case子句中可用。目前还不清楚这些运营商将如何在这些背景下使用。

Second, the operators don't specify what to do in the case of ties. Return all rows? But that would return multiple rows when the user of such an operator would expect only one row.

其次，运营商没有具体说明在关系的情况下该做什么。返回所有行？但是当这样的运算符的用户只期望一行时，这将返回多行。

Third, ANSI standard functionality, such as row_number() can generate equivalent results. Although it might not be as optimal for this particular problem, it is more general. And standard.

第三，ANSI标准功能，例如row_number（）可以生成相同的结果。虽然它可能不是这个特定问题的最佳选择，但它更为通用。和标准。

By the way, Postgres has a nice capability with distinct on(), which is often more efficient than the analytic function equivalent.

顺便说一句，Postgres有一个很好的功能，具有明显的on（），这通常比分析功能等效。

I have secretly wanted a new join type, lookup join, that would fail if more than one record matched. However, I'm not so sure that the entire language should be changed for this purpose.

我秘密地想要一个新的连接类型，查找连接，如果匹配多个记录，则会失败。但是，我不太确定为此目的应该改变整个语言。

#2

8  

Putting aside for a moment the merits of your proposed addition, you actually had only one question:

暂时搁置你提议的补充的优点，你实际上只有一个问题：

The question is: is there some good reason why operators like these don't exist already?

问题是：是否有一些很好的理由说明为什么这样的运营商不存在？

The same reason for any other absent feature: because no one implemented it yet.

任何其他缺席功能的原因相同：因为还没有人实现它。

Now, the reason that no one implemented it is likely that no one felt a burning need to have a special operator for it, because of a combination of the following:

现在，由于以下各项的组合，没有人实现它的原因很可能是没有人觉得需要一个特殊的操作员。

  • The cases where it is useful are uncommon, and polluting a language with a new operator for every conceivable task is not a good idea.
  • 它有用的情况并不常见，并且每个可以想象的任务用新操作符污染语言都不是一个好主意。
  • The workarounds to achieve the same task with existing syntax are easy and adequate.
  • 使用现有语法实现相同任务的变通方法既简单又充足。
  • If people did feel the feature would be worth implementing, they might use a different syntax than one using @ as part of the operators.
  • 如果人们确实认为该功能值得实现，他们可能会使用与使用@作为运算符一部分的语法不同的语法。

Being concise is great of course, but that doesn't seem to be the goal of SQL in general (otherwise SQL would be written in relational algebra notation).

简洁是很好的，但这似乎不是SQL的目标（否则SQL将用关系代数表示法编写）。

Nonstandard clauses like LIMIT (MySQL, PostgreSQL, SQLite), or TOP (Microsoft, Sybase), or ROWNUM (Oracle), or FIRST (Informix, InterBase) suffice for most users.

LIMIT（MySQL，PostgreSQL，SQLite）或TOP（Microsoft，Sybase）或ROWNUM（Oracle）或FIRST（Informix，InterBase）等非标准条款足以满足大多数用户的需求。

Standard syntax using ordered window functions is in fact the solution defined by the SQL:2003 specification. I realize the window functions are not supported by MySQL yet, but they are supported in virtually every other brand of RDBMS.

使用有序窗口函数的标准语法实际上是SQL：2003规范定义的解决方案。我意识到MySQL还不支持窗口功能，但几乎所有其他品牌的RDBMS都支持它们。

If you're proposing adding this functionality specifically for MySQL, it would be better to make it more compatible with other brands using standard syntax, rather than introduce new nonstandard syntax.

如果您建议专门为MySQL添加此功能，最好使用标准语法使其与其他品牌更兼容，而不是引入新的非标准语法。

Now to your other question:

现在回答你的另一个问题：

I'm looking for the REAL problems these @... operators could have.

我正在寻找这些@运营商可能遇到的真正问题。

One comes to mind immediately: it's not general-purpose.

人们立即浮现在脑海中：它不是通用的。

It handles the case where you want only the single row immediately following the comparison operand. But it doesn't handle many other cases that ordered window functions could satisfy:

它处理只需要紧跟比较操作数后面的单行的情况。但它没有处理有序窗口函数可以满足的许多其他情况：

  • Return the next two rows in a window.
  • 返回窗口中的下两行。
  • Return the last row in a window.
  • 返回窗口中的最后一行。
  • Return the top three rows by rank rather than by row number.
  • 按排名而不是按行号返回前三行。
  • etc.
  • 等等

It also introduces a new piece of punctuation that has no precedent in standard SQL, and which may conflict with some vendor additions, like prefixing user variables in MySQL or prefixing parameters in Microsoft SQL Server.

它还引入了一个新的标点符号，它在标准SQL中没有先例，并且可能与某些供应商的添加冲突，例如在MySQL中添加前缀用户变量或在Microsoft SQL Server中添加前缀参数。

I tested your examples in PostgreSQL 8.4, which supports window functions. I know that's not the current version of PostgreSQL, but it is the default version in the CentOS repo, and it's adequate to demonstrate window functions.

我在PostgreSQL 8.4中测试了你的例子，它支持窗口函数。我知道这不是PostgreSQL的当前版本，但它是CentOS repo中的默认版本，它足以演示窗口函数。

Example #1

示例＃1

select * 
from (select *, row_number() over (order by ca) as rn from ta where ca > 'B') as t
where rn = 1;

 id | ca | rn 
----+----+----
  2 | E  |  1

Example #2

例＃2

select * 
from (select *, rank() over (order by cb) as rk from tb where cb > 'E') as t
where rk = 1;

 id | cb | rk 
----+----+----
  5 | F  |  1
  4 | F  |  1

Example #3

例＃3

select t1.ca as c1, t2.ca as c2
from (select ca, row_number() over (order by ca) AS rn from ta) as t1
join (select ca, row_number() over (order by ca) AS rn from ta) as t2
  on t1.rn+1 = t2.rn;

 c1 | c2 
----+----
 A  | E
 E  | I
 I  | O
 O  | U

Example #4

例＃4

select *
from (select ta.id, ta.ca, tb.id, tb.cb, 
    rank() over (partition by ca order by cb) AS rk 
    from ta join tb on ca < cb) as t
where rk = 1;

 id | ca | id | cb | rn 
----+----+----+----+----
  1 | A  |  1 | C  |  1
  2 | E  |  5 | F  |  1
  2 | E  |  4 | F  |  1
  3 | I  |  6 | M  |  1
  4 | O  |  9 | Z  |  1
  5 | U  |  9 | Z  |  1

#3

1  

Perhaps this is not truly a question for somewhere like this. It does sound like a nice function, I must admit, but there are other ways of doing it. (Though I guess a lot of other functions have been added that had other ways f doing it too!)

也许对于像这样的地方来说，这不是一个真正的问题。我必须承认，它确实听起来像一个很好的功能，但还有其他方法可以做到这一点。 （虽然我想其他许多函数都已经添加了，其他方法也可以这样做！）

I suppose the true answers to something like this could be something like:

我想这样的事情的真正答案可能是这样的：

A. Demand - Is it worth the developers time to add functions like this to SQL if they feel that very few people would use them?

A.需求 - 如果开发人员觉得很少有人会使用它们，那么开发人员是否值得将这样的函数添加到SQL中？

B. Functionality - Does it actually allow the developer to DO MORE, or is it just another way of getting to the same goal?

B.功能 - 它是否真的允许开发人员做得更多，或者它只是达到同一目标的另一种方式？

C. Laziness - Can they actually be bothered to develop something like this?

C.懒惰 - 他们真的会*开发这样的东西吗？

The best way to get an answer to this would be to submit it to their development team and see what their response is, if enough people could get involved in asking for something like this to be implemented, then it could... power of the majority!

获得答案的最佳方法是将其提交给他们的开发团队，看看他们的回答是什么，如果有足够多的人可以参与要求实现这样的事情，那么它可以...多数！

#1

12  

I am confused as to whether this is an appropriate forum for this question. But, the reason these operators do not exist is that they are not particularly useful and other ANSI SQL functionality takes their place.

我很困惑这是否是这个问题的适当论坛。但是，这些运算符不存在的原因是它们不是特别有用，而其他ANSI SQL功能取而代之。

First, the comparison operators in the on clause are, in every database I know, available in the where and case clauses. It is unclear how these operators would be used in these contexts.

首先，在我知道的每个数据库中，on子句中的比较运算符在where和case子句中可用。目前还不清楚这些运营商将如何在这些背景下使用。

Second, the operators don't specify what to do in the case of ties. Return all rows? But that would return multiple rows when the user of such an operator would expect only one row.

其次，运营商没有具体说明在关系的情况下该做什么。返回所有行？但是当这样的运算符的用户只期望一行时，这将返回多行。

Third, ANSI standard functionality, such as row_number() can generate equivalent results. Although it might not be as optimal for this particular problem, it is more general. And standard.

第三，ANSI标准功能，例如row_number（）可以生成相同的结果。虽然它可能不是这个特定问题的最佳选择，但它更为通用。和标准。

By the way, Postgres has a nice capability with distinct on(), which is often more efficient than the analytic function equivalent.

顺便说一句，Postgres有一个很好的功能，具有明显的on（），这通常比分析功能等效。

I have secretly wanted a new join type, lookup join, that would fail if more than one record matched. However, I'm not so sure that the entire language should be changed for this purpose.

我秘密地想要一个新的连接类型，查找连接，如果匹配多个记录，则会失败。但是，我不太确定为此目的应该改变整个语言。

#2

8  

Putting aside for a moment the merits of your proposed addition, you actually had only one question:

暂时搁置你提议的补充的优点，你实际上只有一个问题：

The question is: is there some good reason why operators like these don't exist already?

问题是：是否有一些很好的理由说明为什么这样的运营商不存在？

The same reason for any other absent feature: because no one implemented it yet.

任何其他缺席功能的原因相同：因为还没有人实现它。

Now, the reason that no one implemented it is likely that no one felt a burning need to have a special operator for it, because of a combination of the following:

现在，由于以下各项的组合，没有人实现它的原因很可能是没有人觉得需要一个特殊的操作员。

  • The cases where it is useful are uncommon, and polluting a language with a new operator for every conceivable task is not a good idea.
  • 它有用的情况并不常见，并且每个可以想象的任务用新操作符污染语言都不是一个好主意。
  • The workarounds to achieve the same task with existing syntax are easy and adequate.
  • 使用现有语法实现相同任务的变通方法既简单又充足。
  • If people did feel the feature would be worth implementing, they might use a different syntax than one using @ as part of the operators.
  • 如果人们确实认为该功能值得实现，他们可能会使用与使用@作为运算符一部分的语法不同的语法。

Being concise is great of course, but that doesn't seem to be the goal of SQL in general (otherwise SQL would be written in relational algebra notation).

简洁是很好的，但这似乎不是SQL的目标（否则SQL将用关系代数表示法编写）。

Nonstandard clauses like LIMIT (MySQL, PostgreSQL, SQLite), or TOP (Microsoft, Sybase), or ROWNUM (Oracle), or FIRST (Informix, InterBase) suffice for most users.

LIMIT（MySQL，PostgreSQL，SQLite）或TOP（Microsoft，Sybase）或ROWNUM（Oracle）或FIRST（Informix，InterBase）等非标准条款足以满足大多数用户的需求。

Standard syntax using ordered window functions is in fact the solution defined by the SQL:2003 specification. I realize the window functions are not supported by MySQL yet, but they are supported in virtually every other brand of RDBMS.

使用有序窗口函数的标准语法实际上是SQL：2003规范定义的解决方案。我意识到MySQL还不支持窗口功能，但几乎所有其他品牌的RDBMS都支持它们。

If you're proposing adding this functionality specifically for MySQL, it would be better to make it more compatible with other brands using standard syntax, rather than introduce new nonstandard syntax.

如果您建议专门为MySQL添加此功能，最好使用标准语法使其与其他品牌更兼容，而不是引入新的非标准语法。

Now to your other question:

现在回答你的另一个问题：

I'm looking for the REAL problems these @... operators could have.

我正在寻找这些@运营商可能遇到的真正问题。

One comes to mind immediately: it's not general-purpose.

人们立即浮现在脑海中：它不是通用的。

It handles the case where you want only the single row immediately following the comparison operand. But it doesn't handle many other cases that ordered window functions could satisfy:

它处理只需要紧跟比较操作数后面的单行的情况。但它没有处理有序窗口函数可以满足的许多其他情况：

  • Return the next two rows in a window.
  • 返回窗口中的下两行。
  • Return the last row in a window.
  • 返回窗口中的最后一行。
  • Return the top three rows by rank rather than by row number.
  • 按排名而不是按行号返回前三行。
  • etc.
  • 等等

It also introduces a new piece of punctuation that has no precedent in standard SQL, and which may conflict with some vendor additions, like prefixing user variables in MySQL or prefixing parameters in Microsoft SQL Server.

它还引入了一个新的标点符号，它在标准SQL中没有先例，并且可能与某些供应商的添加冲突，例如在MySQL中添加前缀用户变量或在Microsoft SQL Server中添加前缀参数。

I tested your examples in PostgreSQL 8.4, which supports window functions. I know that's not the current version of PostgreSQL, but it is the default version in the CentOS repo, and it's adequate to demonstrate window functions.

我在PostgreSQL 8.4中测试了你的例子，它支持窗口函数。我知道这不是PostgreSQL的当前版本，但它是CentOS repo中的默认版本，它足以演示窗口函数。

Example #1

示例＃1

select * 
from (select *, row_number() over (order by ca) as rn from ta where ca > 'B') as t
where rn = 1;

 id | ca | rn 
----+----+----
  2 | E  |  1

Example #2

例＃2

select * 
from (select *, rank() over (order by cb) as rk from tb where cb > 'E') as t
where rk = 1;

 id | cb | rk 
----+----+----
  5 | F  |  1
  4 | F  |  1

Example #3

例＃3

select t1.ca as c1, t2.ca as c2
from (select ca, row_number() over (order by ca) AS rn from ta) as t1
join (select ca, row_number() over (order by ca) AS rn from ta) as t2
  on t1.rn+1 = t2.rn;

 c1 | c2 
----+----
 A  | E
 E  | I
 I  | O
 O  | U

Example #4

例＃4

select *
from (select ta.id, ta.ca, tb.id, tb.cb, 
    rank() over (partition by ca order by cb) AS rk 
    from ta join tb on ca < cb) as t
where rk = 1;

 id | ca | id | cb | rn 
----+----+----+----+----
  1 | A  |  1 | C  |  1
  2 | E  |  5 | F  |  1
  2 | E  |  4 | F  |  1
  3 | I  |  6 | M  |  1
  4 | O  |  9 | Z  |  1
  5 | U  |  9 | Z  |  1

#3

1  

Perhaps this is not truly a question for somewhere like this. It does sound like a nice function, I must admit, but there are other ways of doing it. (Though I guess a lot of other functions have been added that had other ways f doing it too!)

也许对于像这样的地方来说，这不是一个真正的问题。我必须承认，它确实听起来像一个很好的功能，但还有其他方法可以做到这一点。 （虽然我想其他许多函数都已经添加了，其他方法也可以这样做！）

I suppose the true answers to something like this could be something like:

我想这样的事情的真正答案可能是这样的：

A. Demand - Is it worth the developers time to add functions like this to SQL if they feel that very few people would use them?

A.需求 - 如果开发人员觉得很少有人会使用它们，那么开发人员是否值得将这样的函数添加到SQL中？

B. Functionality - Does it actually allow the developer to DO MORE, or is it just another way of getting to the same goal?

B.功能 - 它是否真的允许开发人员做得更多，或者它只是达到同一目标的另一种方式？

C. Laziness - Can they actually be bothered to develop something like this?

C.懒惰 - 他们真的会*开发这样的东西吗？

The best way to get an answer to this would be to submit it to their development team and see what their response is, if enough people could get involved in asking for something like this to be implemented, then it could... power of the majority!

获得答案的最佳方法是将其提交给他们的开发团队，看看他们的回答是什么，如果有足够多的人可以参与要求实现这样的事情，那么它可以...多数！

标签：

相关文章