求一数据汇总的SQL语句

Question

字段： 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

Answer 1

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)

Answer 2

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

Answer 3

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

Answer 4

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)

Answer 5

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_namedeclare @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