sql case when问题

[endlessw]

现有一张统计表
shop_id Total yeartype time
1 10 'ty' 2009-12-01
1 25 'ly' 2008-12-01
2 15 'ty' 2009-12-01
2 32 'ly' 2008-12-01
3 200 'ty' 2009-12-01
3 23 'ly' 2008-12-01

现在的需求是需要计算出去年和今年的total显示在两列中
列如:
thisyeartotal lastyeartotal
10 25
15 32
200 23

求SQL语句，case when + group by 条件可否是数据集？请教各位

[jiangshun]

--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([shop_id] int,[Total] int,[yeartype] varchar(2),[time] datetime)
insert [TB]
select 1,10,'ty','2009-12-01' union all
select 1,25,'ly','2008-12-01' union all
select 2,15,'ty','2009-12-01' union all
select 2,32,'ly','2008-12-01' union all
select 3,200,'ty','2009-12-01' union all
select 3,23,'ly','2008-12-01'

select  thisyeartotal=max(case when datediff(yy,getdate(),time)=0 then Total else 0 end),
	lastyeartotal=max(case when datediff(yy,getdate(),time)=0 then Total else 0 end)
 from [TB]
group by shop_id

/*
thisyeartotal lastyeartotal 
------------- ------------- 
10            10
15            15
200           200

（所影响的行数为 3 行）

*/
drop table TB

[endlessw]

不知其解的是
Case Total
When 13 then Total*1 else 14 end 'lastyear',
Case Total
When 14 then Total else 13 end 'this year',
这里的能不能是动态的数据集？

[jiangshun]

写错了

--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([shop_id] int,[Total] int,[yeartype] varchar(2),[time] datetime)
insert [TB]
select 1,10,'ty','2009-12-01' union all
select 1,25,'ly','2008-12-01' union all
select 2,15,'ty','2009-12-01' union all
select 2,32,'ly','2008-12-01' union all
select 3,200,'ty','2009-12-01' union all
select 3,23,'ly','2008-12-01'

select  thisyeartotal=max(case when datediff(yy,getdate(),time)=0 then Total else 0 end),
	lastyeartotal=max(case when datediff(yy,getdate(),time)=-1 then Total else 0 end)
 from [TB]
group by shop_id

/*
thisyeartotal lastyeartotal 
------------- ------------- 
10            25
15            32
200           23

（所影响的行数为 3 行）


*/
drop table TB

[Yang_]

select sum(case when year(time)=year(getdate()) then Total  else 0 end) as thisyeartotal,
sum(case when year(time)=year(getdate())-1 then Total  else 0 end) as lastyeartotal 
from tab
where time >= convert(varchar(5),dateadd(year,-1,getdate()),120)+'01-01'
and  time < convert(varchar(5),dateadd(year,1,getdate()),120)+'01-01'

[百年树人]

select 
  sum(case when year([time])=2009 then Total else 0 end) as thisyeartotal,
  sum(case when year([time])=2008 then Total else 0 end) as lastyeartotal
from
  tb
group by 
  shop_id

[Yang_]

3楼，max应该是sum吧

[jiangshun]

[Quote=引用 6 楼 yang_ 的回复:]
3楼，max应该是sum吧
[/Quote]

谢谢海爷指教

[华夏小卒]

---------------------------------------------
--> Author : js_szy
--> Target : 各位大大,小卒就是想要一朵花
--> Date   : 2009-12-02 12:13:53
--> Version: SQL Server 2005
---------------------------------------------
											    
--> 测试数据: @tb
declare @tb table (shop_id int,Total int,yeartype varchar(2),time datetime)
insert into @tb
select 1,10,'ty','2009-12-01' union all
select 1,25,'ly','2008-12-01' union all
select 2,15,'ty','2009-12-01' union all
select 2,32,'ly','2008-12-01' union all
select 3,200,'ty','2009-12-01' union all
select 3,23,'ly','2008-12-01'

select 
	shop_id,
	thisyeartotal=sum(case when yeartype='ty' then Total else 0 end),
	lastyeartotal=sum(case when yeartype='ly' then Total else 0 end)
from @tb
group by 
	shop_id

shop_id     thisyeartotal lastyeartotal
----------- ------------- -------------
1           10            25
2           15            32
3           200           23

(3 行受影响)

[misterliwei]

SELECT SUM(CASE WHEN YEARTYPE = 'TY' THEN TOTAL ELSE 0 END) AS 'thisyeartotal',
       SUM(CASE WHEN YEARTYPE = 'LY' THEN TOTAL ELSE 0 END) AS 'lastyeartotal'
FROM TB
GROUP BY SHOP_ID