Sql UNION 合并多个结果集并排序

来源：互联网发布：二维数组的定义方式编辑：程序博客网时间：2024/06/01 08:24

1.建表语句及测试数据：

--创建表ACREATE 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) --创建表BCREATE 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