时间戳

首页 > 解决方案 > 根据与数组列的比较选择关联记录

sql - 根据与数组列的比较选择关联记录

问题描述

假设有下表的假设场景

Vehicles
+----+--------+
| id | number |
+----+--------+
|  1 | v1     |
|  2 | v2     |
|  3 | v3     |
+----+--------+

Users
+-----+------+-------------+
| id  | name | vehicle_ids |
+-----+------+-------------+
| 100 | u1   | {1}         |
| 200 | u2   | {1,2}       |
| 300 | u3   | {2,3}       |
| 400 | u4   | {}          |
+-----+------+-------------+

鉴于number车辆，我正在寻找返回与该车辆关联的所有用户的查询，

  1. 其中 vehicle_ids 包含车辆的 id
  2. 其中 vehicle_ids 只有车辆的 id（即单个元素）

标签： sqlpostgresql

解决方案

我们可以使用ANY和ALL构造（更多细节在这里Row and Array Comparisons）

使用“任何”

http://sqlfiddle.com/#!17/ec886/21

对于第一种情况，我们需要返回与搜索查询（以及其他）表示的车辆相关联的用户，我们可以使用 ANY。因此，以下查询将返回带有名称的用户行，u2并且u3当v2作为车辆名称提供时

SELECT "users".* 
FROM "users" 
WHERE (
  (SELECT "vehicles"."id" FROM "vehicles" WHERE "vehicles"."number" = 'v2') = 
ANY("users"."vehicle_ids")
)

您可以替换"number" = 'v2'为任何其他车辆名称以获取与该车辆编号关联的所有用户。

使用“全部”

http://sqlfiddle.com/#!17/ec886/27

对于我们需要完全匹配的第二种情况，即数组中只有一个元素，我们将使用 All。因此，以下查询将仅返回具有名称的用户u1行v1

SELECT "users".* 
FROM "users" 
WHERE (
  "users"."vehicle_ids" != '{}' and
  (SELECT "vehicles"."id" FROM "vehicles" WHERE "vehicles"."number" = 'v1') = ALL("users"."vehicle_ids")
)

如果我们不使用"users"."vehicle_ids" != '{}'，那么结果也将包含用户u4

笔记：

此子查询方法仅适用于单个车辆，即我们不能这样做WHERE "vehicles"."number" in ('v2', v3)，因为子查询将返回多行并且 Postgres 将引发错误。我们可能不得不探索unnest它是否可以达到与原始问题相同的目的，以及它是否足够通用以支持上述用例。

推荐阅读