如何筛选同时拥有指定TypeId与StateId集合的用户数据

现有数据表 #

userId  Username TypeId StateId
229    Test name   52   2
229    Test name   52   4
229    Test name   53   2
229    Test name   53   4
238    Test name2  52   2
238    Test name2  53   2

需求说明 #

仅返回**同时拥有TypeId（52、53）和StateId（2、4）**的用户的所有对应行，需排除像Test name2这类缺少StateId=4记录的用户。

尝试的SQL查询 #

select  userId,Appraiser,PropertyTypeId,StateId  from vw_SuggestedAppraisersWithSchedule 
where PropertyTypeId in (52,53)  and stateid in (2,4)
group by UserId,Appraiser,PropertyTypeId,StateId Having count(propertytypeId)=2 and count(stateid)=2

预期结果 #

userId  Username TypeId StateId
229    Test name   52   2
229    Test name   52   4
229    Test name   53   2
229    Test name   53   4

正确的SQL查询语句 #

原查询的问题在于分组粒度太细，group by UserId,Appraiser,PropertyTypeId,StateId会把每一行单独分组，导致count()结果永远为1，无法满足筛选条件。正确思路是先找出符合要求的用户，再关联原视图获取这些用户的所有目标行：

方法1：子查询筛选用户 #

SELECT v.userId, v.Appraiser, v.PropertyTypeId, v.StateId
FROM vw_SuggestedAppraisersWithSchedule v
JOIN (
    SELECT UserId, Appraiser
    FROM vw_SuggestedAppraisersWithSchedule
    WHERE PropertyTypeId IN (52, 53) AND StateId IN (2, 4)
    GROUP BY UserId, Appraiser
    -- 确保用户同时拥有2种TypeId、2种StateId，且每个TypeId都对应两种StateId
    HAVING COUNT(DISTINCT PropertyTypeId) = 2 
       AND COUNT(DISTINCT StateId) = 2
       AND COUNT(DISTINCT CONCAT(PropertyTypeId, '-', StateId)) = 4
) valid_users ON v.UserId = valid_users.UserId AND v.Appraiser = valid_users.Appraiser
WHERE v.PropertyTypeId IN (52, 53) AND v.StateId IN (2, 4)

方法2：窗口函数实现（支持窗口函数的数据库可用） #

如果你的数据库支持窗口函数（如SQL Server、MySQL 8+、PostgreSQL等），可以用更简洁的写法：

WITH user_stats AS (
    SELECT 
        userId, Appraiser, PropertyTypeId, StateId,
        COUNT(DISTINCT PropertyTypeId) OVER (PARTITION BY UserId, Appraiser) AS type_count,
        COUNT(DISTINCT StateId) OVER (PARTITION BY UserId, Appraiser) AS state_count,
        COUNT(DISTINCT CONCAT(PropertyTypeId, '-', StateId)) OVER (PARTITION BY UserId, Appraiser) AS combo_count
    FROM vw_SuggestedAppraisersWithSchedule
    WHERE PropertyTypeId IN (52, 53) AND StateId IN (2, 4)
)
SELECT userId, Appraiser, PropertyTypeId, StateId
FROM user_stats
WHERE type_count = 2 AND state_count = 2 AND combo_count = 4

说明：

• COUNT(DISTINCT PropertyTypeId) = 2：确保用户同时拥有52、53两种TypeId
• COUNT(DISTINCT StateId) = 2：确保用户同时拥有2、4两种StateId
• COUNT(DISTINCT CONCAT(PropertyTypeId, '-', StateId)) = 4：额外验证每个TypeId都对应两种StateId，避免出现“TypeId52有2和4，但TypeId53只有2”的情况

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