MySQL行子查询比较问题 [英] MySQL row subquery comparison issue

问题描述

我有一个小的mysql表(MySQL版本5.6.23):

I have a small mysql table (MySQL version 5.6.23) :

+-----------+-----------------+------+-----+---------+----------------+
| Field     | Type            | Null | Key | Default | Extra          |
+-----------+-----------------+------+-----+---------+----------------+
| id        | int(6) unsigned | NO   | PRI | NULL    | auto_increment |
| dividends | float(8,6)      | YES  |     | NULL    |                |
+-----------+-----------------+------+-----+---------+----------------+

我的where子句遵循行子查询语法.
如果我这样做:

My where clause follows the row-subqueries syntax.
If I do:

SELECT id, dividends FROM test  
  where  (id,dividends) >= (660,0.5);

或

SELECT id, dividends FROM test 
  where  (id,dividends) >= (660,CAST(0.5 AS DECIMAL(8,6)));

我得到这个结果:

+-----+-----------+
| id  | dividends |
+-----+-----------+
| 660 |  0.500000 |
| 661 |  0.470000 |
| 662 |  0.470000 |
| 663 |  0.470000 |
| 664 |  2.580000 |
| 665 |  2.581000 |
...

在我看来，未考虑股息> = 0.5.为什么?

It seems to me that dividends >= 0.5 is not taken into consideration. Why?

推荐答案

您正在使用行构造函数. MySQL完全像表行一样对待它们.因此，WHERE (id,dividends) >= (660,0.5)实际上与以下内容相同:

You're using row constructors. MySQL treats them exactly like rows of a table. Thus WHERE (id,dividends) >= (660,0.5) effectively does the same as:

1. ORDER BY id,dividends;

找到该顺序中(660,0.5)的位置；

Find the point at which (660,0.5) would sit within that ordering;

仅过滤等于或大于排序中该点的那些记录.

Filter for only those records that are equal to or greater than that point in the ordering.

因此，它与WHERE (id=660 AND dividends>=0.5) OR id>660相同.

似乎您真正希望表达的逻辑是WHERE id>=660 AND dividends>=0.5.

It appears as though the logic you really wish to express is WHERE id>=660 AND dividends>=0.5.

这篇关于MySQL行子查询比较问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！