如何从数据库中筛选满足多组field-value条件的多行数据

给定如下数据集，需要筛选出同时满足所有指定field-value配对条件的记录——只要某个name对应的记录有一个条件没满足，就不返回该name的任何记录，条件数量可以灵活调整（1个或多个）。

数据集 #

示例1：双条件查询 #

条件：

• field = 'Are they covered at the moment' AND value = TRUE
• field = 'Where does the client operate from' AND value = Home

预期输出：

示例2：单条件查询 #

条件：

• field = 'Additional information' AND value = x

预期输出：

解决方案 #

1. SQL实现（通用灵活） #

核心思路是先统计每个name满足的条件数量，再筛选出满足所有条件的name，最后取出这些name的全部记录。条件数量调整只需要修改required_conditions部分即可。

-- 定义需要满足的所有条件，新增/删除条件直接在这里加行
WITH required_conditions AS (
    SELECT 'Are they covered at the moment' AS field, 'TRUE' AS value
    UNION ALL
    SELECT 'Where does the client operate from' AS field, 'Home' AS value
),
-- 统计每个name匹配到的条件数
name_match_count AS (
    SELECT t.name, COUNT(*) AS matched_num
    FROM your_table t
    JOIN required_conditions rc 
      ON t.field = rc.field AND t.value = rc.value
    GROUP BY t.name
    -- 只保留匹配到所有条件的name
    HAVING COUNT(*) = (SELECT COUNT(*) FROM required_conditions)
)
-- 取出这些name对应的所有原始记录
SELECT t.*
FROM your_table t
JOIN name_match_count nm ON t.name = nm.name;

2. Python Pandas实现 #

通过将长表转宽表，快速判断每个name是否满足所有条件，再提取对应记录。

import pandas as pd

# 构造原数据
data = [
    ["A", "Are they covered at the moment", "TRUE"],
    ["A", "Where does the client operate from", "Home"],
    ["A", "Additional information", "x"],
    ["B", "Are they covered at the moment", "TRUE"],
    ["B", "Where does the client operate from", "Work"],
    ["B", "Additional information", "x"],
]
df = pd.DataFrame(data, columns=["name", "field", "value"])

# 定义条件，格式为{字段名: 目标值}，新增/删除条件直接修改字典
conditions = {
    "Are they covered at the moment": "TRUE",
    "Where does the client operate from": "Home"
}

# 转宽表：每个field作为单独一列
wide_df = df.pivot(index="name", columns="field", values="value").reset_index()

# 生成筛选掩码：判断每个name是否满足所有条件
mask = True
for field, target_val in conditions.items():
    mask &= (wide_df[field] == target_val)

# 提取符合条件的name列表，再从原表取对应记录
valid_names = wide_df[mask]["name"].tolist()
result = df[df["name"].isin(valid_names)]

print(result)

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