如何编写SQL查询统计布尔标志的多种组合情况？

现有如下表结构，需要统计所有预定义场景的人员数量，要求实现高效，避免为每个统计项单独创建聚合表：

示例表结构与数据 #

declare @persons table(
person_id INT,
country_id INT,
is_adult BIT,
is_fat BIT,
is_tall BIT,
is_bald BIT)

declare @stats table(
    stat_name varchar(max),
    persons_count INT
)

insert @persons
values
    (1000, 12, 1, 0, 1, 0),
    (1001, 15, 1, 0, 0, 1),
    (1002, 12, 0, 1, 1, 0),
    (1003, 20, 1, 0, 0, 0),
    (1004, 15, 0, 1, 0, 1)

insert @stats
values
    ('adults, fat, tall', 0),
    ('adults, fat, tall, bald', 0),
    ('fat, bald', 0),
    ('tall', 0)

高效实现方案 #

可以通过拆分场景关键词+条件匹配的方式，一次性计算所有场景的统计结果，无需单独创建聚合表。以下是具体实现脚本：

-- 更新统计结果
UPDATE s
SET persons_count = p.count_matches
FROM @stats s
CROSS APPLY (
    SELECT COUNT(*) AS count_matches
    FROM @persons p
    WHERE 
        -- 逐个匹配场景中的关键词对应的属性
        (CHARINDEX('adults', s.stat_name) = 0 OR p.is_adult = 1)
        AND (CHARINDEX('fat', s.stat_name) = 0 OR p.is_fat = 1)
        AND (CHARINDEX('tall', s.stat_name) = 0 OR p.is_tall = 1)
        AND (CHARINDEX('bald', s.stat_name) = 0 OR p.is_bald = 1)
) p;

-- 查看结果
SELECT * FROM @stats;

方案说明 #

1. 利用CHARINDEX检查当前统计场景是否包含某个关键词：若不包含则跳过该属性的筛选；若包含则验证人员对应属性是否为1（符合条件）
2. 通过CROSS APPLY为每个统计场景计算符合条件的人员数量，一次性完成所有场景的统计
3. 直接更新@stats表中的统计结果，无需额外存储中间数据

执行后@stats表的结果为：

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