PostgreSQL三表关联查询：跨存储/使用位置匹配UUID

问题 #

现有三张表：

• transport：记录存储与使用位置间的运输转移数据，字段包括amount、timestamp_start、timestamp_end、to（UUID）、from（UUID）、type_water
• location_storage：存储位置信息
• location_usage：使用位置信息

transport的to和from字段UUID对应location_storage或location_usage中的某一行，但无法预先确定所属表。需要关联后获取每条运输记录的amount、distance、timestamp_start、type_water以及to_*、from_*系列字段（根据UUID匹配的表取对应值）。

核心问题：当from和to的UUID同属同一张位置表时，原关联语句无法正确区分并返回字段值。

表结构 #

CREATE TABLE transport (
    amount double precision NOT NULL,
    timestamp_start timestamp without time zone,
    timestamp_end timestamp without time zone,
    "to" uuid,
    "from" uuid,
    type_water character varying
);

CREATE TABLE location_storage (
    type character varying NOT NULL,
    uuid uuid NOT NULL,
    coordinates double precision[],
    status character varying,
    name character varying NOT NULL,
    content_latest double precision
);

CREATE TABLE location_usage (
    name character varying NOT NULL,
    type character varying,
    coordinates double precision[],
    uuid uuid,
    description text,
    status character varying
);

尝试的SQL（存在问题） #

SELECT amount,distance,timestamp_start,type_water,
location_storage.name as to_name, location_storage.type as to_type, location_storage.coordinates as to_coordinates, location_storage.description as to_description, location_storage.status as to_status,
location_usage.name as from_name, location_usage.type as from_type, location_usage.coordinates as from_coordinates,  location_usage.status as from_status
FROM transport
INNER JOIN location_storage ON transport.to = location_storage .uuid OR transport.from = location_storage .uuid
LEFT OUTER JOIN location_usage ON transport.to = location_usage.uuid OR transport.from = location_usage.uuid

示例数据集 #

transport表

location_storage表

location_usage表

期望结果 #

解决方法 #

核心思路 #

分别为to和from的UUID单独关联两张位置表，利用COALESCE函数优先取存在的表中的字段值（因为每个UUID只会属于location_storage或location_usage中的一个）。这种方式可以避免因同属一张表导致的关联混乱，确保to_*和from_*字段精准匹配对应的UUID来源。

完整SQL语句 #

SELECT
    t.amount,
    'tbc' AS distance, -- 距离字段需根据坐标计算，此处保留示例占位符
    t.timestamp_start,
    t.type_water,
    -- 处理to相关字段
    COALESCE(ts.name, tu.name) AS to_name,
    COALESCE(ts.type, tu.type) AS to_type,
    COALESCE(ts.coordinates, tu.coordinates) AS to_coordinates,
    tu.description AS to_description, -- 仅location_usage有description字段
    COALESCE(ts.status, tu.status) AS to_status,
    -- 处理from相关字段
    COALESCE(fs.name, fu.name) AS from_name,
    COALESCE(fs.type, fu.type) AS from_type,
    COALESCE(fs.coordinates, fu.coordinates) AS from_coordinates,
    COALESCE(fs.status, fu.status) AS from_status
FROM transport t
-- 关联to对应的两张位置表
LEFT JOIN location_storage ts ON t."to" = ts.uuid
LEFT JOIN location_usage tu ON t."to" = tu.uuid
-- 关联from对应的两张位置表
LEFT JOIN location_storage fs ON t."from" = fs.uuid
LEFT JOIN location_usage fu ON t."from" = fu.uuid;

说明 #

1. 关联方式：对to和from分别做两次LEFT JOIN，分别关联location_storage和location_usage，确保每个UUID的来源表都被匹配到。
2. COALESCE函数：用于从两个关联表的同名字段中取非空值，因为每个UUID只会在一个表中存在，所以不会出现冲突。
3. 特殊字段处理：to_description仅location_usage有，直接取tu.description即可，不存在时会返回NULL（对应示例中的空值）。
4. 距离字段：示例中用tbc占位，实际可通过坐标计算（如PostGIS的ST_Distance函数）得到真实值。

内容的提问来源于stack exchange，提问作者Sayar4