如何关联两表，基于关键字匹配筛选有效策略ID？

需求说明 #

现有两张表，需筛选出策略名称或其有效属性值包含指定关键字的strategy_id：

• 有效属性定义：attributes表中每个strategy_id对应同type_id的记录，仅取date最新的那条

表结构 #

表A：strategies（策略表） #

+-------+-----------+
|  id   |   name    |
+-------+-----------+
| 10001 | strategy1 |
| 10002 | strategy2 |
| 10003 | strategy3 |
+-------+-----------+

表B：attributes（属性表） #

+----+-------------+---------+------------+------------+
| id | strategy_id | type_id |   value    |    date    |
+----+-------------+---------+------------+------------+
|  1 |       10001 |       1 | Helsinki   | 2022-07-01 |
|  2 |       10001 |       2 | Brownfield | 2022-07-01 |
|  3 |       10002 |       1 | Paris      | 2022-08-01 |
|  4 |       10002 |       2 | Greenfield | 2022-08-01 |
|  5 |       10003 |       1 | Helsinki   | 2022-09-01 |
|  6 |       10003 |       2 | Greenfield | 2022-09-01 |
|  7 |       10001 |       1 | Frankfurt  | 2022-09-22 |
+----+-------------+---------+------------+------------+

解决方案SQL #

完整查询（含名称和属性） #

SELECT s.id AS strategy_id, s.name, GROUP_CONCAT(a.value SEPARATOR ' ') AS effective_attributes
FROM strategies s
LEFT JOIN (
    -- 子查询获取所有策略的有效属性记录
    SELECT strategy_id, type_id, value
    FROM attributes
    WHERE (strategy_id, type_id, date) IN (
        SELECT strategy_id, type_id, MAX(date)
        FROM attributes
        GROUP BY strategy_id, type_id
    )
) a ON s.id = a.strategy_id
GROUP BY s.id, s.name
-- 替换%指定关键字%为实际查询关键字
HAVING s.name LIKE '%指定关键字%' 
   OR effective_attributes LIKE '%指定关键字%';

仅返回strategy_id的简化版 #

SELECT s.id AS strategy_id
FROM strategies s
LEFT JOIN (
    SELECT strategy_id, type_id, value
    FROM attributes
    WHERE (strategy_id, type_id, date) IN (
        SELECT strategy_id, type_id, MAX(date)
        FROM attributes
        GROUP BY strategy_id, type_id
    )
) a ON s.id = a.strategy_id
GROUP BY s.id, s.name
HAVING s.name LIKE '%指定关键字%' 
   OR GROUP_CONCAT(a.value SEPARATOR ' ') LIKE '%指定关键字%';

关键说明 #

1. 子查询先筛选出所有有效属性：通过分组strategy_id和type_id取最大date，再匹配原表得到对应属性值
2. 用GROUP_CONCAT将同一策略的所有有效属性合并为单个字符串，方便用LIKE进行模糊匹配
3. LEFT JOIN确保无属性的策略也能被查询到（仅匹配名称条件）
4. 替换语句中的%指定关键字%为实际需要查询的内容即可

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