求个SQL语句写法

[chengtao999]

有一个表

ID  AMOUNT  NAME  CODE  DATA
1	100		a	  a12   2016.3.5
1	100		b	  b12   2016.3.5
1	100		c	  c12   2016.3.5
1	100		d	  d34   2016.3.5
1	100		e	  e56   2016.2.5
1	100		f	  f56   2016.3.5


CODE 编码规则 第2,3位是材质属性，求得某个月份内以CODE2,3位为group，即所有相同材质的AMOUNT之和

想得到结果
SUM  CODE_23 DATA
300  12		 2016.3
100  34		 2016.3
100  56		 2016.3

[中国风]

select sum(AMOUNT) as [SUM],CONVERT(varchar(7),Date,120) as DATA,right(CODE,2) as CODE_23 from table1 group by CONVERT(varchar(7),Date,120),right(CODE,2)

[Ekun_sky]

with a(ID,AMOUNT,NAME,CODE,DATA) as(
select 1,100,'a','a12',cast('2016.3.5' as DATE) union all
select 1,100,'b','b12','2016.3.5' union all
select 1,100,'c','c12','2016.3.5' union all
select 1,100,'d','d34','2016.3.5' union all
select 1,100,'e','e56','2016.2.5' union all
select 1,100,'f','f56','2016.3.5')



select sum(AMOUNT) as sum ,SUBSTRING(code,2,2) as CODE_23,CONVERT(varchar(7),data,120) as data
 from a where CONVERT(varchar(7),data,120)='2016-03' 
group by SUBSTRING(code,2,2),CONVERT(varchar(7),data,120)

/*
sum  CODE_23 data
--------------------
300	12	2016-03
100	34	2016-03
100	56	2016-03
--------------------
*/

[chengtao999]

引用 2 楼 Ekun_sky 的回复:

with a(ID,AMOUNT,NAME,CODE,DATA) as(
select 1,100,'a','a12',cast('2016.3.5' as DATE) union all
select 1,100,'b','b12','2016.3.5' union all
select 1,100,'c','c12','2016.3.5' union all
select 1,100,'d','d34','2016.3.5' union all
select 1,100,'e','e56','2016.2.5' union all
select 1,100,'f','f56','2016.3.5')



select sum(AMOUNT) as sum ,SUBSTRING(code,2,2) as CODE_23,CONVERT(varchar(7),data,120) as data
 from a where CONVERT(varchar(7),data,120)='2016-03' 
group by SUBSTRING(code,2,2),CONVERT(varchar(7),data,120)

/*
sum  CODE_23 data
--------------------
300	12	2016-03
100	34	2016-03
100	56	2016-03
--------------------
*/

完美