合计查询

sunfor 2011-06-24 01:47:43
表名:TB1
字段:code,name,quantity
数据:0001,名称1,10
0002,名称2,20
0003,名称3,30
0004,名称4,30

表名:TB2
字段:code quantity
数据:0001,10
0002,10
0001,20
0001,null

表名:TB3
字段:code quantity
数据:0002,10
0002,10
0003,20

想把TB2按CODE列合计后的QUANTITY列放在TB1后(tb2.quantity),
把TB3按CODE列合计后的QUANTITY列放在TB1后(tb3.quantity)。
如下:
字段:code,name,quantity,tb2.quantity,tb3.quantity
数据:0001,名称1,10,30,0
0002,名称2,20,10,20
0003,名称3,30, 0,20
0004,名称4,30, 0, 0

请问这SELECT语句的写法?
...全文
45 4 打赏 收藏 转发到动态 举报
写回复
用AI写文章
4 条回复
切换为时间正序
请发表友善的回复…
发表回复
cd731107 2011-06-24
  • 打赏
  • 举报
回复
select a.code,a.name,a.quantity,
sum(b.quantity) as [tb2.quantity],
sum(c.quantity) as [tb3.quantity]
from tb1 a , tb2 b,tb3 c
where a.code = b.code
and a.code = c.code
group by a.code,a.name,a.quantity
FlySQL 2011-06-24
  • 打赏
  • 举报
回复
if object_id('[tb1]') is not null drop table [tb1]
go
create table [tb1]([code] varchar(4),[name] varchar(5),[quantity] int)
insert [tb1]
select '0001','名称1',10 union all
select '0002','名称2',20 union all
select '0003','名称3',30 union all
select '0004','名称4',30
go
if object_id('[TB2]') is not null drop table [TB2]
go
create table [TB2]([code] varchar(4),[quantity] int)
insert [TB2]
select '0001',10 union all
select '0002',10 union all
select '0001',20 union all
select '0001',null
go
if object_id('[TB3]') is not null drop table [TB3]
go
create table [TB3]([code] varchar(4),[quantity] int)
insert [TB3]
select '0002',10 union all
select '0002',10 union all
select '0003',20
go

select a.*,isnull(b.quantity,0) as [tb2quantity],isnull(c.quantity,0) as [tb3quantity]
from tb1 a
left join (select code,sum(quantity) as quantity from tb2 group by code) b on a.code=b.code
left join (select code,sum(quantity) as quantity from tb3 group by code) c on a.code=c.code

/**
code name quantity tb2quantity tb3quantity
---- ----- ----------- ----------- -----------
0001 名称1 10 30 0
0002 名称2 20 10 20
0003 名称3 30 0 20
0004 名称4 30 0 0


(4 行受影响)
**/
FlySQL 2011-06-24
  • 打赏
  • 举报
回复
select a.*,isnull(b.quantity,0) as [tb2quantity],isnull(c.quantity,0) as [tb3quantity]
from tb1 a
left join (select code,sum(quantity) as quantity from tb2 group by code) b on a.code=b.code
left join (select code,sum(quantity) as quantity from tb3 group by code) c on a.code=c.code
AcHerat 2011-06-24
  • 打赏
  • 举报
回复

select a.code,a.name,a.quantity,sum(b.quantity) as tb2qty,sum(c.quantity) as tb3qty
from tb1 a left join tb2 b on a.code = b.code
left join tb3 c on a.code = c.code
group by a.code,a.name,a.quantity

27,579

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧