Union

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;

 

作者:Chuck Lu    GitHub