Oracle select intersect

 1、intersect运算
返回查询结果中相同的部分既他们的交集

SQL> select * from abc
  2  intersect
  3  select * from abc2 ;

CODE       NAME                                                                
---------- ----------                                                          
1001       张三                                                                
1002       李四                                                                
1003       老五                                                                
1004       老六 

2、minus运算
返回在第一个查询结果中与第二个查询结果不相同的那部分行记录,
即两个结果的差集

SQL> select * from abc2
  2  minus
  3  select * from abc ;

STUDENT_CO STUDENT_NA                                                          
---------- ----------                                                          
1005       xiao_三                                                             
1006       xiao_四                                                             
1007       Jay                                                                 
1008       Chou                                                                
1009       Bruce                                                               
1010       Bruce Lee                                                           

已选择6行。

3、Union-Union all
   (1)UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果
   (2)UNION ALL只是简单的将两个结果合并后就返回。如果返回的两个结果集中有重复的数据，返回的结果集就会包含重复的数据
 
   注：从效率上说，UNION ALL要比UNION快很多，如果可以确认合并的两个结果集中不包含重复的数据的话，就使用UNION ALL

使用 UNION 组合查询的结果集有两个最基本的规则：

(1)所有查询中的列数和列的顺序必须相同。

(2)数据类型必须兼容

Union:
SQL> select d.code,d.name from abc d
  2  union
  3  select e.code,e.name from abc3 e ;

CODE       NAME                                                                
---------- ----------                                                          
1000       Jolin                                                               
1001       张三                                                                
1002       李四                                                                
1003       老五                                                                
1004       老六                                                                
1005       Jay                                                                 
1006       Kven Chou                                                           
1007       Jay_Chou                                                            
1008       Jack_Lee                                                            
1009       Bruce_Lee                                                           

已选择10行。

Union all:
SQL> select d.code,d.name from abc d
  2  union all
  3  select e.code,e.name from abc3 e ;

CODE       NAME                                                                
---------- ----------                                                          
1001       张三                                                                
1002       李四                                                                
1003       老五                                                                
1004       老六                                                                
1001       张三                                                                
1002       李四                                                                
1003       老五                                                                
1004       老六                                                                
1005       Jay                                                                 
1006       Kven Chou                                                           
1007       Jay_Chou                                                            

CODE       NAME                                                                
---------- ----------                                                          
1008       Jack_Lee                                                            
1009       Bruce_Lee                                                           
1000       Jolin                                                               

已选择14行。

4、join
inner join(等值连接) 只返回两个表中联结字段相等的行
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
on 指定表间联结字段及其关系的等号 "=" 表达式, 返回 true 或 false. 当表达式返回 true 时, 则查询中包含该记录.
inner join:
SQL> select * from abc d
  2  inner join abc2 e on e.student_code=d.code ;

CODE       NAME       STUDENT_CO STUDENT_NA                                    
---------- ---------- ---------- ----------                                    
1001       张三       1001       张三                                          
1002       李四       1002       李四                                          
1003       老五       1003       老五                                          
1004       老六       1004       老六 

right join:
SQL> select * from abc d
  2  right join abc2 e on e.student_code=d.code ;

CODE       NAME       STUDENT_CO STUDENT_NA                                    
---------- ---------- ---------- ----------                                    
1001       张三       1001       张三                                          
1002       李四       1002       李四                                          
1003       老五       1003       老五                                          
1004       老六       1004       老六                                          
                      1007       Jay                                           
                      1009       Bruce                                         
                      1008       Chou                                          
                      1005       xiao_三                                       
                      1006       xiao_四                                       
                      1010       Bruce Lee                                     

已选择10行。

left join:
SQL> select * from abc d
  2  left join abc2 e on e.student_code=d.code ;

CODE       NAME       STUDENT_CO STUDENT_NA                                    
---------- ---------- ---------- ----------                                    
1001       张三       1001       张三                                          
1002       李四       1002       李四                                          
1003       老五       1003       老五                                          
1004       老六       1004       老六  

报表函数:
1、repField:
repField(子报表资源标识.子报表数据列标识)来取值，如repField(“sub.avgage”)

2、concatStr(str1,str2,str3…):
连接字符串