[sql server] 问题总结7- union-- union all--with as 实例

有三个表，Porducts(产品表),InStorages(入库表)和TheStorages(出库表)

Porducts表中数据为
id PorductsCode
1 111
2 222
3 333

InStorages表中数据为
id          PorductsCode          InStoragesNumber           InStoragesTime
1                111                            100                                2011-1-1
2                111                             20                                 2011-1-2  
3                 222                            45                                 2011-1-1
4                 222                            55                                 2011-1-3
   
TheStorages表中数据为

id         PorductsCode         TheStoragesNumber        TheStoragesTime
1            111                            60                                   2011-1-2
2            111                           220                                  2011-1-3  
3            333                            85                                   2011-1-1
4            222                            15                                   2011-1-3

入库表和出库表中分别有入库记录和出库记录，我需要将这三张表联合查询出来，
结果显示为：
id                   PorductsCode 入库数量        出库数量        时间
-------------------- ------------ ----------- ----------- ----------
1                    111          100         0           2011-01-01
2                    111          20          60          2011-01-02
3                    111          0           220         2011-01-03
4                    222          45          0           2011-01-01
5                    222          55          15          2011-01-03
6                    333          0           85          2011-01-01


查询为空的字段都用0表示

 

--------------------------

 

 

 

 

if exists (select * from sysobjects where id = OBJECT_ID('[InStorages]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [InStorages]

CREATE TABLE [InStorages] (
[id] [int]  NULL,
[PorductsCode] [varchar]  (50) NULL,
[InStoragesNumber] [int]  NULL,
[InStoragesTime] [datetime]  NULL)

INSERT [InStorages] ([id],[PorductsCode],[InStoragesNumber],[InStoragesTime]) VALUES ( 1,'111',100,'2011-1-1 0:00:00')
INSERT [InStorages] ([id],[PorductsCode],[InStoragesNumber],[InStoragesTime]) VALUES ( 2,'111',20,'2011-1-2 0:00:00')
INSERT [InStorages] ([id],[PorductsCode],[InStoragesNumber],[InStoragesTime]) VALUES ( 3,'222',45,'2011-1-1 0:00:00')
INSERT [InStorages] ([id],[PorductsCode],[InStoragesNumber],[InStoragesTime]) VALUES ( 4,'222',55,'2011-1-3 0:00:00')

 

 

 

 

if exists (select * from sysobjects where id = OBJECT_ID('[TheStorages]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [TheStorages]

CREATE TABLE [TheStorages] (
[id] [int]  NULL,
[PorductsCode] [varchar]  (50) NULL,
[TheStoragesNumber] [int]  NULL,
[TheStoragesTime] [datetime]  NULL)

INSERT [TheStorages] ([id],[PorductsCode],[TheStoragesNumber],[TheStoragesTime]) VALUES ( 1,'111',60,'2011-1-2 0:00:00')
INSERT [TheStorages] ([id],[PorductsCode],[TheStoragesNumber],[TheStoragesTime]) VALUES ( 2,'111',220,'2011-1-3 0:00:00')
INSERT [TheStorages] ([id],[PorductsCode],[TheStoragesNumber],[TheStoragesTime]) VALUES ( 3,'333',85,'2011-1-1 0:00:00')
INSERT [TheStorages] ([id],[PorductsCode],[TheStoragesNumber],[TheStoragesTime]) VALUES ( 4,'222',15,'2011-1-3 0:00:00')

 

 

 

 

 

 WITH TT
AS
(SELECT PorductsCode,InStoragesTime AS Time
FROM InStorages
UNION
SELECT PorductsCode,TheStoragesTime AS Time
FROM TheStorages)

/**用UNION  创建一个框架 ,其中包括 PorductsCode和时间两个字段，因为 UNION  会把这两列的数据分别合并到一起，所以进货和出货数量列不适合在UNION  里设置。下面的 left jion 就是 添加这2个字段的**/

SELECT ROW_NUMBER() OVER(ORDER BY GETDATE()) AS id,TT.PorductsCode,ISNULL(B.instoragesnumber,0) AS '入库数量',ISNULL(C.TheStoragesNumber,0) AS '出库数量' ,CONVERT(VARCHAR(10),TT.time,120) AS '时间'
FROM TT
LEFT JOIN InStorages B ON  B.InStoragesTime  = TT.time AND b.PorductsCode = TT.PorductsCode
left JOIN TheStorages C ON C.TheStoragesTime = TT.time AND c.PorductsCode = TT.PorductsCode

/*
id                   PorductsCode 入库数量        出库数量        时间
-------------------- ------------ ----------- ----------- ----------
1                    111          100         0           2011-01-01
2                    111          20          60          2011-01-02
3                    111          0           220         2011-01-03
4                    222          45          0           2011-01-01
5                    222          55          15          2011-01-03
6                    333          0           85          2011-01-01

(6 行受影响)
*/