创建skillsInRange函数遇问题：wcount不存在且统计结果异常

需要创建SQL函数skillsInRange(n1 int, n2 int)，返回拥有至少n1个、至多n2个技能的Westerosis人数。关联表WesterosiSkill存储wid（人物ID）与skill（技能）的对应关系，尝试通过统计wid重复次数并筛选范围编写函数时，报错wcount不存在，且统计结果异常，调整WHERE/HAVING也无法解决。

相关表结构 #

INSERT INTO WesterosiSkill(wid, skill) VALUES
  (1001,'Archery'), (1001,'Politics'), (1002,'Archery'),
  (1002,'Politics'), (1004,'Politics'), (1004,'Archery'),
  (1005,'Politics'), (1005,'Archery'), (1005,'Swordsmanship'),
  (1006,'Archery'), (1006,'HorseRiding'), ...

尝试的错误函数代码 #

CREATE FUNCTION skillsInRange (n1 int, n2 int)
RETURNS INTEGER AS
$$
SELECT COUNT(wid) AS wcount
FROM westerosiSkill
GROUP BY wid
HAVING wcount BETWEEN n1 AND n2
$$ LANGUAGE SQL;

解决方案 #

错误原因 #

1. HAVING子句无法直接引用SELECT中定义的别名wcount——SQL执行顺序中，HAVING在SELECT之前执行，此时别名还未生成。
2. 原语句分组后返回的是每个wid的技能数，没有再统计符合条件的wid总数，导致结果不是预期的人数。

修正后的函数代码 #

CREATE FUNCTION skillsInRange (n1 int, n2 int)
RETURNS INTEGER AS
$$
SELECT COUNT(*)
FROM (
    SELECT wid
    FROM WesterosiSkill
    GROUP BY wid
    -- 直接用COUNT(skill)统计每个wid的技能数，筛选范围
    HAVING COUNT(skill) BETWEEN n1 AND n2
) AS filtered_wids;
$$ LANGUAGE SQL;

补充说明 #

• 内层子查询先按wid分组，统计每个wid的技能数量，筛选出技能数在[n1, n2]区间的wid。
• 外层查询统计这些符合条件的wid数量，就是最终要返回的人数。
• 如果skill字段可能存在NULL值，建议用COUNT(*)代替COUNT(skill)，因为COUNT(skill)会忽略NULL记录，而COUNT(*)会统计所有关联的技能记录。

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