基于多属性条件筛选单一物种的SQL查询优化问询

问题背景 #

我正在开展一项节肢动物物种识别项目，用一张表记录物种及其属性，表结构和数据如下：

表的主键为i_spID和t_property，每个物种可对应多个属性。我的需求是通过一组属性值对筛选出目标物种，之前用多子查询关联的写法如下：

SELECT t1.i_spID, t1.t_species 
FROM ( SELECT * FROM DBO.t_species WHERE t_property = 'nmb_legs' AND i_property_val = 6 ) AS t1, 
     ( SELECT * FROM DBO.t_species WHERE t_property = 'nmb_wings' AND i_property_val = 4 ) AS t2 
WHERE t1.i_spID = t2.i_spID

但属性数量增加时，语句会变得非常繁琐，想找更简洁高效的写法。

附建表及插入数据的SQL：

CREATE table DBO.t_species (
 i_spID int NOT NULL,
 t_property nvarchar(20) NOT NULL,
 t_species nvarchar(50),
 i_property_val int
);
ALTER TABLE DBO.t_species ADD CONSTRAINT pkID_sp01 PRIMARY KEY (i_spID,t_property);
INSERT INTO DBO.t_species (i_spID, t_property, t_species, i_property_val)
VALUES
(1, 'nmb_legs', 'Blatta germanica', 6),
(2, 'nmb_legs', 'Musca domestica', 6),
(3, 'nmb_legs', 'Latrodectus hesperus', 8),
(1, 'nmb_wings', 'Blatta germanica', 4),
(2, 'nmb_wings', 'Musca domestica', 2)

优化方案 #

方案1：GROUP BY + HAVING 统计匹配数

这是最简洁的写法之一，通过筛选符合条件的属性记录，再按物种分组统计匹配的属性数量，当数量等于筛选的属性对总数时，即为目标物种。

示例（筛选腿数6、翅膀数4的物种）：

SELECT i_spID, t_species
FROM DBO.t_species
WHERE (t_property = 'nmb_legs' AND i_property_val = 6)
   OR (t_property = 'nmb_wings' AND i_property_val = 4)
GROUP BY i_spID, t_species
HAVING COUNT(*) = 2 -- 此处数字需与筛选的属性对数量一致

新增属性时，只需在WHERE里添加新的条件，并把HAVING里的数字加1即可。

方案2：简化的自连接写法

如果偏好JOIN的逻辑，可以用单表自连接替代子查询，结构更清晰：

SELECT t1.i_spID, t1.t_species
FROM DBO.t_species t1
JOIN DBO.t_species t2 ON t1.i_spID = t2.i_spID
WHERE t1.t_property = 'nmb_legs' AND t1.i_property_val = 6
  AND t2.t_property = 'nmb_wings' AND t2.i_property_val = 4

新增属性时，继续添加JOIN DBO.t_species tn ON t1.i_spID = tn.i_spID和对应的WHERE条件即可，比子查询嵌套更易维护。

方案3：临时表存储动态筛选条件

如果需要频繁修改筛选的属性对，或者属性数量不确定，可以用临时表存储条件，再关联统计匹配数，无需修改查询主体：

-- 创建临时表存放筛选条件
CREATE TABLE #FilterProps (
    t_property nvarchar(20),
    i_property_val int
)
-- 插入需要的属性值对
INSERT INTO #FilterProps VALUES ('nmb_legs', 6), ('nmb_wings', 4)

-- 查询匹配所有条件的物种
SELECT s.i_spID, s.t_species
FROM DBO.t_species s
JOIN #FilterProps fp ON s.t_property = fp.t_property AND s.i_property_val = fp.i_property_val
GROUP BY s.i_spID, s.t_species
HAVING COUNT(*) = (SELECT COUNT(*) FROM #FilterProps)

-- 清理临时表
DROP TABLE #FilterProps

这种方式灵活性最高，新增或修改筛选条件时，只需要修改临时表的插入语句即可。

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