数据库SQL 关联查询
表A
ID NAME
1 小李
2 小张
表B
ID NameID Spend
1 1 10
2 2 20
3 1 30
4 2 40
想要获取 小张 小李 一共花了多少钱
查询结果：
ID NAME Spend
1 小李 40
2 小张 60
求帮助！ ?
[解决办法]
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-11-15 13:15:36
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go
create table [A]([ID] int,[NAME] varchar(4))
insert [A]
select 1,'小李' union all
select 2,'小张'
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([ID] int,[NameID] int,[Spend] int)
insert [B]
select 1,1,10 union all
select 2,2,20 union all
select 3,1,30 union all
select 4,2,40
--------------开始查询--------------------------
select a.id,a.NAME,SUM(b.spend)[总花费]
from [A] INNER JOIN [b] ON a.id=b.nameid
GROUP BY a.id,a.NAME
----------------结果----------------------------
/*
id NAME 总花费
----------- ---- -----------
1 小李 40
2 小张 60
*/
