如何在不添加额外列的情况下用COUNT过滤SQL查询结果？

给你两种简单的解决办法，都能去掉不需要的count列：

方法一：用EXISTS子查询筛选（推荐） #

直接通过子查询判断员工是否有多个技能，不需要生成额外列：

select e.employeename, s.skilldescription
from employeeskills_t k
inner join employee_t e on k.employeeid = e.employeeid
inner join skill_t s on k.skillid=s.skillid 
where exists (
    select 1
    from employeeskills_t k2
    where k2.employeeid = e.employeeid
    having count(*) > 1
)
order by employeename, skilldescription

方法二：明确指定外层查询的列 #

如果你想保留原有的窗口函数逻辑，只需要把外层的select *改成具体需要的列即可：

select t.employeename, t.skilldescription
from (
    select e.employeename, s.skilldescription,
        count(*) over(partition by e.employeeid) as count
    from employeeskills_t k
    inner join employee_t e on k.employeeid = e.employeeid
    inner join skill_t s on k.skillid=s.skillid 
) t
where t.count > 1
order by t.employeename, t.skilldescription

两种方法都能得到你想要的结果：只显示员工姓名和技能描述，同时只保留拥有多个技能的员工记录。

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