SAP HANA 集合操作 UNION/Union all/INTERSECT/EXCEPT (SAP HANA Set Operations)

SAP HANA 集合操作:SAP HANA Set Operations

官方文档参考: http://help.sap.com/hana/html/_esql_operators.html#sql_operators_set_operators

Set Operators

The operators described in this section perform set operations on the results of two or more queries.

OperatorReturned Value
UNIONCombines the results of two or more select statements or query expressions
UNION ALLCombines the results of two or more select statements or query expressions, including all duplicate rows.
INTERSECTCombines the results of two or more select statements or query expressions, and returns all common rows.
EXCEPTTakes output from the first query and then removes rows selected by the second query.

1.UNION [DISTINCT] --合并重复行UNION 和 UNION DISTINCT功能相同

Selects all unique records from all selectstatements by removing duplicates found from different select statements. UNION has the same function as UNION DISTINCT.

运算返回所有由任一查询选择的行。用UNION运算从多表返回所有行,但除去任何重复的行。

语法使用:

Select statement  UNION [DISTINCT] Select statement….;

 

2.UNION ALL--不合并重复行

运算从多个查询中返回所有行

Selects all records from all selectstatements. Duplicates are not removed

语法使用:

Select statement  UNION ALLSelect statement….;

3.INTERSECT --交集操作

用相交运算返回多个查询中所有的公共行。 无重复行

语法使用:

Select statement  INTERSECT Select statement….;

4.EXCEPT --差集操作,无重复行
相减运算求差集。用相减运算返回由第一个查询返回的行那些行不出现在第二个查询中 (第一个SELECT语句减第二个SELECT语句)

语法使用:

Select statement  EXCEPT Select statement….; 

合并重复行

select * from A union select * from B 

不合并重复行 select * from A union all select * from B 

按某个字段排序 --合并重复行

select * from ( select * from A union select * from B) AS T order by 字段名

不合并重复行

select * from ( select * from A union all select * from B) AS T order by 字段名

范例数据准备:

create column table t1 ( id int primary key, customer varchar(5), year int, product varchar(5), sales int );
 insert into t1 values(1, 'C1', 2009, 'P1', 100);
 insert into t1 values(2, 'C1', 2009, 'P2', 200);
 insert into t1 values(3, 'C1', 2010, 'P1', 50);
 insert into t1 values(4, 'C1', 2010, 'P2', 150);
 insert into t1 values(5, 'C2', 2009, 'P1', 200);
 insert into t1 values(6, 'C2', 2009, 'P2', 300);
 insert into t1 values(7, 'C2', 2010, 'P1', 100);
 insert into t1 values(8, 'C2', 2010, 'P2', 150);
 create column table t2 ( id int primary key, customer varchar(5), year int, product varchar(5), sales int );
  insert into t2 values(1, 'C1', 2011, 'P1', 100);
 insert into t2 values(2, 'C1', 2011, 'P2', 200);
 insert into t2 values(3, 'C1', 2011, 'P1', 50);
 insert into t2 values(4, 'C1', 2011, 'P2', 150);
 insert into t2 values(5, 'C2', 2011, 'P1', 200);
 insert into t2 values(6, 'C2', 2011, 'P2', 300);
 insert into t2 values(7, 'C2', 2011, 'P1', 100);
 insert into t2 values(8, 'C2', 2011, 'P2', 150);
 insert into t2 values(9, 'C1', 2011, 'P1', 100);

 insert into t2 values(10, 'C2', 2009, 'P1', 200);
 insert into t2 values(11, 'C2', 2009, 'P2', 300);
 insert into t2 values(12, 'C2', 2010, 'P1', 100);
 insert into t2 values(13, 'C2', 2010, 'P2', 150);

 

范例一: UNION

SELECT CUSTOMER,YEAR,PRODUCT,SALES FROM T1
UNION
SELECT CUSTOMER,YEAR,PRODUCT,SALES FROM T2;

 

范例二: UNION ALL


SELECT CUSTOMER,YEAR,PRODUCT,SALES FROM T1
UNION ALL
SELECT CUSTOMER,YEAR,PRODUCT,SALES FROM T2;

范例三: INTERSECT

 

SELECT CUSTOMER,YEAR,PRODUCT,SALES FROM T1
INTERSECT
SELECT CUSTOMER,YEAR,PRODUCT,SALES FROM T2;

范例四: EXCEPT

SELECT CUSTOMER,YEAR,PRODUCT,SALES FROM T1
EXCEPT
SELECT CUSTOMER,YEAR,PRODUCT,SALES FROM T2;


 

 select count(1) from (select  customer,year,product,sales from t1 union select  customer,year,product,sales from t2)    结果--->> 16

 select count(1) from (select  customer,year,product,sales from t1  UNION DISTINCT select  customer,year,product,sales from t2)  结果 --->> 16

 select count(1) from (select  customer,year,product,sales from t1 union all select  customer,year,product,sales from t2) 结果-->>21

select top 10 * from (select  customer,year,product,sales from t1 union all select  customer,year,product,sales from t2) order by customer

结果

CUSTOMER;YEAR;PRODUCT;SALES
C1;2009;P1;100
C1;2011;P2;150
C1;2011;P1;50
C1;2011;P2;200
C1;2011;P1;100
C1;2010;P1;50
C1;2009;P2;200
C1;2010;P2;150
C1;2011;P1;100
C2;2010;P2;150

 

  • 0
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值