group by 的小问题。
数据
------
id date money
1 20070611 100
1 20070612 100
1 20070613 100
2 20070611 100
------
希望通过 "年月 " 来group by ,sum(money)，而现在的date字段还有 "日 ".
该怎么才能得到
1 200706 300
2 200706 100
[解决办法]
select id,left([date],6) [date],sum(money) [money] from [Table] group by id,left([date],6)
[解决办法]
declare @t table(id int,date char(8),money int)
insert @t
select 1, '20070611 ',100 union all
select 1, '20070612 ',100 union all
select 1, '20070613 ',100 union all
select 2, '20070611 ',100
select
id,
substring(date,1,6) as date,
sum(money) as money
from @t
group by id,substring(date,1,6)
[解决办法]
select id,month = substring(date,1,6),money = sum(money) from table a group by id,substring(date,1,6)
