表 moneydetail
id memberid money
1 cn1 100
2 cn2 500
3 cn3 600
4 cn1 1000
5 cn2 5000
6 cn1 2000表member
memberid membername
cn1 张三
cn2 李四
cn3 王五以moneydetail表中的memberid分组查询money总和
关联member表中的membername先谢过
id memberid money
1 cn1 100
2 cn2 500
3 cn3 600
4 cn1 1000
5 cn2 5000
6 cn1 2000表member
memberid membername
cn1 张三
cn2 李四
cn3 王五以moneydetail表中的memberid分组查询money总和
关联member表中的membername先谢过
where a.memberid=b.memberid
group by a.memberid,a.membername
from moneydetail a
join member b on a.memberid=b.memberid
group by a.memberid,b.membername
from member a
left join moneydetail b on a.memberid = b.memberid
group by a.memberid
if object_id('[moneydetail]') is not null drop table [moneydetail]
go
create table [moneydetail]([id] int,[memberid] varchar(3),[money] int)
insert [moneydetail]
select 1,'cn1',100 union all
select 2,'cn2',500 union all
select 3,'cn3',600 union all
select 4,'cn1',1000 union all
select 5,'cn2',5000 union all
select 6,'cn1',2000
if object_id('[member]') is not null drop table [member]
go
create table [member]([memberid] varchar(3),[membername] varchar(4))
insert [member]
select 'cn1','张三' union all
select 'cn2','李四' union all
select 'cn3','王五'
---查询---
select a.memberid,b.membername,sum(a.[money]) as [money]
from moneydetail a
join member b on a.memberid=b.memberid
group by a.memberid,b.membername---结果---
memberid membername money
-------- ---------- -----------
cn1 张三 3100
cn2 李四 5500
cn3 王五 600(3 行受影响)
-- Author : htl258(Tony)
-- Date : 2010-04-27 18:03:39
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
--------------------------------------------------------------------------
--> 生成测试数据表:moneydetailIF NOT OBJECT_ID('[moneydetail]') IS NULL
DROP TABLE [moneydetail]
GO
CREATE TABLE [moneydetail]([id] INT,[memberid] NVARCHAR(10),[money] INT)
INSERT [moneydetail]
SELECT 1,'cn1',100 UNION ALL
SELECT 2,'cn2',500 UNION ALL
SELECT 3,'cn3',600 UNION ALL
SELECT 4,'cn1',1000 UNION ALL
SELECT 5,'cn2',5000 UNION ALL
SELECT 6,'cn1',2000
GO
--SELECT * FROM [moneydetail]--> 生成测试数据表:memberIF NOT OBJECT_ID('[member]') IS NULL
DROP TABLE [member]
GO
CREATE TABLE [member]([memberid] NVARCHAR(10),[membername] NVARCHAR(10))
INSERT [member]
SELECT 'cn1',N'张三' UNION ALL
SELECT 'cn2',N'李四' UNION ALL
SELECT 'cn3',N'王五'
GO
--SELECT * FROM [member]-->SQL查询如下:
select a.[membername],sum(b.[money]) [money]
from [member] a
join [moneydetail] b
on a.[memberid]=b.[memberid]
group by a.[membername]
/*
membername money
---------- -----------
李四 5500
王五 600
张三 3100(3 行受影响)
*/
from member t left join moneydetail r
on t.memberid = r.memberid
group by t.memberid,t.membername