表链接proc sql
来源:互联网 发布:淘宝购物车设计 编辑:程序博客网 时间:2024/04/26 07:23
朱世武的书<SAS 编程技术>的例子很详细,现在想想基本上涵盖了一些论坛上的问题.
/*21.1.1 简单连接*/proc sql;select * from resdat.china, resdat.usa;quit/*21.1.3 内部连接*/proc sql;select * from resdat.china, resdat.usawhere china.level=usa.level;quit;/*21.1.3.1 使用表的别名*/proc sql;select * from resdat.china as a, resdat.usa as bwhere a.level=b.level;quit;/*21.1.3.2 使用别名进行表的自我连接*/proc sql;select * from resdat.china a, resdat.china bwhere a.level<b.level;quit;/*21.1.3.3 设定连接输出的排列顺序*/proc sql;select * from resdat.china a, resdat.china bwhere a.level<b.levelorder 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.clprfrom resdat.sampstk a,resdat.lstkinfo b,resdat.qttndist cwhere 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 bon a.level=b.level;quit;/* 21.1.8.2 右外部连接 */proc sql;select * from resdat.china a right join resdat.usa bon a.level=b.level;quit;/*21.1.8.3 完全外部连接*/proc sql;select * from resdat.china a full join resdat.usa bon 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 usafrom resdat.china a full join resdat.usa bon 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 aWhere code eq (select code from b where assistant='Chen');Quit;/* 21.3.2 产生多个值的子查询 */Proc sql;select stkcd,lstknm,lstdt from resdat.lstkinfowhere 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 '1jan2005'd<=date<='31dec2005'd;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 '1jan2005'd<=date<='31dec2005'd;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
- SQL多表链接
- sql的多表链接查询中
- sql笔记整理之多表链接查询
- 表链接
- SQL Server高级内容之子查询和表链接
- SQL Server高级内容之子查询和表链接
- SQL Server高级内容:子查询和表链接
- Sql Server中增删查改语句 及 表链接
- sql的表链接left join,right join
- SQL多表链接left join、right join、inner join
- SQL 多个表链接JOIN统计条目查询
- 表链接方式
- 两表链接更新
- 多表链接
- 表链接(jion)
- 数据库多表链接
- 线性表链接存储
- android input 事件传递主要流程
- 位域
- linux 下消息队列编程
- Activity在onStop后处于什么状态
- 设计模式六大原则之(二)---里氏替换原则
- 表链接proc sql
- C# 调用带参数的控制台程序 并将命令行输出的内容实时输出至窗体中的显示控件
- android Service之一:不需和Activity交互的本地服务
- BNF和EBNF的含义及其用法
- 图像特效之怀旧风格
- uva 350 - Pseudo-Random Numbers
- A/B测试终极指南
- 官方微博运营之道的一点总结
- 通过WriteProcessMemory改写进程的内存