http://www.runoob.com/sql/sql-union.html
使用sql server数据库
构造数据
DECLARE @sql NVARCHAR(4000); IF OBJECT_ID('UnionTest1') IS NOT NULL BEGIN PRINT 'Table UnionTest1 exist, will drop and create'; SET @sql = 'DROP TABLE UnionTest1'; EXEC sys.sp_executesql @sql; END; ELSE PRINT 'Table UnionTest1 not exist, will create'; IF OBJECT_ID('UnionTest2') IS NOT NULL BEGIN PRINT 'Table UnionTest2 exist, will drop and create'; SET @sql = 'DROP TABLE UnionTest2'; EXEC sys.sp_executesql @sql; END; ELSE PRINT 'Table UnionTest2 not exist, will create'; CREATE TABLE UnionTest1 ( rut NVARCHAR(4000) NOT NULL , ProductCode NVARCHAR(4000) NOT NULL , ProductGroupCode NVARCHAR(4000) NULL DEFAULT NULL , [Count] INT NOT NULL ); CREATE TABLE UnionTest2 ( rut NVARCHAR(4000) NOT NULL , ProductCode NVARCHAR(4000) NULL DEFAULT NULL , ProductGroupCode NVARCHAR(4000) NOT NULL , [Count] INT NOT NULL ); INSERT INTO dbo.UnionTest1 ( rut , ProductCode , [Count] ) VALUES ( N'001' , -- rut - nvarchar(4000) N'Product1' , -- ProductCode - nvarchar(4000) 1 -- Count - int ); INSERT INTO dbo.UnionTest1 ( rut , ProductCode , [Count] ) VALUES ( N'001' , -- rut - nvarchar(4000) N'Product3' , -- ProductCode - nvarchar(4000) 3 -- Count - int ); INSERT INTO dbo.UnionTest1 ( rut , ProductCode , [Count] ) VALUES ( N'002' , -- rut - nvarchar(4000) N'Product2' , -- ProductCode - nvarchar(4000) 2 -- Count - int ); INSERT INTO dbo.UnionTest2 ( rut , ProductGroupCode , Count ) VALUES ( N'001' , -- rut - nvarchar(4000) N'ProductGroup1' , -- ProductGroupCode - nvarchar(4000) 1 -- Count - int ); INSERT INTO dbo.UnionTest2 ( rut , ProductGroupCode , Count ) VALUES ( N'002' , -- rut - nvarchar(4000) N'ProductGroup1' , -- ProductGroupCode - nvarchar(4000) 2 -- Count - int ); INSERT INTO dbo.UnionTest2 ( rut , ProductGroupCode , Count ) VALUES ( N'002' , -- rut - nvarchar(4000) N'ProductGroup3' , -- ProductGroupCode - nvarchar(4000) 3 -- Count - int );
查询两张表中的数据
SELECT * FROM dbo.UnionTest1; SELECT * FROM dbo.UnionTest2 ORDER BY ProductGroupCode;
进行union
只能对union后的结果进行排序
SELECT * FROM dbo.UnionTest1 UNION SELECT * FROM dbo.UnionTest2 ORDER BY ProductGroupCode;