Full JOIN 的定义
来源:互联网 发布:openssl linux 编辑:程序博客网 时间:2024/06/02 21:17
Full JOIN 的定义:All records from both tables - identical to a union of left join and rigth join
- --tbl_A:
- --STCD STNM
- --10001 站点A
- --10002 站点B
- --10003 站点C
- --10004 站点D
- --10005 站点E
- If object_id('tbl_A') is not NULL
- drop table tbl_A
- Go
- Create table tbl_A (STCD int,STNM varchar(100))
- Go
- insert into tbl_A
- select 10001,'站点A' Union all
- select 10002,'站点B' Union all
- select 10003,'站点C' Union all
- select 10004,'站点D' Union all
- select 10005,'站点E'
- --tbl_B:
- --STCD TM VAL1 VAL2
- --...
- --10003 2008-12-01 1 1
- --10003 2008-12-03 3 3
- --10003 2008-12-07 7 7
- --...
- If object_id('tbl_B') is not NULL
- drop table tbl_B
- Go
- Create table tbl_B (STCD int,TM datetime,VAL1 int,VAL2 int)
- Go
- insert into tbl_B
- select 10003,'2008-12-01',1,1 Union all
- select 10003,'2008-12-03',3,3 Union all
- select 10003,'2008-12-07',7,7
- --tbl_C:
- --STCD TM VAL3 VAL4
- --...
- --10003 2008-12-02 4 4
- --10003 2008-12-03 6 6
- --10003 2008-12-04 8 8
- --10003 2008-12-07 14 14
- --...
- If object_id('tbl_C') is not NULL
- drop table tbl_C
- Go
- Create table tbl_C (STCD int,TM datetime,VAL3 int,VAL4 int)
- Go
- insert into tbl_C
- select 10003,'2008-12-02',4,4 Union all
- select 10003,'2008-12-03',6,6 Union all
- select 10003,'2008-12-04',8,8 Union all
- select 10003,'2008-12-07',14,14
- /*
- tbl_A:站点代码、站点名称的索引表
- tbl_B:存储所有站点的某一类监测数据(如:VAL1、VAL2)
- tbl_C:存储所有站点的另一类监测数据(如:VAL3、VAL4)
- tbl_B和tbl_C的TM(时间)字段数值不一定相同
- 要显示满足某站点的所有不同时间的VAL1、VAL2、VAL3、VAL4数据。
- */
- --全连接
- select ISNULL(b.STCD,c.STCD) STCD,
- ISNULL(convert(varchar(100),b.TM,23),convert(varchar(100),c.TM,23)) TM,
- ISNULL(cast(b.VAL1 as varchar),'-') VAL1,
- ISNULL(cast(b.VAL2 as varchar),'-') VAL2,
- ISNULL(cast(c.VAL3 as varchar),'-') VAL3,
- ISNULL(cast(c.VAL4 as varchar),'-') VAL4
- from tbl_B b
- FULL join tbl_C c on b.STCD=c.STCD and b.TM=c.TM
- /*
- STCD TM VAL1 VAL2 VAL3 VAL4
- 10003 2008-12-01 1 1 - -
- 10003 2008-12-03 3 3 6 6
- 10003 2008-12-07 7 7 14 14
- 10003 2008-12-02 - - 4 4
- 10003 2008-12-04 - - 8 8
- */
- --左外连接
- select ISNULL(b.STCD,c.STCD) STCD,ISNULL(convert(varchar(100),b.TM,23),convert(varchar(100),c.TM,23)) TM,
- ISNULL(cast(b.VAL1 as varchar),'-') VAL1,
- ISNULL(cast(b.VAL2 as varchar),'-') VAL2,
- ISNULL(cast(c.VAL3 as varchar),'-') VAL3,
- ISNULL(cast(c.VAL4 as varchar),'-') VAL4
- from tbl_B b
- Left join tbl_C c on b.STCD=c.STCD and b.TM=c.TM
- /*
- STCD TM VAL1 VAL2 VAL3 VAL4
- 10003 2008-12-01 1 1 - -
- 10003 2008-12-03 3 3 6 6
- 10003 2008-12-07 7 7 14 14
- */
- --右外连接
- select ISNULL(b.STCD,c.STCD) STCD,ISNULL(convert(varchar(100),b.TM,23),convert(varchar(100),c.TM,23)) TM,
- ISNULL(cast(b.VAL1 as varchar),'-') VAL1,
- ISNULL(cast(b.VAL2 as varchar),'-') VAL2,
- ISNULL(cast(c.VAL3 as varchar),'-') VAL3,
- ISNULL(cast(c.VAL4 as varchar),'-') VAL4
- from tbl_B b
- Right join tbl_C c on b.STCD=c.STCD and b.TM=c.TM
- /*
- STCD TM VAL1 VAL2 VAL3 VAL4
- 10003 2008-12-02 - - 4 4
- 10003 2008-12-03 3 3 6 6
- 10003 2008-12-04 - - 8 8
- 10003 2008-12-07 7 7 14 14
- */
- Full JOIN 的定义
- sql的inner join/left join/right join/full join
- inner join, left join, right join, full join 的区别
- inner join, left join, right join, full join 的区别
- inner join, left join, right join, full join 的区别?
- left join、right join、inner join、full Join的区别
- MySQL Full Join的实现
- SQL的join链接之inner join和full join
- FULL JOIN
- 【SQL】inner join、outer join、cross join和left join、right join、full join的区别
- mysql的full join的实现
- 关于sql的full outer join,left join,right join,inner join, cross join 的说明
- SQL的JOIN语法解析(inner join, left join, right join, full outer join的区别)
- SQL的JOIN语法解析(inner join, left join, right join, full outer join的区别)
- 一个使用FULL JOIN的例子
- full join的一个小测试
- oracle full join on的用法
- Mysql实现full join的替换方法
- MD5算法研究
- defer属性 指示JS执行时间 Internet Explorer无法打开Internet站点......已终止操作 原由 解决办法
- 几百个会员的网站一个月也收入近万你信吗?
- Log4j简介
- iFrame研究
- Full JOIN 的定义
- 详细讲解Quartz如何从入门到精通
- 操作Web.config的AppSettings
- new IT- and business opportunities by uniting SaaS, SOA and Web 2.0
- 小常识:在Oracle数据库中如何给用户解锁
- java 编码
- Rational Rose 2003 笔记
- PHP使用eval(gzinflate(str_rot13(base64_decode('BASE64加密后内容'))))核心代码的解密
- 数据库导出实际使用中的问题