如何仅通过TestEnvironment ID构建静态查询获取关联的TestCommentary

KeyValue:
- id
- key
- value

TestEnvironment:
- id

TestEnvironmentProperties:
- test_env_id
- keyvalue_id

TestCommentary:
- id
- comment

TestCommentaryProperties:
- test_commentary_id
- keyvalue_id

KeyValue #

TestEnvironment #

TestEnvironmentProperties（关联表） #

TestCommentary #

TestCommentaryProperties（关联表） #

TestEnvironment与KeyValue为多对多关系，TestCommentary与KeyValue同样为多对多关系。

测试环境完全由与其关联的任意键值对定义。根据上述示例数据，共定义了3个测试环境：

测试注释的定义方式类似，但测试注释拥有独立的键值对集合，无需与测试环境一一对应。根据上述示例数据，测试注释定义如下：

从概念上来说，当TestCommentary的键值对是TestEnvironment键值对的子集时，二者存在关联关系。

例如，查询各测试环境的关联注释时，结果如下：

TestEnvironment.id = 1 #

TestEnvironment.id = 2 #

TestEnvironment.id = 3 #

如何构建一个无需动态生成的单一查询，仅通过TestEnvironment的id即可获取与其关联的TestCommentary？

解决方案 #

可以通过验证测试注释的所有键值对是否都包含在目标测试环境的键值对中，来筛选关联注释，以下是通用SQL查询语句：

SELECT DISTINCT tc.id, tc.comment
FROM TestCommentary tc
JOIN TestCommentaryProperties tcp ON tc.id = tcp.test_commentary_id
WHERE NOT EXISTS (
    -- 查找当前注释中，不在目标测试环境里的键值对
    SELECT 1
    FROM TestCommentaryProperties tcp_inner
    JOIN KeyValue kv_inner ON tcp_inner.keyvalue_id = kv_inner.id
    WHERE tcp_inner.test_commentary_id = tc.id
    AND NOT EXISTS (
        SELECT 1
        FROM TestEnvironmentProperties tep
        JOIN KeyValue kv_env ON tep.keyvalue_id = kv_env.id
        WHERE tep.test_env_id = [目标环境ID] -- 替换为实际要查询的TestEnvironment.id
        AND kv_env.key = kv_inner.key
        AND kv_env.value = kv_inner.value
    )
);

逻辑说明 #

1. 外层关联TestCommentary和TestCommentaryProperties，获取所有注释及其对应的键值对。
2. 嵌套的NOT EXISTS子句用来确认：注释的每一个键值对，都能在目标测试环境的键值对中找到完全匹配的记录。只有满足这个条件的注释，才会被返回。
3. DISTINCT用于避免同一个注释被重复输出。

示例查询（以TestEnvironment.id=2为例） #

SELECT DISTINCT tc.id, tc.comment
FROM TestCommentary tc
JOIN TestCommentaryProperties tcp ON tc.id = tcp.test_commentary_id
WHERE NOT EXISTS (
    SELECT 1
    FROM TestCommentaryProperties tcp_inner
    JOIN KeyValue kv_inner ON tcp_inner.keyvalue_id = kv_inner.id
    WHERE tcp_inner.test_commentary_id = tc.id
    AND NOT EXISTS (
        SELECT 1
        FROM TestEnvironmentProperties tep
        JOIN KeyValue kv_env ON tep.keyvalue_id = kv_env.id
        WHERE tep.test_env_id = 2
        AND kv_env.key = kv_inner.key
        AND kv_env.value = kv_inner.value
    )
);

执行后会返回注释ID1和2，与示例结果一致。

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