如何查询赛事属性表中多行列属性和大于指定值X的赛事记录

完全可以通过单条SQL查询实现需求，不需要调整现有表结构，也不需要执行多次查询。

实现思路 #

• 先从Match Attributes表筛选出attribute_id为3、4、5、6的所有记录
• 按match_id分组，通过条件聚合分别计算每场赛事的全场进球和、加时赛进球和
• 最后通过HAVING子句过滤满足阈值条件的match_id

基础查询SQL（仅返回符合条件的match_id） #

SELECT match_id
FROM Match_Attributes
WHERE attribute_id IN (3,4,5,6)
GROUP BY match_id
HAVING (
    -- 全场总进球：3为主队全场进球，4为客队全场进球
    SUM(CASE WHEN attribute_id = 3 THEN attribute_value ELSE 0 END)
    + SUM(CASE WHEN attribute_id = 4 THEN attribute_value ELSE 0 END)
) > @X
OR (
    -- 加时赛总进球：5为主队加时进球，6为客队加时进球
    SUM(CASE WHEN attribute_id = 5 THEN attribute_value ELSE 0 END)
    + SUM(CASE WHEN attribute_id = 6 THEN attribute_value ELSE 0 END)
) > @X;

说明：@X为你输入的指定阈值，若需要查询总进球小于X的赛事，直接将>改为<即可。

进阶查询（关联返回赛事完整信息） #

如果需要同步获取Match Overview表中的赛事对阵、日期等信息，可以用子查询关联：

SELECT mo.*
FROM Match_Overview mo
JOIN (
    SELECT match_id
    FROM Match_Attributes
    WHERE attribute_id IN (3,4,5,6)
    GROUP BY match_id
    HAVING (
        SUM(CASE WHEN attribute_id = 3 THEN attribute_value ELSE 0 END)
        + SUM(CASE WHEN attribute_id = 4 THEN attribute_value ELSE 0 END)
    ) > @X
    OR (
        SUM(CASE WHEN attribute_id = 5 THEN attribute_value ELSE 0 END)
        + SUM(CASE WHEN attribute_id = 6 THEN attribute_value ELSE 0 END)
    ) > @X
) valid_matches ON mo.id = valid_matches.match_id;

业务规则适配说明 #

如果你的业务要求必须同时存在对应两个属性才参与统计（例如缺少客场全场进球的赛事不计算全场总进球），可以将CASE表达式中的ELSE 0修改为ELSE NULL：此时只要缺少任意一个对应属性，求和结果就为NULL，不会被判定为符合条件。

性能优化建议 #

你当前使用的键值对属性表结构非常适合属性稀疏的场景，完全不需要改为宽表避免大量NULL值。如果该查询频率较高，可以给Match_Attributes表添加联合索引(match_id, attribute_id, attribute_value)，就能大幅提升查询效率。

内容的提问来源于stack exchange，提问作者Marc-9