如何查询db1.metadatas表中符合多条件的关联行并展示？

现有db1.metadatas表，包含record_id、meta_struct_id、value三个字段，需实现以下逻辑：

• 仅保留record_id对应的meta_struct_id=1的value匹配'YEAR-2000%'的记录组
• 将符合条件的record_id下，meta_struct_id=1和40的value分别作为两列返回

方案一：条件聚合（通用型，适配多数数据库） #

SELECT
    MAX(CASE WHEN meta_struct_id = 1 THEN value END) AS "This is id 1",
    MAX(CASE WHEN meta_struct_id = 40 THEN value END) AS "This is based on id 40"
FROM db1.metadatas
WHERE record_id IN (
    -- 先筛选出符合value规则的record_id
    SELECT record_id
    FROM db1.metadatas
    WHERE meta_struct_id = 1 AND value LIKE 'YEAR-2000%'
)
AND meta_struct_id IN (1, 40)
GROUP BY record_id;

方案二：自连接（逻辑更直观） #

SELECT
    m1.value AS "This is id 1",
    m40.value AS "This is based on id 40"
FROM db1.metadatas m1
INNER JOIN db1.metadatas m40 
    ON m1.record_id = m40.record_id
WHERE m1.meta_struct_id = 1 
  AND m1.value LIKE 'YEAR-2000%'
  AND m40.meta_struct_id = 40;

示例数据 #

record_id:     |      meta_struct_id:    | value: 
4              |      1                  | YEAR-2000-DEBIT-O1
4              |      40                 | $3,000.20
4              |      6                  | Kansas City
8              |      1                  | YEAR-2000-DEBIT-02
8              |      40                 | $15,604.50
8              |      6                  | Los Angeles
20             |      1                  | YEAR-2000-DEBIT-03
20             |      40                 | $12,550.01
20             |      6                  | Auckland
51             |      1                  | YEAR-2020-DEBIT-01
51             |      40                 | $6,050

期望结果 #

This is id 1          This is based on id 40
YEAR-2000-DEBIT-01    $3,000.20
YEAR-2000-DEBIT-02    $15,604.50
YEAR-2000-DEBIT-03    $12,550.01

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