union和union all的区别

UNION 写一篇
联合查询(把前后两个表的查询结果集合在前表中)
首先有个为什么需要 相同记录数？？ 记错了。应该是union两张表的查询字段数目要一致，字段类型要相似
相同的数据类型，至少是相似，可转化的。

UNION 和 UNION ALL 的区别：前者会自动剔除掉后表相同的数据，从而得到的结果集没有重复数据，
而 UNION ALL 会把所有数据都查询出来，包括重复的数据。

测试脚本：

USE [test]
GO

/****** Object:  Table [dbo].[my]    Script Date: 11/25/2015 08:57:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[my](
    [id] [int] NOT NULL,
    [num] [int] IDENTITY(0,1) NOT NULL,
    [dnum] [int] NOT NULL,
    [name] [nchar](10) NULL,
    [col] [int] NULL,
 CONSTRAINT [PK_my] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[my] ADD  CONSTRAINT [DF_my_dnum]  DEFAULT ((11)) FOR [dnum]
GO

--union 
SELECT id from test union select id from my;

id
1
2
3
4
7
8


--UNION ALL 
SELECT id from test union all select id from my;

id
1
2
3
1
3
4
7
8

 

疑问：1. 按理来说前表的结果应该全部能查出来，但是有人提出union后的记录数会少于前表，有可能吗？

2.union的使用场景：应该是两个相似的表，并且要合并结果集。

 

案例：

 

  解答思路：这里肯定要使用到group by 进行分组，但是主表和副表的表结构不一致，这个时候需要使用常量处理，然后用union去合并记录，从而生成需要的新表。

CREATE TABLE TT(
ID INT ,
A INT,
B INT
)
CREATE TABLE TA(
ID INT ,
A INT,
B INT,
C INT,
D INT,
E INT,
F INT 
)
INSERT INTO TT VALUES (1,15,1);
INSERT INTO TT VALUES (1,15,2);
INSERT INTO TT VALUES (1,15,3);
INSERT INTO TT VALUES (1,15,4);
INSERT INTO TT VALUES (2,15,1);
INSERT INTO TT VALUES (2,15,2);
INSERT INTO TT VALUES (2,15,3);
INSERT INTO TT VALUES (2,15,1);

INSERT INTO TA VALUES (1,15,1,1,550,780,220);
INSERT INTO TA VALUES (1,15,2,2,550,380,220);
INSERT INTO TA VALUES (1,15,3,3,550,580,220);
INSERT INTO TA VALUES (1,15,1,4,550,700,200);
INSERT INTO TA VALUES (1,15,2,5,500,780,220);
INSERT INTO TA VALUES (1,15,3,6,550,780,100);
INSERT INTO TA VALUES (1,15,4,7,500,480,220);
INSERT INTO TA VALUES (1,15,1,8,550,600,100);

SELECT
    * INTO TN
FROM (SELECT
    T1.AID,
    T1.A,
    T1.B,
    SUM(T1.C1) AS C_C1,
    SUM(T1.D1) AS C_D1,
    SUM(T1.E1) AS C_E1,
    SUM(T1.F1) AS C_F1
FROM ((SELECT
    ID AS AID,
    A,
    B,
    0 AS C1,
    0 AS D1,
    0 AS E1,
    0 AS F1
FROM TT) UNION (SELECT
    AID,
    A,
    B,
    SUM(C) AS C1,
    SUM(D) AS D1,
    SUM(E) AS E1,
    SUM(F) AS F1
FROM TA
GROUP BY    AID,
            A,
            B))
AS T1
GROUP BY    T1.AID,
            T1.A,
            T1.B)
T