求一条SQL语句

[whiteseal]

表A
id name bankaccount
100 aaa 123000
200 bbb 321000


表B
id amount
100 200
100 310
200 450
200 540
200 600

期望得到的结果是
id name bankaccount amount
100 aaa 123000 510
200 bbb 321000 1590

[永生天地]

select 
a.id,a.name,a.bankaccount,sum(b.amount) amount
from 表A a left join 表B b on a.id=b.id group by a.id

[百年树人]

---测试数据---
if object_id('[A]') is not null drop table [A]
go
create table [A]([id] int,[name] varchar(3),[bankaccount] int)
insert [A]
select 100,'aaa',123000 union all
select 200,'bbb',321000
if object_id('[B]') is not null drop table [B]
go
create table [B]([id] int,[amount] int)
insert [B]
select 100,200 union all
select 100,310 union all
select 200,450 union all
select 200,540 union all
select 200,600
 
---查询---
select a.id,a.name,a.bankaccount,sum(b.amount) as amount
from a,b
where a.id=b.id
group by a.id,a.name,a.bankaccount

---结果---
id          name bankaccount amount
----------- ---- ----------- -----------
100         aaa  123000      510
200         bbb  321000      1590

(2 行受影响)

[YangWenChaoX]

[Quote=引用 3 楼 josy 的回复:]

SQL code
---测试数据---
if object_id('[A]') is not null drop table [A]
go
create table [A]([id] int,[name] varchar(3),[bankaccount] int)
insert [A]
select 100,'aaa',123000 union all
select 200,'bbb',321……
[/Quote]
甚好 ！ 每次都提供测试数据。。。

[wujinyuan]

select a.*,t.amount from a left join (select b.ID,amount=sum(b.amount) from b group by b.ID) t on a.ID=b.id 

[dawugui]

select a.* , sum(b.amount) amount from a , b where a.id = b.id group by a.id , a.name , a.bankaccount

select a.* , (select sum(b.amount) from b where b.id = a.id) amount from a

[cxmcxm]

select a.*,c.amount
from a, (selecvt id,sum(amount) as amount from b group by id) c
where a.id=c.id

[wqmgxj]

select a.*,c.amount
from a, (selecvt id,sum(amount) as amount from b group by id) c
where a.id=c.id

[wqmgxj]

select a.*,c.amount
from a, (selecvt id,sum(amount) as amount from b group by id) c
where a.id=c.id

[andy_liucj]

select a.id,a.name,a.bankaccount, t.amount from a join (
select id,sum(amount) amount from b group by id)t on t.id=a.id

[ChinaITOldMan]

select a.id,a.name,a.bankaccount,sum(b.amount) as amount
from a,b
where a.id=b.id
group by a.id,a.name,a.bankaccount

[htl258_Tony]

--> 生成测试数据表: [A]
IF OBJECT_ID('[A]') IS NOT NULL
	DROP TABLE [A]
GO
CREATE TABLE [A] ([id] [int],[name] [nvarchar](10),[bankaccount] [int])
INSERT INTO [A]
SELECT '100','aaa','123000' UNION ALL
SELECT '200','bbb','321000'

--> 生成测试数据表: [B]
IF OBJECT_ID('[B]') IS NOT NULL
	DROP TABLE [B]
GO
CREATE TABLE [B] ([id] [int],[amount] [int])
INSERT INTO [B]
SELECT '100','200' UNION ALL
SELECT '100','310' UNION ALL
SELECT '200','450' UNION ALL
SELECT '200','540' UNION ALL
SELECT '200','600'

--SELECT * FROM [A]
--SELECT * FROM [B]

-->SQL查询如下:

SELECT *
FROM a 
	CROSS APPLY(
		SELECT SUM(amount)amount 
		FROM b
		WHERE a.id=b.id
	) c
/*
id          name       bankaccount amount
----------- ---------- ----------- -----------
100         aaa        123000      510
200         bbb        321000      1590

(2 行受影响)
*/

2005

[lxy_swufe]

select id，name， bankaccount ，amount
from table_A,(select table_B.id,sum(amount) from table_B group by table_B.id) table_C
where table_A.id = table_C.id;

[claro]

帮顶 回复内容太短了！

[心中的彩虹]

if object_id('a') is not null drop table a
go
create table a(id int,name varchar(3),bankaccount int)
insert a
select 100,'aaa',123000 union all
select 200,'bbb',321000

if object_id('b') is not null drop table b
go
create table b(id int,amount int)
insert b
select 100,200 union all
select 100,310 union all
select 200,450 union all
select 200,540 union all
select 200,600

select a.id,bankaccount,sum(amount) amount
from a,b
where a.id=b.id
group by a.id,bankaccount
--结果
id      bankaccount     amount
100	123000	        510
200	321000	        1590

[宇峰科技]

这里的SQL 高手回答问题好快的啊

[aaajedll]

b表GROUP BY ,SUM，然后和A表内连接就好了

[zhengtw]

select A.id, A.name, A.bankaccount, sum(B.amount) as amount
from A, B
where A.id = B.id
group by A.id, A.name, A.bankaccount

[xiaoxiao8372]

写地都挺好啊，我就不献丑了啊，哈哈！！

[lucky_2005]

select a.id,a.name,a.bankaccount,sum(b.amount)
from a,b
where a.id = b.id
group by a.id,a.name,a.bankaccount

[极夜之辉]

select 
a.id,a.name,a.bankaccount,sum(b.amount) amount
from 
a left join b
on
a.id=b.id