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

求一存儲過程

bb_chen 2007-12-18 12:27:56
如題:
表一:
col1 col2
A 100
B 200
A 300
C 400
B 500
表二:
col1 col2 col3
A a1 10
A a2 20
A a3 20
B b1 40

根據表二的紀錄計算結果為:
A 50 400
B 40 700

說明結果中的50為表二中的10+20+20,40為表二中40,400為表一中的100+300,200為表一中的200+500

謝謝!
...全文
44 点赞 收藏 7
写回复
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
areswang 2007-12-18
create table tb1(col1 varchar(10), col2 int)
go
insert into tb1 values('A', 100)
insert into tb1 values('B', 200)
insert into tb1 values('A', 300)
insert into tb1 values('C', 400)
insert into tb1 values('B', 500)
create table tb2(col1 varchar(10), col2 varchar(10), col3 int)
insert into tb2 values('A', 'a1', 10 )
insert into tb2 values('A', 'a2', 20 )
insert into tb2 values('A', 'a3', 20 )
insert into tb2 values('B', 'b1', 40 )

go
create proc test
as
select a.Col1,b.col2,a.col3
from
(select Col1 ,sum(col2) as col3 from tb1 group by col1) a
inner join
(select Col1,sum(col3) as col2 from tb2 group by col1) b
on a.Col1=b.Col1

exec test

drop table tb1
drop table tb2
drop proc test
回复
dawugui 2007-12-18
--如果取两表不匹配的col1,考虑用全连接full join 或左连接,右连接.
create table tb1(col1 varchar(10), col2 int)
insert into tb1 values('A', 100)
insert into tb1 values('B', 200)
insert into tb1 values('A', 300)
insert into tb1 values('C', 400)
insert into tb1 values('B', 500)
create table tb2(col1 varchar(10), col2 varchar(10), col3 int)
insert into tb2 values('A', 'a1', 10 )
insert into tb2 values('A', 'a2', 20 )
insert into tb2 values('A', 'a3', 20 )
insert into tb2 values('B', 'b1', 40 )
go

select isnull(m.col1,n.col1) col1 , isnull(n.col3,0) col3 , isnull(m.col2,0) col2 from
(select col1 , col2 = sum(col2) from tb1 group by col1) m full join
(select col1 , col3 = sum(col3) from tb2 group by col1) n
on m.col1 = n.col1

drop table tb1,tb2

/*
col1 col3 col2
---------- ----------- -----------
A 50 400
B 40 700
C 0 400

(所影响的行数为 3 行)
*/
回复
dawugui 2007-12-18
create table tb1(col1 varchar(10), col2 int)
insert into tb1 values('A', 100)
insert into tb1 values('B', 200)
insert into tb1 values('A', 300)
insert into tb1 values('C', 400)
insert into tb1 values('B', 500)
create table tb2(col1 varchar(10), col2 varchar(10), col3 int)
insert into tb2 values('A', 'a1', 10 )
insert into tb2 values('A', 'a2', 20 )
insert into tb2 values('A', 'a3', 20 )
insert into tb2 values('B', 'b1', 40 )
go

select m.col1 , n.col3 , m.col2 from
(select col1 , col2 = sum(col2) from tb1 group by col1) m,
(select col1 , col3 = sum(col3) from tb2 group by col1) n
where m.col1 = n.col1

drop table tb1,tb2

/*
col1 col3 col2
---------- ----------- -----------
A 50 400
B 40 700

(所影响的行数为 2 行)
*/
回复
lwl0606 2007-12-18


select a.Col1,T_1,T_2
from
(select Col1 ,sum(col2) as T_1 from table1 group by col1) a
inner join
(select Col1,sum(col3) as T_2 from table2 group by col1) b
on a.Col1=b.Col1


view 就可以,如果要存储过程 写在存储过程里面就可以了
回复
dawugui 2007-12-18
select m.col1 , n.col3 , m.col2 from
(select col1 , col2 = sum(col2) from tb1 group by col1) m,
(select col1 , col3 = sum(col3) from tb2 group by col1) n
where m.col1 = n.col1
回复
bb_chen 2007-12-18
謝謝!
回复
中国风 2007-12-18
create table tb1(col1 varchar(10), col2 int)
insert into tb1 values('A', 100)
insert into tb1 values('B', 200)
insert into tb1 values('A', 300)
insert into tb1 values('C', 400)
insert into tb1 values('B', 500)
create table tb2(col1 varchar(10), col2 varchar(10), col3 int)
insert into tb2 values('A', 'a1', 10 )
insert into tb2 values('A', 'a2', 20 )
insert into tb2 values('A', 'a3', 20 )
insert into tb2 values('B', 'b1', 40 )
go

select
a.col1,
[col2]=(select sum(col3) from tb2 where col1=a.col1),
[col3]=(select sum(col2) from tb1 where col1=a.col1)
from
tb1 a,tb2 b
where
a.col1=b.col1
group by a.col1


col1 col2 col3
---------- ----------- -----------
A 50 400
B 40 700

(所影响的行数为 2 行)

回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

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