a
spbm jhsl
001 10
001 30
002 10
b
spbm chsl
001 25
002 3
002 2
相得到a表spbm汇总-b有spbm汇总的余数
得到spbm jhsl chsl kcsl
001 40 25 15
002 10 5 5
spbm jhsl
001 10
001 30
002 10
b
spbm chsl
001 25
002 3
002 2
相得到a表spbm汇总-b有spbm汇总的余数
得到spbm jhsl chsl kcsl
001 40 25 15
002 10 5 5
from a join b on a.spbm=b.spbm group by a.spbm
select a.spbm,sum(jhsl),sum(chsl),kcsl=sum(jhsl)-sum(chsl) from a
join b
on a.spbm=b.spbm
group by a.spbm
select T1.spbm,isnull(jhs1,0),isnull(chs1,0)jhs1-chs1 as kcs1
from (
select spbm ,sum(jhsl ) jhsl
from a
group by spbm )
T1
left join
(select spbm,sum(chs1) chs1 from b
group by spbm)
T2
on T1.spbm = T2.spbm
declare @a table(spbm char(3),jhsl int)
declare @b table(spbm char(3),chsl int)insert @a
select '001',10 union all
select '001',30 union all
select '002',10insert @b
select '001',25 union all
select '002',3 union all
select '002',2select a.*,b.chsl,kcsl=jhsl-chsl from
(select spbm,sum(jhsl) as jhsl from @a group by spbm)a
join
(select spbm,sum(chsl) as chsl from @b group by spbm)b
on a.spbm=b.spbm/*
spbm jhsl chsl kcsl
001 40 25 15
002 10 5 5 */
DECLARE @A TABLE(
spbm varchar(10),
jhsl INT
)
INSERT INTO @A
SELECT '001', 10 UNION ALL
SELECT '001', 30 UNION ALL
SELECT '003', 30 UNION ALL
SELECT '002', 10 DECLARE @B TABLE(
spbm varchar(10),
chsl INT
)
INSERT INTO @B
SELECT '001', 25 UNION ALL
SELECT '002', 3 UNION ALL
SELECT '002', 2 select a.*,ISNULL(b.chsl,0) 'JHSL',kcsl=ISNULL(jhsl-chsl,A.JHSL) from
(select spbm,sum(jhsl) as jhsl from @a group by spbm)a
LEFT join
(select spbm,sum(chsl) as chsl from @b group by spbm)b
on a.spbm=b.spbm--7l加强版
----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-07-26 21:49:54
----------------------------------------------------------------
--> 测试数据:[a1]
if object_id('[a]') is not null drop table [a]
create table [a]([spbm] varchar(3),[jhsl] int)
insert [a]
select '001',10 union all
select '001',30 union all
select '002',10
--> 测试数据:[b]
if object_id('[b]') is not null drop table [b]
create table [b]([spbm] varchar(3),[chsl] int)
insert [b]
select '001',25 union all
select '002',3 union all
select '002',2
--------------开始查询--------------------------
select
a.*,
isnull(b.chsl,0) as 'jhsl',
kcsl=isnull(jhsl-chsl,A.JHSL)
from
(select spbm,sum(jhsl) as jhsl from a group by spbm)a,
(select spbm,sum(chsl) as chsl from b group by spbm)b
where
a.spbm=b.spbm
----------------结果----------------------------
/*spbm jhsl jhsl kcsl
---- ----------- ----------- -----------
001 40 25 15
002 10 5 5(所影响的行数为 2 行)
*/
注意那个“有”字