检查同一列中另一列中的ID是否没有颗粒值

我有一个带有薪水umId,薪水年份的薪水表,我想进行一次选择查询,以查找根本没有2020-01-01的salsingSumIds。

我的查询看起来像这样

select distinct  salarySumId, salaryYear 
from Salary 

该查询返回类似这样的内容

salarySumId     salaryYear 
-----------     ------------
4593086         2019-01-01 
4593093         2018-01-01 
4593093         2019-01-01 
4593093         2020-01-01 
4593094         2019-01-01 
4593095         2018-01-01 
4593095         2019-01-01 
4593095         2020-01-01 
4593096         2017-01-01 
4593096         2018-01-01 
4593096         2019-01-01 
4593096         2020-01-01 

当我编写查询过滤器2020-01-01时,如下所示:

select distinct  salarySumId, salaryYear 
from Salary 
where Salary.InsuranceYear < '2020-01-01'

alarySumId      salaryYear 
-----------     ------------
4593086         2019-01-01 
4593093         2018-01-01 
4593093         2019-01-01 
4593094         2019-01-01 
4593095         2018-01-01 
4593095         2019-01-01 
4593096         2017-01-01 
4593096         2018-01-01 

实际上,我只希望查询返回(4593086,4593094) 因为他们根本没有2020年1月1日的薪金年值。

如何重新编写查询呢?

评论
  • tut
    tut 回复

    Do a GROUP BY. Use a case expression in the HAVING clause to count the number of 2020-01-01 rows.

    select salarySumId
    from Salary
    group by salarySumId
    having sum(case when salaryYear = '2020-01-01' then 1 else 0 end) = 0
    
  • 天然呆zZ
    天然呆zZ 回复

    您可以与自身抗连接:

    select distinct  salarySumId, salaryYear 
    from Salary s1
    where not exists (select 1 from Salary s2 where s2.salaryYear='2020-01-01' and s2.salarySumId=s1.salarySumId)
    
  • 习惯性隐身
    习惯性隐身 回复

    You can use not exists :

    select s.*
    from Salary s
    where not exists (select 1 
                      from Salary s1 
                      where s1.salarySumId = s.salarySumId and 
                            s1.salaryYear = '2020-01-01'
                    );