sql - 如果一行不为空，但仍获得该组中为空的其他结果，则 SQL 返回结果

Question

我有一个查询，按人查找返回结果，但我正在寻找其中一列不为空但我仍然想查看所有其他相关行的结果

select person, hiredate, status, mtktype, startdate, enddate, supervisor, termdate, personpercentage
from employees 
left join mtkper on person = mtkperson
left join timekeep a on mtkperson = a.tkinit
left join timekeep b on supervisor = b.tkinit
where status <> 'TE'
and mtkdate2 > dateadd(day,datediff(day,0,GetDate())- 0,0)
and person = 'sally'
order by person, status

退货

但是我根本不能在 termdate 不为空的地方做，因为那样我就不会得到其他三行了？

标签： sql

Answer 1

一种方法使用窗口函数来计算非 NULL 值的数量：

select x.*
from (select person, hiredate, status, mtktype, startdate, enddate,
             supervisor, termdate, personpercentage,
             count(termdate) over (partition by person) as cnt_termdate
      from employees e left join
           mtkper p
           on person = mtkperson left join
           timekeep tkp
           on mtkperson = tkp.tkinit left join
           timekeep tks
           on supervisor = tks.tkinit
      where status <> 'TE' and
            mtkdate2 > convert(date, getdate()) and
            person = 'sally'
     ) x
where cnt_person > 0
order by person, status;

我介绍了有意义的表别名。可能还有其他解决方案，但不知道这些列来自哪里，很难提出建议。