求一数据汇总的SQL语句

[mzli]

字段： id(自动编号) shop_name shop_cost addtime
值： 1 shop1 ￥100 2006-3-15
2 shop2 ￥300 2006-3-18
3 shop3 ￥400 2006-3-20
4 shop4 ￥200 2006-3-21
5 shop1 ￥600 2006-3-25
6 shop2 ￥100 2006-3-26
7 shop3 ￥500 2006-3-26
8 shop4 ￥200 2006-3-27
9 shop2 ￥600 2006-3-29

要分别返回shop1,shop2,shop3,shop4,shop5……在2006年3月份shop_cost值的和。

结果值如：shop1=700,shop2=1000,shop3=900,shop4=400

[itblog]

select shop_name,addtime=convert(char(6),addtime,120),sums=isnull(sum(shop_cost),0)
from 表
group by shop_name,convert(char(6),addtime,120)

[netcup]

select shop_name,sum(shop_cost) from table where substring(rq,1,6)='2006-03' group by shop_name
这个行不行？
要么是下面这个？
select
sum(case shop_name when 'shop1' then shop_cost then shop_cost else 0 end)as shop1,
sum(case shop_name when 'shop2' then shop_cost then shop_cost else 0 end)as shop2,
sum(case shop_name when 'shop3' then shop_cost then shop_cost else 0 end)as shop3,
sum(case shop_name when 'shop4' then shop_cost then shop_cost else 0 end)as shop4,
sum(case shop_name when 'shop5' then shop_cost then shop_cost else 0 end)as shop5
from table

[huailairen]

select shop_name,sum(shop_cost) as 合计
from 表
where year(addtime)=2006 and month(addtime)=3
group by shop_name

[itblog]

declare @t table(id int identity(1,1),shop_name varchar(10),shop_cost int,addtime datetime)
insert @t select 'shop1',100,'2006-3-15'
union all select 'shop2',300,'2006-3-18'
union all select 'shop3',400,'2006-3-20'
union all select 'shop4',200,'2006-3-21'
union all select 'shop1',600,'2006-3-25'
union all select 'shop2',100,'2006-3-26'
union all select 'shop3',500,'2006-3-26'
union all select 'shop4',200,'2006-3-27'
union all select 'shop2',600,'2006-3-29'

select shop_name,addtime=convert(char(6),addtime,112),sums=isnull(sum(shop_cost),0)
from @t
group by shop_name,convert(char(6),addtime,112)

[huailairen]

create table tb( id int identity(1,1), shop_name varchar(100), shop_cost int , addtime datetime)

insert into tb(shop_name, shop_cost , addtime) values( 'shop1', 100 , '2006-3-15')
insert into tb(shop_name, shop_cost , addtime) values( 'shop2' ,300 , '2006-3-18')
insert into tb(shop_name, shop_cost , addtime) values( 'shop3' ,400 , '2006-3-20')
insert into tb(shop_name, shop_cost ,addtime) values( 'shop4' ,200 , '2006-3-21')
insert into tb(shop_name, shop_cost ,addtime) values( 'shop1' ,600 , '2006-3-25')
insert into tb(shop_name, shop_cost , addtime) values( 'shop2' ,100, '2006-3-26')
insert into tb(shop_name, shop_cost , addtime) values( 'shop3' ,500, '2006-3-26')
insert into tb(shop_name, shop_cost , addtime) values( 'shop4' ,200, '2006-3-27')
insert into tb(shop_name, shop_cost , addtime) values( 'shop2' ,600, '2006-3-29')


select shop_name,sum(shop_cost) as 合计
from tb
where year(addtime)=2006 and month(addtime)=3
group by shop_name

declare @str varchar(7800)
set @str=''

select @str=@str+',sum(case when shop_name='''+shop_name+''' then shop_cost else 0 end) as '+shop_name
from tb
group by shop_name
set @str=stuff(@str,1,1,'')

exec('select '+@str+' from tb ')
shop1 shop2 shop3 shop4
----------- ----------- ----------- -----------
700 1000 900 400

[netcup]

流浪毛高!