请教一个SQL语句，具体要求内详！

2007-12-19 10:52:28 106 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Apollo MPC横纵向耦合控制学习笔记 Apollo MPC横纵向控制原理学习笔记阅读详情

三个表：
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语句

...全文

基于紫光同创FPGA的adf网表文件使用和黑匣子设置这个时候就可以选择将你的网表文件给到他使用，就是相当于把你的功能模块封装成了一个黑匣子一样，只有外部端口，没有内部代码；，在TOP层的子模块声明里面需要加上/*synthesis syn_black_box */进行黑匣子设置，此时子层 adf 文件作为黑盒是不会综合的，在 device map 时会将顶层综合结果与子层 adf 文件合并传递至布局布线阶段；这些都设置完毕后呢，就可以对工程进行编译和综合了，综合完毕后会生成一个.adf的网表文件，我们要使用的就是这个文件；我这里是把两个子模块的添加了哈；阅读详情

xiaozg118

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

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

请教一个SQL语句，具体要求内详！

相关推荐