如何从DECIMAL型数据中提取年月,并分组计算?
t1:   dt   decimal(8,0)
        at   decimal(15,2)
现有数据:
  dt                   at
20070101         100
20070102         100
20070201         100
20070202         100

希望结果:
200701           200
200702           200
如何处理?

------解决方案--------------------
declare @t1 table(dt decimal(8,0),at decimal(15,0))
insert into @t1 select 20070101,100
insert into @t1 select 20070102,100
insert into @t1 select 20070201,100
insert into @t1 select 20070202,100

select (cast(dt as int)/100) as dt,sum(at) as at from @t1 group by (cast(dt as int)/100)

/*
dt at
----------- ----------------------------------------
200701 200
200702 200
*/
------解决方案--------------------
create table t

(dt decimal(8,0),at decimal(15,2))


insert into t
select 20070101 , 100 union all
select 20070102 , 100 union all
select 20070201 , 100 union all
select 20070202 , 100


select left (cast(dt as varchar(10)),6) as dt,sum(at)as at from t
group by left(cast(dt as varchar(10)),6)


dt at
---------- ----------------------------------------
200701 200.00
200702 200.00

(2 row(s) affected)

------解决方案--------------------
select left(cast(dt as varchar),6) , sum(at) from t group by left(cast(dt as varchar),6)
------解决方案--------------------
select left(rtrim(dt),6) dt,sum(at)
from t1
group by left(rtrim(dt),6)
------解决方案--------------------
create table Tleft

(dt decimal(8,0),at decimal(15,2))


insert into Tleft
select 20070101 , 100 union all
select 20070102 , 100 union all
select 20070201 , 100 union all
select 20070202 , 100

select left(convert(varchar(8),dt),6) as dt, sum(at) from Tleft group by left(convert(varchar(8),dt),6)


dt at
---------- ----------------------------------------
200701 200.00
200702 200.00