求一条(或一组)分类汇总的SQL语句.

[Jeremiah]

有数据表table,中有列
id name type money
1 s xxx 100
2 s sdsds 100
3 s ssss 100
4 m sss 90
5 z sss 80

然后要求按照name分类汇总.得到结果如下
id name type money
1 s Multi-Row 300
4 m Single-Row 90
5 z Single-Row 80

请教高手,这样的SQL结果应该如何实现?

[孤独侠客123]

select id,name,type,sum(money) as money from table  group by name

[Jeremiah]

[Quote=引用 1 楼 lonely7345 的回复:]
SQL codeselectid,name,type,sum(money)asmoneyfromtablegroupbyname
[/Quote]
谢谢1楼的及时回答,你的语句没有问题,不过没有实现功能啊.
要求的是如果相同的name大于1条的话,type应该显示为Multi-type

[liangpei2008]

declare @t table (id int,names varchar,types varchar(10),moneys int)
insert @t select 1,'s','xxx',100
union all select 2,'s','sdsds',100
union all select 3,'s','ssss',100
union all select 4,'m','sss',90
union all select 5,'z','sss',80

select min(id) as id,
       names,
       types=(case when count(names)>1 then 'multi' else 'single' end),
       sum(moneys) as moneys
from @t
group by names
order by 1

[让你望见影子的墙]

select distinct id =(select min (id) from tb where name=t.name),
       name,
       type=case when (select count(*) from tb where type=t.type)>1 then 'Multi-Row' else 'single-row' end,
       money=(select sum(money) from tb where name=t.name)
from tb t

                  
1	s	single-row	300
4	m	Multi-Row	90
5	z	Multi-Row	80

[让你望见影子的墙]

稍微改一下

--> --> (让你望见影子的墙)生成測試數據
 
if not object_id('tb') is null
	drop table tb
Go
Create table tb([id] int,[name] nvarchar(1),[type] nvarchar(5),[money] int)
Insert tb
select 1,N's',N'xxx',100 union all
select 2,N's',N'sdsds',100 union all
select 3,N's',N'ssss',100 union all
select 4,N'm',N'sss',90 union all
select 5,N'z',N'sss',80
Go
Select * from tb


select distinct id =(select min (id) from tb where name=t.name),
       name,
       type=case when (select count(*) from tb where name=t.name)>1 then 'Multi-Row' else 'single-row' end,
       money=(select sum(money) from tb where name=t.name)
from tb t

1	s	Multi-Row	300
4	m	single-row	90
5	z	single-row	80
               

[rucypli]

select min(id)
	,name
	,case when count(*)>1 then 'multi-row' else 'single-row'
	,sum(money)
from table
group by name

[等不到来世]

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(1),[type] varchar(5),[money] int)
insert [tb]
select 1,'s','xxx',100 union all
select 2,'s','sdsds',100 union all
select 3,'s','ssss',100 union all
select 4,'m','sss',90 union all
select 5,'z','sss',80
--select * from [tb]

select  id=min(id)
		,name
		,type=case count(*) when 1 then 'Single-Row' else 'Multi-Row' end
		,money=sum(money)
from tb
group by name
order by id
--测试结果:
/*
id          name type       money
----------- ---- ---------- -----------
1           s    Multi-Row  300
4           m    Single-Row 90
5           z    Single-Row 80

(3 行受影响)
*/

[dawugui]

[Quote=引用 3 楼 liangpei2008 的回复:]
declare @t table (id int,names varchar,types varchar(10),moneys int)
insert @t select 1,'s','xxx',100
union all select 2,'s','sdsds',100
union all select 3,'s','ssss',100
union all select 4,'m','sss',90
union all select 5,'z','sss',80

select min(id) as id,
names,
types=(case when count(names)>1 then 'multi' else 'single' end),
sum(moneys) as moneys
from @t
group by names
order by 1
[/Quote]

我支持这个.

[ljhcy99]

select table.ID,table.name,type = case when AA.cnt >1 then 'multi-row'
when AA.cnt =1 then 'single-row'
else '' end,AA.money
from table,
(select min(id)as Id,sum(money) as money,count(money) as cnt,name
from table
group by name) AS AA
where AA.name=table.name
and AA.id=table.id

[Jeremiah]

谢谢大家的回答, 出了一趟远门,坐了一天的飞机,有些累,没有请及时关注贴子的情况.

一会试一下就结贴.

谢谢各位的帮助及顶贴.