使用对称符号检查2个行值表达式(示例代码)

2021-05-07

栏目: 十大 ·

简介  这篇文章主要介绍了使用对称符号检查2个行值表达式(示例代码)以及相关的经验技巧,文章约5723字,浏览量3743,点赞数7,值得推荐!

我想比较Table-1中的两列值(例如colA, colX),以检查它们是否在Table-2中的列值(例如colB, colD, colY, colZ)的范围内。也就是说,如果colAcolB-colD之间,和/或colXcolY-colZ之间。

我了解BETWEEN SYMMETRIC用于与订单无关的情况,例如(最小,最大)或(最大,最小)。下面是我创建的示例。

表:

CREATE TABLE location (id int, lat float, lon float, gpstime int);
CREATE TABLE trips( trip_id int, lat_start float, lat_end float,
               lon_start float, lon_end float, travel_mode text);

表值:

INSERT INTO location
SELECT 1, 41.2, 8.3, 11111 UNION ALL   --both lat and lon in the interval
SELECT 2, 43.3, 8.5, 22222 UNION ALL   --only lat meets criteria
SELECT 3, 44.0, 8.5,  33333            --only lon meets criteria

INSERT INTO trips
SELECT 1, 41.1, 41.9, 8.0, 8.8, 'foot' UNION ALL  
SELECT 2, 43.1, 43.5, 9.5, 8.9, 'bus' UNION ALL
SELECT 3, 42.1, 42.8, 8.7, 8.0, 'metro' 

TASK:

目标是获得lat表中的lonlocation(或两者)碰巧满足标准(在trips表中)的所有行:latlat_start-lat_end之间; lon之间的lon_start- lon_end

显然location表的所有行都应返回:第1行符合所有条件,第2和3行符合任一条件。

预期结果:

+---------+-----------+------+-----+-------------+
| trip_id | timestamp | lat  | lon | travel_mode |
+---------+-----------+------+-----+-------------+
|       1 |     11111 | 41.2 | 8.3 | foot        |
|       2 |     22222 | 43.1 | 8.5 | bus         |
|       3 |     33333 | 44.0 | 8.5 | metro       |
+---------+-----------+------+-----+-------------+

所以我使用BETWEEN SYMMETRIC尝试了不同的查询,但结果不正确。

查询-1:

SELECT trip_id, gpstime AS timestamp, lat, lon,  travel_mode
FROM location 
INNER JOIN trips 
   ON (lat BETWEEN SYMMETRIC lat_start AND lat_end) 
   AND (lon BETWEEN SYMMETRIC lon_start AND lon_end) 

查询结果:

+---------+-----------+------+-----+-------------+
| trip_id | timestamp | lat  | lon | travel_mode |
+---------+-----------+------+-----+-------------+
|       1 |     11111 | 41.2 | 8.3 | foot        |
+---------+-----------+------+-----+-------------+

Query-2:搞砸了一切

SELECT trip_id, gpstime AS timestamp, lat, lon,  travel_mode
FROM location 
INNER JOIN trips 
   ON (lat, lon) BETWEEN SYMMETRIC (lat_start,  lat_end) AND (lon_start, lon_end)

结果:

+---------+-----------+------+-----+-------------+
| trip_id | timestamp | lat  | lon | travel_mode |
+---------+-----------+------+-----+-------------+
|       2 |     11111 | 41.2 | 8.3 | foot        |
|       3 |     11111 | 41.2 | 8.3 | metro       |
+---------+-----------+------+-----+-------------+

Query-3:甚至更差

SELECT trip_id, gpstime AS timestamp, lat, lon,  travel_mode
FROM location 
INNER JOIN trips 
   ON lat BETWEEN SYMMETRIC lat_start AND lat_end
UNION 
SELECT trip_id, gpstime AS timestamp, lat, lon,  travel_mode
FROM location 
INNER JOIN trips 
   ON lon BETWEEN SYMMETRIC lon_start AND lon_end

结果:

+---------+-----------+------+-----+-------------+
| trip_id | timestamp | lat  | lon | travel_mode |
+---------+-----------+------+-----+-------------+
|       1 |     11111 | 41.2 | 8.3 | foot        |
|       1 |     22222 | 43.3 | 8.5 | foot        |
|       1 |     33333 | 44   | 8.5 | foot        |
|       2 |     22222 | 43.3 | 8.5 | bus         |
|       3 |     11111 | 41.2 | 8.3 | metro       |
|       3 |     22222 | 43.3 | 8.5 | metro       |
|       3 |     33333 | 44   | 8.5 | metro       |
+---------+-----------+------+-----+-------------+

问题:什么是达到期望结果的正确查询?

编辑:

基于收到的答案,建议在查询1中使用OR而不是AND,我在下面编辑我的问题以显示推荐查询的结果:

QUERY:

SELECT trip_id, gpstime AS timestamp, lat, lon,  travel_mode
FROM location 
INNER JOIN trips 
   ON (lat BETWEEN SYMMETRIC lat_start AND lat_end) 
   OR (lon BETWEEN SYMMETRIC lon_start AND lon_end);

这里是查询结果:

+---------+-----------+------+-----+-------------+
| trip_id | timestamp | lat  | lon | travel_mode |
+---------+-----------+------+-----+-------------+
|       1 |     11111 | 41.2 | 8.3 | foot        |
|       3 |     11111 | 41.2 | 8.3 | metro       |
|       1 |     22222 | 43.3 | 8.5 | foot        |
|       2 |     22222 | 43.3 | 8.5 | bus         |
|       3 |     22222 | 43.3 | 8.5 | metro       |
|       1 |     33333 | 44   | 8.5 | foot        |
|       3 |     33333 | 44   | 8.5 | metro       |
+---------+-----------+------+-----+-------------+
答案

目标是获取所有经纬度(或纬度)的行位置表符合条件

在您的第一个查询中将AND替换为OR

另一答案

您正在寻找的查询是

SELECT trip_id, gpstime AS timestamp, lat, lon,  travel_mode
FROM location 
INNER JOIN trips 
   ON (lat BETWEEN SYMMETRIC lat_start AND lat_end) 
   OR (lon BETWEEN SYMMETRIC lon_start AND lon_end);

就像萨尔曼说的一样,但让我帮助您清除误会。

BETWEEN SYMMETRIC是什么意思?

x BETWEEN SYMMETRIC a AND b

与]相同>

x BETWEEN SYMMETRIC LEAST(a, b) AND GREATEST(a, b)

因此,它仅在适当时交换边界。

(a, b) < (c, d)是什么意思?

比较元组是以字典方式进行的。

首先,比较ac,只有它们相等时,才比较bd

SO

(a, b) < (c, d)

与]相同>

a < c OR (a = c AND b < d)

所以那根本不是您想要的。


以上就是本文的全部内容,希望对大家的学习有所帮助,本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。

相关文章

对称二叉树

101.对称二叉树(示例代码)

对称二叉树(示例代码)

对称二叉树(示例代码)

二叉树(三)--对称二叉树

轮换对称式(示例代码)

Python Pandas:更改格式,其中索引和列名以及相应的值是新的行值

判断对称二叉树(示例代码)