declare @t table(lei varchar(10),zong int)
insert into @t
select 'a',800
union all select 'b',1000
union all select 'c',1000
--你的汉字变成拼音了
declare @b table(lei varchar(10),yong int)
insert into @b
select 'a',200
union all select 'b',11
union all select 'a',20
union all select 'd',15
select isnull(a.lei,b.lei),(isnull(a.zong,0)-isnull(b.yong,0)) sheng from @t a
full join (select lei, sum(yong) yong from @b group by lei) b
on a.lei=b.lei
declare @t table(lei varchar(10),zong int)
insert into @t
select 'a',800
union all select 'b',1000
declare @b table(lei varchar(10),yong int)
insert into @b
select 'a',200
union all select 'b',11
union all select 'a',20
select b.lei,shengyu=a.zong-sum(b.yong) from @t a inner join @b b on a.lei=b.lei group by b.lei,a.zong
(
G_TYPE VARCHAR(3),
G_AMOUNT INT
)
INSERT INTO TABLE1(G_TYPE,G_AMOUNT)VALUES ('A',900)
INSERT INTO TABLE1(G_TYPE,G_AMOUNT)VALUES ('B',1000)
CREATE TABLE DETAILS
(
G_TYPE VARCHAR(3),
USED_AMOUNT INT
)
insert into details values('A',20)
insert into details values('B',60)
insert into details values('A',11)
---TEST
SELECT SUM (G_AMOUNT) FROM
(
SELECT G_TYPE,G_AMOUNT FROM TABLE1
union all
SELECT G_TYPE,USED_AMOUNT*(-1) FROM DETAILS
) RESULT
select a.g_type,a.G_amount-isnull(b.USED_AMOUNT,0) as lefts from table1 a left join
(select g_type,sum(used_amount) as USED_AMOUNT from details group by g_type) b on a.g_type=b.g_type order by a.g_type
SELECT
A.类型,(SUM(A.总数) - SUM(B.使用数)) 剩余数
FROM
(
SELECT 类型,SUM(总数) 总数
FROM 表1
GROUP BY 类型
) A,
(
SELECT 类型,SUM(使用数) 使用数
FROM 表2
GROUP BY 类型
) B
WHERE A.类型=B.类型
GROUP BY A.类型
create table tb1(类型 varchar(10), 总数 int)
insert into tb1 values('A', 900)
insert into tb1 values('B', 1000)
create table tb2(类型 varchar(10), 使用数 int)
insert into tb2 values('A', 20)
insert into tb2 values('B', 60)
insert into tb2 values('A', 11)
insert into tb2 values('C', 11)
go
select isnull(a.类型,b.类型) 类型 , isnull(a.总数,0) - isnull(b.使用数,0) 剩余数 from tb1 a
full join
(select 类型 , sum(使用数) 使用数 from tb2 group by 类型) b
on a.类型 = b.类型
order by a.类型
drop table tb1,tb2
/*
类型 剩余数
---------- -----------
A 869
B 940
C -11
create table tb1(类型 varchar(10), 总数 int)
insert into tb1 values('A', 900)
insert into tb1 values('B', 1000)
create table tb2(类型 varchar(10), 使用数 int)
insert into tb2 values('A', 20)
insert into tb2 values('B', 60)
insert into tb2 values('A', 11)
go
select a.类型 , a.总数 - isnull(b.使用数,0) 剩余数 from tb1 a
left join
(select 类型 , sum(使用数) 使用数 from tb2 group by 类型) b
on a.类型 = b.类型
order by a.类型
drop table tb1,tb2