如何从Order表读取数组格式Product_id关联查询Product表详情？

现有数据表结构 #

表Product #

表Order #

需求：从Order表读取数据时，自动关联Product表获取对应商品的详情，而非手动指定IN参数中的值。

用户尝试的SQL语句：

SELECT
    *
FROM
    product
INNER JOIN
    order
WHERE
    order.product_id IN (2,3)
AND 
    orders.id = 3;

解决方案 #

一、推荐：修改表结构（符合数据库范式） #

Order表中用逗号分隔存储多个product_id是反范式设计，会导致查询效率低、数据维护困难。建议拆分出订单商品关联表：

新建Order_Product关联表

关联查询SQL

SELECT 
    o.id AS order_id, 
    p.id AS product_id, 
    p.Title
FROM `Order` o
JOIN Order_Product op ON o.id = op.order_id
JOIN Product p ON op.product_id = p.id
WHERE o.id = 3; -- 可替换为任意订单ID

二、临时方案：不修改表结构时的查询写法 #

如果暂时无法调整表结构，可根据使用的数据库类型，使用字符串处理函数实现关联：

MySQL 写法

SELECT 
    o.id AS order_id, 
    p.id AS product_id, 
    p.Title
FROM `Order` o
JOIN Product p ON FIND_IN_SET(p.id, o.Product_id) > 0
WHERE o.id = 3;

SQL Server 写法

SELECT 
    o.id AS order_id, 
    p.id AS product_id, 
    p.Title
FROM `Order` o
CROSS APPLY STRING_SPLIT(o.Product_id, ',') AS sp
JOIN Product p ON p.id = CAST(sp.value AS INT)
WHERE o.id = 3;

PostgreSQL 写法

SELECT 
    o.id AS order_id, 
    p.id AS product_id, 
    p.Title
FROM "Order" o
JOIN Product p ON p.id = ANY(STRING_TO_ARRAY(o.Product_id, ',')::INT[])
WHERE o.id = 3;

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