PS:今天上午，非常郁悶，有很多簡單基礎的問題搞得我有些迷茫，哎，代碼幾天不寫就忘。目前又不當COO，還是得用心記代碼哦！

????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

文章結束給大家分享下程序員的一些笑話語錄： 　一邊用著越獄的ip，一邊拜喬幫主的果粉自以為是果粉，其實在喬幫主的眼里是不折不扣的叛徒。

總結

以上是生活随笔為你收集整理的行、重复-SAP HANA 集合操作 UNION/Union all/INTERSECT/EXCEPT (SAP HANA Set Operations)-by小雨...的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。