导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

请教一个SQL语句,具体要求内详!

xiaozg118 2007-12-19 10:52:28
三个表:
table_primary(主表):id(char 3)
table_log1(子表1) id(char 3) 对应table_primary中的id num (int)
table_log2(子表2) id(char 3) 对应table_primary中的id num (int)

现在在主表里有数据
id
001
002

子表1里有数据
id num
001 2
001 3
002 1

子表2里有数据
id num
001 4
002 2

最终我想要的结果是(也就是对他们分别汇总)
id log1num log2num
001 5 4
002 1 2

请教sql语句
...全文
50 点赞 收藏 10
写回复
10 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
areswang 2007-12-19
create table table_primary([id] varchar(10) )
insert into table_primary select '001'
union all
select '002'

go
create table table_log1([id] varchar(10),num int)
insert into table_log1 select '001',2
union all
select '001',3
union all
select '002',1
go

create table table_log2([id] varchar(10),num int)
insert into table_log2 select '001',4
union all
select '002',2
go

select a.[id],b.log1num1,c.log1num2
from table_primary a
left join (select [id], sum(num) log1num1 from table_log1 group by [id]) b
on a.[id] =b.[id]
left join (select [id],sum(num) log1num2 from table_log2 group by [id]) c
on a.[id] = c.[id]

drop table table_primary
drop table table_log1
drop table table_log2
回复
dobear_0922 2007-12-19
select id ,
log1num=(select isnull(sum(num),0) from table_log1 where id=M.id),
log2num=(select isnull(sum(num),0) from table_log2 where id=M.id)
from table_primary M
回复
dawugui 2007-12-19
create table 主表(id varchar(10))
insert into 主表 values('001')
insert into 主表 values('002')
create table 子表1(id varchar(10), num int)
insert into 子表1 values('001', 2)
insert into 子表1 values('001', 3)
insert into 子表1 values('002', 1)
create table 子表2(id varchar(10), num int)
insert into 子表2 values('001', 4)
insert into 子表2 values('002', 2)
go

select t.id ,
log1num = (select sum(num) from 子表1 where id = t.id),
log1num = (select sum(num) from 子表2 where id = t.id)
from 主表 t


drop table 主表,子表1,子表2

/*
id log1num log2num
---------- ----------- -----------
001 5 4
002 1 2

(2 行受影响)
*/

回复
dawugui 2007-12-19
--另外一种.
create table 主表(id varchar(10))
insert into 主表 values('001')
insert into 主表 values('002')
create table 子表1(id varchar(10), num int)
insert into 子表1 values('001', 2)
insert into 子表1 values('001', 3)
insert into 子表1 values('002', 1)
create table 子表2(id varchar(10), num int)
insert into 子表2 values('001', 4)
insert into 子表2 values('002', 2)
go

select t.id ,
log1num = (select sum(num) from 子表1 where id = t.id),
log1num = (select sum(num) from 子表2 where id = t.id)
from 主表 t
group by id

drop table 主表,子表1,子表2

/*
id log1num log2num
---------- ----------- -----------
001 5 4
002 1 2

(2 行受影响)
*/

回复
dawugui 2007-12-19
create table 主表(id varchar(10))
insert into 主表 values('001')
insert into 主表 values('002')
create table 子表1(id varchar(10), num int)
insert into 子表1 values('001', 2)
insert into 子表1 values('001', 3)
insert into 子表1 values('002', 1)
create table 子表2(id varchar(10), num int)
insert into 子表2 values('001', 4)
insert into 子表2 values('002', 2)
go

select t.id , isnull(t1.log1num,0) log1num , isnull(t2.log2num,0) log2num from 主表 t
left join (select id , sum(num) log1num from 子表1 group by id) t1 on t.id = t1.id
left join (select id , sum(num) log2num from 子表2 group by id) t2 on t.id = t2.id

drop table 主表,子表1,子表2

/*
id log1num log2num
---------- ----------- -----------
001 5 4
002 1 2

(2 行受影响)
*/

回复
chuifengde 2007-12-19
简化一下也可:
select id ,
log1num=(select sum(num) from b where id=a.id),
log2num=(select sum(num) from c where id=a.id)
from a
回复
chuifengde 2007-12-19
create table a(id char(3))
insert a select '001' union all select '002'
create table b(id char(3),num int)
insert b select '001',2 union all select '001',3 union all select '002',1
create table c(id char(3),num int)
insert c select '001',4 union all select '002',2

select id ,
log1num=(select num from (select id,sum(num) num from b group by id)aa where id=a.id),
log2num=(select num from (select id,sum(num) num from c group by id)bb where id=a.id)
from a
--result
/*id log1num log2num
---- ----------- -----------
001 5 4
002 1 2

(所影响的行数为 2 行)*/
回复
dawugui 2007-12-19
select a.id , isnull(t1.log1num,0) log1num , isnull(t2.log2num,0) log2num from 主表 t
left join (select id , sum(num) log1num from 子表1 group by id) t1 on t.id = t1.id
left join (select id , sum(num) log2num from 子表2 group by id) t2 on t.id = t2.id
回复
dobear_0922 2007-12-19
--try
select M.id, log1num=isnull(S1.num,0), log2num=isnull(S2.num,0)
from table_primary M
left join (select id, num=sum(num) from table_log1 group by id) S1 on M.id=S1.id
left join (select id, num=sum(num) from table_log2 group by id) S2 on M.id=S2.id
回复
rouqu 2007-12-19
select  t1.id,a.num1,b.num2 from 
t1 left join
(select id, sum(num) num1 from t2 group by id) a on t1.id=a.id
left join (select id, sum(num) num2 from t3 group by id) b on t1.id=b.id
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告