SQL 子查询

最新推荐文章于 2021-05-08 09:34:02 发布

原创最新推荐文章于 2021-05-08 09:34:02 发布 · 295 阅读

0 ·

CC 4.0 BY-SA版权

SQL学习日志专栏收录该内容

3 篇文章

订阅专栏

由SAS adv 63题中
Given the SAS data sets:
WORK.EMPLOYEE WORK.NEWEMPLOYEE
Name Dept Names Salary
Alan Sales Michelle 50000
Michelle Sales Paresh 60000
A SAS program is submitted and the following is written to the SAS log:

101 proc sql;
 102 select dept, name
 103 from WORK.EMPLOYEE
 104 where name=(select names
 from newemployee
 where salary > 40000)

ERROR: Subquery evaluated to more than one row.
105 ;
106 quit;
What would allow the program to successfully execute without errors?

A. Replace the where clause with: 
where EMPLOYEE.Name=(select Names delimited with‘,'
 from WORK.NEWEMPLOYEE
 where Salary > 40000);
B. Replace line 104 with:
where EMPLOYEE.Name =ANY (select Names separated with‘,'
 from WORK.NEWEMPLOYEE
 where Salary > 40000);

where型子查询：指把内部查询的结果作为外层查询的比较条件。
from型子查询：把内层的查询结果当成临时表，供外层sql再次查询。
in子查询：内层查询语句仅返回一个数据列，这个数据列的值将供外层查询语句进行比较。
exists子查询：把外层的查询结果，拿到内层，看内层是否成立，简单来说后面的返回true,外层（也就是前面的语句）才会执行，否则不执行。
any/some子查询：只要满足内层子查询中的任意一个比较条件，就返回一个结果作为外层查询条件。
all子查询：内层子查询返回的结果需同时满足所有内层查询条件。
比较运算符子查询：子查询中可以使用的比较运算符如 “>” “<” “= ” “!=”

作者：机智的豆子
来源：优快云
原文：https://blog.youkuaiyun.com/qq_39380737/article/details/81127497
版权声明：本文为博主原创文章，转载请附上博文链接！
正确样例

proc sql;
select *
from datalib.dm
where Subject_Id=any(select Subject_Id
from datalib.ds
where dsyn<'是') ;
quit;

替换

where Subject_Id in(select Subject_Id

where Subject_Id=some(select Subject_Id

where exists(select *
              from datalib.ds
             where dm.Subject_Id=ds.Subject_Id and dsyn<'是') ;

union 纵表连接表现得不如set
set 中两表纵表连接

data c;
set a b;

就直接 b接在a后面

    data c;
    set a b;
    by x y;

有by 就是a b 必须先按照 x y 排序
这样pdv读入之前就会对两个数据集中的数据先按照 x y进行比较

http://www.runoob.com/sql/sql-union.html