--> 测试时间:2009-07-10
--> 我的淘宝:http://shop36766744.taobao.com/if object_id('[TA]') is not null drop table [TA]
create table [TA]([Id] int,[Name] varchar(1),[totalNum] int)
insert [TA]
select 1,'a',1000 union all
select 2,'b',2000 union all
select 3,'c',3000if object_id('[TB]') is not null drop table [TB]
create table [TB]([Id] int,[fkId] int,[num] int)
insert [TB]
select 1,1,100 union all
select 2,1,200 union all
select 3,1,300 union all
select 4,2,200 union all
select 5,2,200 union all
select 6,3,100 union all
select 7,3,200 union all
select 8,3,100select * from [TA]
select * from [TB]select A.id,name,totalnum=max(totalnum),
sumNum=sum(num),subNum=max(totalnum)-sum(num)
from TA A join TB B on A.ID=B.fkId
group by name,A.id/*
id name totalnum sumNum subNum
----------- ---- ----------- ----------- -----------
1 a 1000 600 400
2 b 2000 400 1600
3 c 3000 400 2600(所影响的行数为 3 行)
*/drop table [TA],[TB]
from tb1 a,tb2 b
where a.id=b.fkid group by b.fkid,a.name,a.totalNum
create table [TA]([Id] int,[Name] varchar(1),[totalNum] int)
insert [TA]
select 1,'a',1000 union all
select 2,'b',2000 union all
select 3,'c',3000if object_id('[TB]') is not null drop table [TB]
create table [TB]([Id] int,[fkId] int,[num] int)
insert [TB]
select 1,1,100 union all
select 2,1,200 union all
select 3,1,300 union all
select 4,2,200 union all
select 5,2,200 union all
select 6,3,100 union all
select 7,3,200 union all
select 8,3,100select b.fkid as id,a.name,a.totalNum ,sum(b.num) as sumNum,subNum=a.totalNum-sum(b.num)
from ta a,tb b
where a.id=b.fkid group by b.fkid,a.name,a.totalNumdrop table tb/*id name totalNum sumNum subNum
----------- ---- ----------- ----------- -----------
1 a 1000 600 400
2 b 2000 400 1600
3 c 3000 400 2600(所影响的行数为 3 行)*/
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-10 17:26:41
---------------------------------
--> 生成测试数据表:aIf not object_id('[a]') is null
Drop table [a]
Go
Create table [a]([Id] int,[Name] nvarchar(1),[totalNum] int)
Insert a
Select 1,'a',1000 union all
Select 2,'b',2000 union all
Select 3,'c',3000
Go
--Select * from a--> 生成测试数据表:bIf not object_id('[b]') is null
Drop table [b]
Go
Create table [b]([Id] int,[fkId] int,[num] int)
Insert b
Select 1,1,100 union all
Select 2,1,200 union all
Select 3,1,300 union all
Select 4,2,200 union all
Select 5,2,200 union all
Select 6,3,100 union all
Select 7,3,200 union all
Select 8,3,100
Go
--Select * from b-->SQL查询如下:
select a.[id],a.[name],a.[totalNum],
sumNum=sum(b.[num]),subNum=totalnum-sum(num)
from a
join B
on A.ID=B.fkId
group by a.id,a.name,a.totalnum
/*
id name totalNum sumNum subNum
----------- ---- ----------- ----------- -----------
1 a 1000 600 400
2 b 2000 400 1600
3 c 3000 400 2600(3 行受影响)
*/
insert into #A values(1,'a',1000)
insert into #A values(2,'b',2000)
insert into #A values(3,'c',3000)create table #B(ID int ,fkID int,num int)
insert into #B values (1,1,100)
insert into #B values (2,1,200)
insert into #B values (3,1,300)
insert into #B values (4,2,200)
insert into #B values (5,2,200)
insert into #B values (6,3,100)
insert into #B values (7,3,200)
insert into #B values (8,3,100)select g.ID,g.Name,g.TotalNum,Sum(g.Num) as sumNum ,g.TotalNum-Sum(g.Num) as subNum from
(
select a.ID,a.Name,a.TotalNum ,b.num from #A a
left join #B b on a.ID=b.fkID
) g group by g.ID,g.Name,g.TotalNum
SQL200结果
-- Author: htl258(Tony)
-- Date : 2009-07-10 17:26:41
---------------------------------
--> 生成测试数据表:aIf not object_id('[a]') is null
Drop table [a]
Go
Create table [a]([Id] int,[Name] nvarchar(1),[totalNum] int)
Insert a
Select 1,'a',1000 union all
Select 2,'b',2000 union all
Select 3,'c',3000
Go
--Select * from a--> 生成测试数据表:bIf not object_id('[b]') is null
Drop table [b]
Go
Create table [b]([Id] int,[fkId] int,[num] int)
Insert b
Select 1,1,100 union all
Select 2,1,200 union all
Select 3,1,300 union all
Select 4,2,200 union all
Select 5,2,200 union all
Select 6,3,100 union all
Select 7,3,200 union all
Select 8,3,100
Go
--Select * from b-->SQL查询如下:
select a.*,b.sumNum,subNum=a.totalnum-b.sumNum
from a
join (select fkID,sumNum=sum(num) from B group by fkID) b
on A.ID=B.fkId
/*
id name totalNum sumNum subNum
----------- ---- ----------- ----------- -----------
1 a 1000 600 400
2 b 2000 400 1600
3 c 3000 400 2600(3 行受影响)
*/
Drop table [a]
Go
Create table [a]([Id] int,[Name] nvarchar(1),[totalNum] int)
Insert a
Select 1,'a',1000 union all
Select 2,'b',2000 union all
Select 3,'c',3000
Go
--Select * from a--> 生成测试数据表:bIf not object_id('[b]') is null
Drop table [b]
Go
Create table [b]([Id] int,[fkId] int,[num] int)
Insert b
Select 1,1,100 union all
Select 2,1,200 union all
Select 3,1,300 union all
Select 4,2,200 union all
Select 5,2,200 union all
Select 6,3,100 union all
Select 7,3,200 union all
Select 8,3,100
Go
select a.*,c.sumNum,subNum=a.totalnum-c.sumNum
from a
join (select fkID,sumNum=sum(num) from B group by fkID) c
on A.ID=c.fkId
Id Name totalNum sumNum subNum
----------- ---- ----------- ----------- -----------
1 a 1000 600 400
2 b 2000 400 1600
3 c 3000 400 2600