你不要这样对人家好不好?
这种临时表,我找不到数据在哪里,不过你这些已经写好的有错误,我已经修改好了
declare @购进 table(ID varchar(2),日期 smalldatetime, 数量 int)
insert @购进 values ('A' ,'2003-3-1', 10)
insert @购进 values ('C','2003-3-1', 30)
insert @购进 values ('A' ,'2003-4-1', 50)
insert @购进 values ('C','2003-4-1', 80)declare @消耗 table(ID varchar(2),日期 smalldatetime,数量 int)
insert @消耗 values('C','2003-3-1',22)
insert @消耗 values('D','2003-3-1',33)
insert @消耗 values('C','2003-4-1',40)
insert @消耗 values('D','2003-4-1',50)declare @销售 table(ID varchar(2),日期 smalldatetime,数量 int)
insert @销售 values('D','2003-3-1', 6)
insert @销售 values('E','2003-3-1', 9)
insert @销售 values('D','2003-4-1', 6)
insert @销售 values('E','2003-4-1', 9)
这种临时表,我找不到数据在哪里,不过你这些已经写好的有错误,我已经修改好了
declare @购进 table(ID varchar(2),日期 smalldatetime, 数量 int)
insert @购进 values ('A' ,'2003-3-1', 10)
insert @购进 values ('C','2003-3-1', 30)
insert @购进 values ('A' ,'2003-4-1', 50)
insert @购进 values ('C','2003-4-1', 80)declare @消耗 table(ID varchar(2),日期 smalldatetime,数量 int)
insert @消耗 values('C','2003-3-1',22)
insert @消耗 values('D','2003-3-1',33)
insert @消耗 values('C','2003-4-1',40)
insert @消耗 values('D','2003-4-1',50)declare @销售 table(ID varchar(2),日期 smalldatetime,数量 int)
insert @销售 values('D','2003-3-1', 6)
insert @销售 values('E','2003-3-1', 9)
insert @销售 values('D','2003-4-1', 6)
insert @销售 values('E','2003-4-1', 9)
set @月份='2003-3-1'select id,sum(购进) 购进,sum(消耗) 消耗,sum(销售) 销售 from (select isnull(isnull(a.id,b.id),c.id) id,a.数量 购进,b.数量 消耗,c.数量 销售 from (select id,sum(数量) 数量 from @购进 where 日期=@月份 group by id) a full join (select id,sum(数量) 数量 from @消耗 where 日期=@月份 group by id) b on a.id=b.id full join (select id,sum(数量) 数量 from @销售 where 日期=@月份 group by id) c on a.id=c.id ) tem group by id
A.购进,B.消耗,C.销售
FROM
(SELECT ID,SUM(数量) AS 购进 FROM @购进 GROUP BY ID) AS A
FULL JOIN
(SELECT ID,SUM(数量) AS 消耗 FROM @消耗 GROUP BY ID) AS B
ON A.ID=B.ID
FULL JOIN
(SELECT ID,SUM(数量) AS 销售 FROM @销售 GROUP BY ID) AS C
ON B.ID=C.ID
SELECT ISNULL(a.id,e.id) id ,ISNULL(a.日期,e.日期) 日期 ,a.数量,e.消耗,e.销售
FROM 购进 a
FULL JOIN
( SELECT ISNULL (b.id,c.id) id ,ISNULL(b.日期,c.日期) 日期, b.数量 as 消耗,c.数量 as 销售
FROM 消耗 b FULL JOIN 销售 c ON b.id=c.id AND b.日期=c.日期 ) e
ON a.id=e.id AND a.日期=e.日期 ) f
WHERE f.日期='2003-03-01'
ORDER BY f.id
FROM (SELECT ID,min(数量) AS 购进 FROM @购进 GROUP BY ID) AS A
FULL JOIN (SELECT ID,min(数量) AS 消耗 FROM @消耗 GROUP BY ID) AS B ON A.ID=B.ID
FULL JOIN (SELECT ID,min(数量) AS 销售 FROM @销售 GROUP BY ID) AS C ON B.ID=C.ID
order by id