SAP HANA SQL语句UNION 和 UNION ALL的用法

最新推荐文章于 2023-12-17 19:45:00 发布

qptufly

最新推荐文章于 2023-12-17 19:45:00 发布

阅读量4.3k

点赞数 1

分类专栏： SAP HANA

本文链接：https://blog.youkuaiyun.com/qptufly/article/details/8475186

版权

SAP HANA 专栏收录该内容

80 篇文章

订阅专栏

UNION ALL--不合并重复行

Selects all records from all selectstatements. Duplicates are not removed

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.

合并重复行

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);

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) 结果-->>17

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