洞察纵观鸿蒙next版本,如何凭借FinClip加强小程序的跨平台管理,确保企业在数字化转型中的高效运营和数据安全?
462
2023-11-29
这篇文章给大家分享的是有关表链接proc sql的示例分析的内容。小编觉得挺实用的,因此分享给大家做个参考,一起跟随小编过来看看吧。
/*21.1.1 简单连接*/ proc sql; select * from resdat.china, resdat.usa; quit /*21.1.3 内部连接*/ proc sql; select * from resdat.china, resdat.usa where china.level=usa.level; quit; /*21.1.3.1 使用表的别名*/ proc sql; select * from resdat.china as a, resdat.usa as b where a.level=b.level; quit; /*21.1.3.2 使用别名进行表的自我连接*/ proc sql; select * from resdat.china a, resdat.china b where a.level<b.level; quit; /*21.1.3.3 设定连接输出的排列顺序*/ proc sql; select * from resdat.china a, resdat.china b where a.level<b.level order by a.level desc; quit; /*21.1.6 缺失值对连接的影响*/ /* 程序一 */ proc sql; title Table A and B Joined; select a.obs A-OBS, a.stkcd, b.obs B-OBS, b.stkcd from a, b where a.stkcd= b.stkcd;/* 程序二 */proc sql; title Table Three and Four Joined; select Three.Obs 3-OBS, Three.Fdcd, Four.Obs 4-OBS, Four.Fdcd from Three, Four where Three.fdcd= Four.fdcd and three.fdcd is not missing; /* 21.1.7 从多于两个表的数据集中查询数据 */ proc sql outobs=3; select a.stkcd,b.lstknm,c.clpr from resdat.sampstk a,resdat.lstkinfo b,resdat.qttndist c where a.stkcd=b.stkcd and b.stkcd=c.stkcd and a.stkcd=c.stkcd; quit; /* 21.1.8.1 左外部连接 */ proc sql; select * from resdat.china a left join resdat.usa b on a.level=b.level; quit; /* 21.1.8.2右外部连接 */proc sql; select * from resdat.china a right join resdat.usa b on a.level=b.level; quit; /*21.1.8.3 完全外部连接*/ proc sql; select * from resdat.china a full join resdat.usa b on a.level=b.level; quit; /*21.1.9.1 与简单连接功能相同的Cross连接 */ proc sql; select * from resdat.china cross join resdat.usa; quit; /*21.1.9.2 包含所有行的Union连接 */ proc sql; select * from resdat.china union join resdat.usa; quit; /*21.1.9.3使用自动匹配连接的Natural连接*/ proc sql; select * from resdat.china union join resdat.usa; quit; /*21.1.10 连接使用COALESCE函数 */ Proc sql; select a.level,a.china,coalesce(b.level,a.level),coalesce(b.usa,a.china)as usa from resdat.china a full join resdat.usa b on a.level=b.level; quit; /* 21.2.1所有行匹配无重复值的情况 */data merge1; merge a b; by code; run; proc print data=merge1 noobs; title Table MERGE1; run; proc sql; title Table MERGE1; select a.code, a.manager, b.Assitant from a, b where a.code=b.code; quit; /* 21.2.2部分行匹配无重复值的情况 */ /* 程序一 */data merge2; merge a b; by code; run; proc print data=merge2 noobs; title Table MERGE2; run; /* 程序二*/ proc sql; select code,a.manager,b.assistant from a natural full join b; quit; /*21.2.3有重复值的情况 */ /*程序一*/ data merge3; merge a b; by code; run; proc print data=merge3 noobs; title Table MERGE3; run; /* 程序二*/ Proc sql; Title Table Merge3; Select a.code, a.manager, b.assistant From a full join b On a.code=b.code; quit; /* 21.3.1 产生单个值的子查询 */ Proc sql; Title Which Manager has the same code as Assistant Chen; Select * From a Where code eq (select code from b where assistant=Chen); Quit; /* 21.3.2 产生多个值的子查询 */ Proc sql; select stkcd,lstknm,lstdt from resdat.lstkinfo where stkcd in (select stkcd from resdat.sampstk); quit; /* 21.3.3 混合子查询 */ proc sql; select stkcd,yrret from resdat.yrret a where (select stktype from resdat.lstkinfo b where a.stkcd=b.stkcd)=A and 1jan2005d<=date<=31dec2005d; quit; /* 21.3.5 子查询的多重嵌套 */ Proc sql; select stkcd,yrret from resdat.yrret a where stkcd in (select stkcd from resdat.sampstk b where stkcd in(select stkcd from resdat.lstkinfo c where c.stktype=A)) and 1jan2005d<=date<=31dec2005d; quit; /*21.3.6 在JOIN连接中使用子查询*/ proc sql; select a.id,b.id,sqrt((a.x-b.x)**2+(a.y-b.y)**2)as dist from point a,point b where a.id lt b.id and calculated dist=(select min(sqrt((c.x-d.x)**2+(c.y-d.y)**2)) from point c,point d where c.id lt d.id); quit; /*21.5.2 由多个查询产生非重复观测 (UNION算符)*/ /*程序一*/proc sql; title A UNION B; select * from A union select * from B; quit; /* 程序二 */ proc sql; title A UNION ALL B; select * from A union all select * from B; quit; /*21.5.3 产生只属于第一个查询的观测(EXCEPT算符)*/ /*程序一*/ proc sql; title A EXCEPT B; select * from A except select * from B; quit; /*程序二*/proc sql; title A EXCEPT ALL B; select * from A except all select * from B; /*21.5.4 从多个查询中产生公共部分 (INTERSECT算符)*/ proc sql; title A INTERSECT B; select * from A intersect select * from B; /*21.5.5直接连接查询结果 (OUTER UNION算符)*/ /*程序一*/ proc sql; title A OUTER UNION B; select * from A outer union select * from B; /*程序二*/proc sql; title A OUTER UNION CORR B; select * from A outer union corr select * from B; /*21.5.6 特殊的查询合并方式 */ proc sql; title A EXCLUSIVE UNION B; (select * from A except select * from B) union (select * from B except select * from A);感谢各位的阅读!关于“表链接proc sql的示例分析”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,让大家可以学到更多知识,如果觉得文章不错,可以把它分享出去让更多的人看到吧!
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。
发表评论
暂时没有评论,来抢沙发吧~