刚开始接触SAS里的SQL,老师的视频里没讲太多例子,于是我又下载了SAS官方notes,发现关于Subqueries的一个问题:好像correlated subqueries都可以用noncorrelated subqueries 来解决 而且看起来更简单,不知老师是否也这样认为?
有个Correlated Subqueries的例子是The temporary table work.fa is a subset of airline.staffmaster containing the names and IDs of all flight attendants. The airline.flightschedule table contains a row for each crew member assigned to a flight for each date.要求是Determine which flight attendants were not scheduled.意思就是找出work.fa里有但airline.flightschedule里没有的人, 然后notes里用了:
proc sql;
select LastName, FirstName
from work.fa
where not exists
( select *
from airline.flightschedule
where fa.EmpID=flightschedule.EmpID)
order by EmpID;
我觉得看着这看着太别扭 觉得还是写成noncorrelated subquery简单 :
proc sql;
select LastName, FirstName
from work.fa
where EmpID in
(select EmpID
from airline.flightschedule)
order by EmpID;
但因为work.fa这个数据文件我没有 所以无法验证。烦请老师有空的时候帮忙看看我的理解是否正确。不胜感谢