SQL按优先级匹配columnY/columnX关联orders与customers表取price

方案1：窗口函数实现（适配MySQL 8.0+/PostgreSQL/SQL Server等支持窗口函数的数据库） #

WITH matched_data AS (
    SELECT 
        a.*,
        c.*,
        ROW_NUMBER() OVER (
            PARTITION BY a.product 
            ORDER BY CASE WHEN a.product = c.columnY THEN 1 ELSE 2 END ASC
        ) AS rn
    FROM orders a
    LEFT JOIN customers c 
        ON a.product = c.columnY OR a.product = c.columnX
)
SELECT product, yr, cus_name, columnX, columnY, price
FROM matched_data
WHERE rn = 1;

逻辑说明：先查询所有匹配行，按优先级给每个product的匹配结果排序，匹配columnY的行优先级更高排第一位，最终只取每个product排序第一的行即可，适配你提到的无重复匹配的场景。

方案2：双LEFT JOIN实现（适配所有版本数据库，包括MySQL 5.x等不支持窗口函数的场景） #

SELECT 
    a.product,
    a.yr,
    COALESCE(c1.cus_name, c2.cus_name) AS cus_name,
    COALESCE(c1.columnX, c2.columnX) AS columnX,
    COALESCE(c1.columnY, c2.columnY) AS columnY,
    COALESCE(c1.price, c2.price) AS price
FROM orders a
LEFT JOIN customers c1 ON a.product = c1.columnY
LEFT JOIN customers c2 ON a.product = c2.columnX;

逻辑说明：优先关联columnY对应的客户记录，如果关联到就优先用c1的字段值，未关联到的话自动取columnX关联的c2的字段值，完全符合优先级要求。

以上两种方案的运行结果都和你提供的预期输出完全一致。

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