select isnull(a.pm,b.pm) pm,isnull(a.fzr,b.fzr),isnull(a.jhsl,0),isnull(a.jhjz,0),isnull(b.chsl,0),isnull(b.chjz,0),a.jhcs,b.chcs from
(select pm,fzr,sum(jhsl) jhsl,sum(jhjz) jhjz,count(*) jhcs from tablea group by pm,fzr) a
outer join
(select pm,fzr,sum(chsl) chsl,sum(chjz) chjz,count(*) from tableb group by pm,fzr) b
on a.fzr=b.fzr and a.pm=b.pm
(select pm,fzr,sum(jhsl) jhsl,sum(jhjz) jhjz,count(*) jhcs from tablea group by pm,fzr) a
outer join
(select pm,fzr,sum(chsl) chsl,sum(chjz) chjz,count(*) from tableb group by pm,fzr) b
on a.fzr=b.fzr and a.pm=b.pm
CREATE VIEW vTab1
AS
WITH CTET AS
(
SELECT [品名],[进货数量],[进货价值],[出货数量]=0,[出货价值]=0,[负责人] FROM a
UNION ALL
SELECT [品名],[进货数量]=0,[进货价值]=0,[出货数量],[出货价值],[负责人] FROM b
)
SELECT 负责人
, 品名
, 进货数量=SUM(进货数量)
, 进货价值=SUM(进货价值)
, 出货数量=SUM(出货数量)
, 出货价值=SUM(出货价值)
, 进货次数=SUM(SIGN(进货数量))
, 出货次数=SUM(SIGN(出货数量))
FROM CTET
GROUP BY 负责人
, 品名;
AS
WITH Stock AS
(
SELECT 品名,进货数量,进货价值,出货数量=0,出货价值=0,负责人,进货次数=1,出货次数=0 FROM #a
UNION ALL
SELECT 品名,进货数量=0,进货价值=0,出货数量,出货价值,负责人,进货次数=0,出货次数=1 FROM #b
)
SELECT 负责人, 品名, SUM(进货数量) AS 进货数量,SUM(进货价值) as 进货价值, SUM(出货数量) as 出货数量,SUM(出货价值) as 出货价值,
Sum(进货次数) AS 进货次数, Sum(出货次数) AS 出货次数
FROM CTET
GROUP BY 负责人, 品名
AS
WITH Stock AS
(
SELECT 品名,进货数量,进货价值,出货数量=0,出货价值=0,负责人,进货次数=1,出货次数=0 FROM #a
UNION ALL
SELECT 品名,进货数量=0,进货价值=0,出货数量,出货价值,负责人,进货次数=0,出货次数=1 FROM #b
)
SELECT 负责人, 品名, SUM(进货数量) AS 进货数量,SUM(进货价值) as 进货价值, SUM(出货数量) as 出货数量,SUM(出货价值) as 出货价值,
Sum(进货次数) AS 进货次数, Sum(出货次数) AS 出货次数
FROM CTET
GROUP BY 负责人, 品名