有三个表，Porducts(产品表),InStorages(入库表)和TheStorages(出库表)Porducts表中数据为id PorductsCode1 1112 2223 333InStorages表中数据为id PorductsCode InStoragesNumber InStoragesTime1 111 100 2011-1-12 111 20 2011-1-2 3 222 45 2011-1-14 222 55 2011-1-3 TheStorages表中数据为
id PorductsCode TheStoragesNumber TheStoragesTime1 111 60 2011-1-22 111 220 2011-1-3 3 333 85 2011-1-14 222 15 2011-1-3入库表和出库表中分别有入库记录和出库记录，我需要将这三张表联合查询出来，结果显示为：id PorductsCode 入库数量 出库数量 时间-------------------- ------------ ----------- ----------- ----------1 111 100 0 2011-01-012 111 20 60 2011-01-023 111 0 220 2011-01-034 222 45 0 2011-01-015 222 55 15 2011-01-036 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 TTAS(SELECT PorductsCode,InStoragesTime AS TimeFROM InStoragesUNION SELECT PorductsCode,TheStoragesTime AS TimeFROM 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 TTLEFT JOIN InStorages B ON B.InStoragesTime = TT.time AND b.PorductsCode = TT.PorductsCodeleft JOIN TheStorages C ON C.TheStoragesTime = TT.time AND c.PorductsCode = TT.PorductsCode
/*id PorductsCode 入库数量 出库数量 时间-------------------- ------------ ----------- ----------- ----------1 111 100 0 2011-01-012 111 20 60 2011-01-023 111 0 220 2011-01-034 222 45 0 2011-01-015 222 55 15 2011-01-036 333 0 85 2011-01-01(6 行受影响)*/
