• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

SQL语句:感觉很简单的问题,可是我不会~请帮忙

半老徐男 2008-04-04 09:33:21
表1: a

---------------
a1 a2 a3
---------------
1 10 0.5
2 20 0
3 5 5


表2: b

---------------
a1 b2 b3
---------------
1 1.5 0.5
1 5.5 0.5
3 6 5

其中a.a1=b.a1

麻烦帮忙写个SQL语句, 怎么样才能查出a2的和减去b2的和,然后按a1分组,显示表a里所有的a1信息,
现在的麻烦就在有的a.a1在表b中没有值。

即得出如下结果:
---------------------
a1 sum(a2)-sum(b2)
---------------------
1 3
2 20
3 -1
...全文
82 点赞 收藏 7
写回复
7 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
Limpire 2008-04-05
[Quote=引用 5 楼 xhj0592 的回复:]
To viva369

结果非常正确,很感谢~
[/Quote]

那还不结贴?
回复
net3 2008-04-04
SELECT A.A1,A.A2-ISNULL(B.B2,0) FROM AT A
LEFT JOIN (SELECT A1 ,SUM(B2) B2 FROM BT GROUP BY A1) B
ON A.A1=B.A1
回复
半老徐男 2008-04-04
To viva369

结果非常正确,很感谢~
回复
pt1314917 2008-04-04

declare @a table(a1 int,a2 money,a3 money)
insert into @a select 1,10,0.5
insert into @a select 2,20,0
insert into @a select 3,5,5

declare @b table(a1 int,b2 money,b3 money)
insert into @b select 1,1.5,0.5
insert into @b select 1,5.5,0.5
insert into @b select 3,6,5


select a.a1,a2-isnull(b2,0)
from @a a left join
(select a1,sum(b2)b2 from @b group by a1) b
on a.a1=b.a1
回复
viva369 2008-04-04
group by b1--多余了~
回复
viva369 2008-04-04
create table #a
(
a1 float,
a2 float,
a3 float
)

create table #b
(
b1 float,
b2 float,
b3 float
)

insert #a select 1 ,10 ,0.5
insert #a select 2 ,20 ,0
insert #a select 3 ,5 ,5

insert #b select 1 , 1.5 , 0.5
insert #b select 1 , 5.5 , 0.5
insert #b select 3,6,5
go
select #a.a1,sum(#a.a2)-isnull((select sum(b2) from #b where b1=#a.a1 group by b1),0) from #a
group by #a.a1
回复
bqb 2008-04-04
select a.a1,a.a2-b.b2 from a left join (select a1,sum(b2) from b group by a1) b on a.a1=b.a1
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-04-04 09:33
社区公告
暂无公告