有表1如下:dm yje
a163 200.00
a165 512.00
a262 233.00有表2如下:
dm eje
a111 300.00
a113 278.00
a162 568.00
a165 217.00要得到表3 (空的表3已做好)
dm yje eje hje
a111 0 300.00 300.00
a113 0 278.00 278.00
a162 0 568.00 568.00
a163 200.00 0 200.00
a165 512.00 217.00 729.00
a262 233.00 0 233.00
合计 945.00 1363.00 2308.00 求sql server的语句
a163 200.00
a165 512.00
a262 233.00有表2如下:
dm eje
a111 300.00
a113 278.00
a162 568.00
a165 217.00要得到表3 (空的表3已做好)
dm yje eje hje
a111 0 300.00 300.00
a113 0 278.00 278.00
a162 0 568.00 568.00
a163 200.00 0 200.00
a165 512.00 217.00 729.00
a262 233.00 0 233.00
合计 945.00 1363.00 2308.00 求sql server的语句
我的信箱:[email protected]
declare @1 table(dm varchar(4),yje decimal(5,2))
insert @1
select 'a163',200.00 union all
select 'a165',512.00 union all
select 'a262',233.00
--原始数据:@2
declare @2 table(dm varchar(4),eje decimal(5,2))
insert @2
select 'a111',300.00 union all
select 'a113',278.00 union all
select 'a162',568.00 union all
select 'a165',217.00select dm=isnull(a.dm,b.dm),yje=isnull(a.yje,0),eje=isnull(b.eje,0),hje=isnull(a.yje,0)+isnull(b.eje,0)
from @1 a full join @2 b on a.dm=b.dmunion allselect '合计',sum(a.yje),sum(b.eje),sum(isnull(a.yje,0)+isnull(b.eje,0))
from @1 a full join @2 b on a.dm=b.dmorder by dm/*
dm yje eje hje
---- ----------------- ------------------ -------------
a111 .00 300.00 300.00
a113 .00 278.00 278.00
a162 .00 568.00 568.00
a163 200.00 .00 200.00
a165 512.00 217.00 729.00
a262 233.00 .00 233.00
合计 945.00 1363.00 2308.00
*/
GO
CREATE TABLE DBO.T1
(DM CHAR(20),
yje decimal(18,2))
CREATE TABLE dbo.t2
(dm char(20),
eje decimal(18,2))INSERT INTO dbo.T1
SELECT 'a163',200.00
UNION ALL
SELECT 'a165',512.00
UNION ALL
SELECT 'a262',233.00INSERT INTO dbo.T2
SELECT 'a111',300.00
UNION ALL
SELECT 'a113',278.00
UNION ALL
SELECT 'a162',568.00
UNION ALL
SELECT 'a165',217.00CREATE TABLE DBO.T3
(DM CHAR(20),
yje decimal(18,2),
eje decimal(18,2),
hje decimal(18,2))INSERT INTO T3
SELECT ISNULL(A.DM,B.DM) dm
,ISNULL(A.YJE,0) yje
,ISNULL(B.EJE,0) eje
,ISNULL(A.YJE,0)+ISNULL(B.EJE,0) hje
FROM DBO.T1 AS A WITH(NOLOCK)
FULL JOIN
DBO.T2 AS B WITH(NOLOCK)
on A.DM=b.dm
UNION ALL
SELECT '合计',SUM(A.YJE),SUM(B.EJE),SUM(ISNULL(A.YJE,0)+ISNULL(B.EJE,0))
FROM DBO.T1 AS A WITH(NOLOCK)
FULL JOIN
DBO.T2 AS B WITH(NOLOCK)
on A.DM=b.dm
ORDER BY DMSELECT * FROM dbo.T3 WITH(NOLOCK)
DROP TABLE DBO.T1
DROP TABLE DBO.T2
DROP TABLE DBO.T3
/*
DM yje eje hje
-------------------- --------------------------------------- --------------------------------------- ----------a111 0.00 300.00 300.00
a113 0.00 278.00 278.00
a162 0.00 568.00 568.00
a163 200.00 0.00 200.00
a165 512.00 217.00 729.00
a262 233.00 0.00 233.00
合计 945.00 1363.00 2308.00*/
insert into A values('a163', 200.00 )
insert into A values('a165', 512.00 )
insert into A values('a262', 233.00 )
create table B(dm varchar(10),eje decimal(18,2))
insert into B values('a111', 300.00 )
insert into B values('a113', 278.00 )
insert into B values('a162', 568.00 )
insert into B values('a165', 217.00 )
goselect isnull(A.dm,B.dm) dm , isnull(A.yje,0) yje,isnull(B.eje,0) eje , isnull(A.yje,0)+ isnull(B.eje,0) hje
from A full join B on A.dm = B.dm
union all
select dm = '合计' , sum(isnull(A.yje,0)) yje,sum(isnull(B.eje,0)) eje , sum(isnull(A.yje,0)+ isnull(B.eje,0)) hje
from A full join B on A.dm = B.dmdrop table a,b/*
dm yje eje hje
---------- ------- ------ -------
a111 .00 300.00 300.00
a113 .00 278.00 278.00
a162 .00 568.00 568.00
a165 512.00 217.00 729.00
a163 200.00 .00 200.00
a262 233.00 .00 233.00
合计 945.00 1363.00 2308.00(所影响的行数为 7 行)
*/