sql - SQL中根据一串JOIN规则查找第一条记录

Question

我需要使用多字段关系找到“最适合”的记录。我需要基于 3 个字段加入，如果失败使用 2 个字段，如果失败使用 1 个字段等。这样的链可以很长，字段可以变化等等。如何实现。

下面我提出了一些想法，但不知何故我觉得这可以做得更好。

第一种方式(我不喜欢 UNION，因为我猜引擎会执行它们，而不管 LIMIT 1 子句，它可以作为“第一次找到时停止”:

SELECT A1, A2, A3, B4 FROM A
LEFT OUTER JOIN (SELECT B4 FROM
      ((SELECT B4, 1 AS ORD FROM B 
            WHERE B.B1 = A.A1 AND B.B2 = A.B2 AND B.B3 = A.A3)
      UNION ALL
      (SELECT B4, 2 AS ORD FROM B 
            WHERE B.B1 = A.A1 AND B.B2 = A.B2)
      UNION ALL
      (SELECT B4, 3 AS ORD FROM B 
            WHERE B.B1 = A.A1)) ORDER BY ORD ASC LIMIT 1); 

第二种方式(这里我不喜欢在THEN部分重复SELECT IN WHEN和again，而且只有在返回单个字段时才好

SELECT A1, A2, A3,
    CASE 
        WHEN EXISTS 
            (SELECT B4 FROM B WHERE B.B1 = A.A1 AND B.B2 = A.B2 AND B.B3 = A.A3) 
            THEN 
            (SELECT B4 FROM B WHERE B.B1 = A.A1 AND B.B2 = A.B2 AND B.B3 = A.A3 LIMIT 1)
        WHEN EXISTS 
            (SELECT B4 FROM B WHERE B.B1 = A.A1 AND B.B2 = A.B2) 
            THEN 
            (SELECT B4 FROM B WHERE B.B1 = A.A1 AND B.B2 = A.B2 LIMIT 1)
        WHEN EXISTS 
            (SELECT B4 FROM B WHERE B.B1 = A.A1) 
            THEN (SELECT B4 FROM B WHERE B.B1 = A.A1 LIMIT 1)
        ELSE NULL
    END AS B4
FROM A;

我使用 postgreSQL 9.6，但这些问题应被视为标准 SQL 问题。

要测试的代码:

CREATE TABLE A (A1 INT, A2 INT, A3 INT);
CREATE TABLE B (B1 INT, B2 INT, B3 INT, B4 VARCHAR(2));

INSERT INTO A VALUES 
    (10, 20, 30),
    (11, 21, 31),
    (12, 22, 32),
    (13, 23, 33),
    (14, 24, 34);

INSERT INTO B VALUES 
    (10, 20,    30, 'A1'),
    (11, 21,    0,  'B'),
    (12, 0,     0,  'C'),
    (10, 23,    30, 'A2'),
    (14, 0,     34, 'D');

/*
EXPECTED RESULT
10, 20, 30, A1
11, 21, 31, B
12, 22, 32, C
*/

Answer 1

您可以使用左连接。这是一种方法:

select a.A1, a.A2, a.A3, coalesce(b1.B4, b2.b4, b3.b4) as b4
from A left join
     b b1
     on b1.b1 = a.a1 and b1.b2 = a.b2 and b1.b3 = a.b3 left join
     b b2
     on b1.b1 = a.a1 and b1.b2 = a.b2 and b1.a1 is null left join
     b b3
     on b1.b1 = a.a1 and b2.b1 is null and b1.b1 is null;

以上可以返回多行。如果你只想要一行，那么你可以尝试:

select a.*,
       (select b.b4
        from b
        where b.b1 = a.a1
        order by (b.b2 = a.a2 and b.b3 = a.a3)::int desc,
                 (b.b2 = a.a2)::int desc
        limit 1
       ) as b4
from a;

这样效率不高。而且，该查询可以表述为横向连接——如果您想返回多个值，这一点特别有用。