Sql UNION 合并多个结果集并排序
1.建表语句及测试数据: ![](//img-blog.csdn.net/20131203201405859?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvYTQ5Nzc4NTYwOQ==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)
--创建表A CREATE TABLE A( A1 int NULL, A2 nvArchAr(50) NULL, A3 decimAl(18, 0) NULL ) --测试数据 INSERT INTO A VALUES (1,'A1',1) INSERT INTO A VALUES (2,'A2',1) INSERT INTO A VALUES (3,'A3',1) --创建表B CREATE TABLE B( B1 int NULL, B2 nvArchAr(50) NULL, B3 decimAl(18, 0) NULL ) --测试数据 INSERT INTO B VALUES (1,'B1',1) INSERT INTO B VALUES (2,'B2',1) INSERT INTO B VALUES (3,'B3',1) INSERT INTO B VALUES (4,'B4',1) INSERT INTO B VALUES (5,'B5',1)
2.数据预览:
A表:
B表:
3.合并数据集:
SELECT ROW_NUMBER() OVER(ORDER BY a) ROW_NUM, * FROM ( --结果集1 ( SELECT A1 AS a, A2 AS b, A3 AS c FROM A ) UNION --结果集2 ( SELECT B1 AS a, B2 AS b, B3 AS c FROM B ) )temp