表A 信息表
aid aname
1 aa
2 bb
3 cc
4 dd表 B
bid aid bje1 bje2
1 1 10 20
2 2 30 40
3 1 20 50
4 3 50 60
5 3 30 70我想通过储存过程得到一张新的表,表的结构(即统计表A的几个金额 总值):
naid naname nje1 nje2
1 aa 30 70
2 bb 30 40
3 cc 80 130
4 dd 0 0
aid aname
1 aa
2 bb
3 cc
4 dd表 B
bid aid bje1 bje2
1 1 10 20
2 2 30 40
3 1 20 50
4 3 50 60
5 3 30 70我想通过储存过程得到一张新的表,表的结构(即统计表A的几个金额 总值):
naid naname nje1 nje2
1 aa 30 70
2 bb 30 40
3 cc 80 130
4 dd 0 0
from table_a a
inner join table_b b
on a.aid = b.aid
group by a.aid, a.aname
order by a.aid
left join 表B b on b.aid = a.aid
group by a.aid,a.aname
if object_id('tempdb.dbo.#T1') is not null drop table #T1
create table #T1 (aid int,aname varchar(2))
insert into #T1
select 1,'aa' union all
select 2,'bb' union all
select 3,'cc' union all
select 4,'dd'
--> 测试数据: #T2
if object_id('tempdb.dbo.#T2') is not null drop table #T2
create table #T2 (bid int,aid int,bje1 int,bje2 int)
insert into #T2
select 1,1,10,20 union all
select 2,2,30,40 union all
select 3,1,20,50 union all
select 4,3,50,60 union all
select 5,3,30,70/*
naid naname nje1 nje2
1 aa 30 70
2 bb 30 40
3 cc 80 130
4 dd 0 0
*/
select a.aid,a.aname,isnull(b.bje1,0) bje1,isnull(b.bje2,0) bje2
from #T1 a
left outer join
(select aid,sum(bje1) bje1,sum(bje2) bje2 from #T2 group by aid) b
on a.aid=b.aid
select a.aid,a.aname,sum(bje1),sum(bje2)
from 表A a,表B b
where a.aid=b.aid
group by a.aid,a.aname
a.*,bje1=isnull(bje1,0),bje2=isnull(b.bje2,0)
from
A
left join
(select aid,bje1=sum(bje1),bje2=sum(bje2) from B group by aid )t on a.ID=b.aid
Insert @A
select 1,N'aa' union all
select 2,N'bb' union all
select 3,N'cc' union all
select 4,N'dd'declare @B table([bid] int,[aid] int,[bje1] int,[bje2] int)
Insert @B
select 1,1,10,20 union all
select 2,2,30,40 union all
select 3,1,20,50 union all
select 4,3,50,60 union all
select 5,3,30,70
select
a.*,bje1=isnull(b.bje1,0),bje2=isnull(b.bje2,0)
from
@A a
left join
(select aid,bje1=sum(bje1),bje2=sum(bje2) from @B group by aid )b on a.[aid]=b.aid(4 個資料列受到影響)(5 個資料列受到影響)
aid aname bje1 bje2
----------- ----- ----------- -----------
1 aa 30 70
2 bb 30 40
3 cc 80 130
4 dd 0 0(4 個資料列受到影響)
Insert @A
select 1,N'aa' union all
select 2,N'bb' union all
select 3,N'cc' union all
select 4,N'dd'declare @B table([bid] int,[aid] int,[bje1] int,[bje2] int)
Insert @B
select 1,1,10,20 union all
select 2,2,30,40 union all
select 3,1,20,50 union all
select 4,3,50,60 union all
select 5,3,30,70
select
a.*,bje1=sum(isnull(b.bje1,0)),bje2=sum(isnull(b.bje2,0))
from
@A a
left join
@B b on a.[aid]=b.aid
group by a.[aid],a.[aname]aid aname bje1 bje2
----------- ----- ----------- -----------
1 aa 30 70
2 bb 30 40
3 cc 80 130
4 dd 0 0(4 個資料列受到影響)
如果表A里面的自动还有好几个,而且也要在新的表里也显示是不是要 这样写
select a.aid as naid, a.aname as naname,a.aname1 as naname1,a.aname2 as naname2, a.aname3 as naname3,a.aname4 as naname4,sum(b.bje1) as nje1, sum(b.bje2) as nje2
from table_a a
inner join table_b b
on a.aid = b.aid
group by a.aid, a.aname, a.aname1, a.aname2, a.aname3, a.aname4
order by a.aid
FROM A
LEFT JOIN(
SELECT AID,SUM(bje1) NJE1,SUM(bje2) NJE2 GROUP BY AID
) B
ON A.AID =B.AID
insert into 新表
select a.aid as naid,a.aname as naname,sum(a.bje1) as nje1,sum(a.bje2) as nje2 from a,b where a.aid=b.aid
GO
CREATE PROCEDURE [dbo].[存储过程名称] AS
insert into 新表
select a.aid as naid,a.aname as naname,sum(a.bje1) as nje1,sum(a.bje2) as nje2 from a,b where a.aid=b.aid
group by a.aid,a.aname
GO
INSERT INTO @TA SELECT 1,'AA'
UNION ALL SELECT 2,'BB'
UNION ALL SELECT 3,'CC'
UNION ALL SELECT 4,'DD'DECLARE @TB TABLE (BID INT,AID INT, BJE1 INT,BJE2 INT)
INSERT INTO @TB SELECT 1,1,10,20
UNION ALL SELECT 2,2,30,40
UNION ALL SELECT 3,1,20,50
UNION ALL SELECT 4,3,50,60
UNION ALL SELECT 5,3,30,70 SELECT A.AID,A.NAME ,ISNULL(SUM(B.BJE1),0) NJE1 ,ISNULL(SUM(B.BJE2),0) NJE2 FROM @TA A
LEFT JOIN @TB B
ON A.AID=B.AID
GROUP BY A.NAME,A.AID/*
AID NAME NJE1 NJE2
----------- ----- ----------- -----------
1 AA 30 70
2 BB 30 40
3 CC 80 130
4 DD 0 0(所影响的行数为 4 行)
*/