求SQL语句。如何汇总表中的数据？请诸位高手帮忙！谢谢！
用户个人信息表
userinfo
u_id用户编号(自动编号)
u_name 用户姓名(如：张三，李四，王五,...)
u_emid用户合同号(如：123,124,125,...)
如：
u_idu_nameu_emid
1张三123
2李四124
3王五125
...
消费表
expense
ep_id 自动编号(1,2,3,...)
ep_u_id用户编号(取userinfo表u_id字段值)
ep_year年份(如2010,2011,2012,...)
ep_month月份(1~12月)
ep_money月消费
比如现在是2012年4月，expense表数据：
ep_id ep_u_id ep_year ep_month ep_money
713 2012 3 1300
702 2012 3 1200
691 2012 3 1200
.
.
.
373 2012 2 800
362 2012 2 1100
351 2012 2 900
.
.
.
33 2012 1 1000
22 2012 1 1200
11 2012 1 1100
.
.
.
问题：如何汇总输出下列格式？能跨年度算上一年的吗？假如现在是2012.2.1，算出2011.11.1至2012.01.31的汇总？
该汇总数据供查询用。最好用SQL2005及以前版本能用的存储过程来汇总。
汇总IDu_emid u_name 时间段消费汇总 备注
1123 张三 2012.01-2012.033200.00 2012.01消费1100，2012.2消费900，2012.3消费1200
2124 李四 2012.01-2012.033500.00 2012.01消费1200，2012.2消费1100，2012.3消费1200
3125 王五 2012.01-2012.033100.00 2012.01消费1000，2012.2消费800，2012.3消费1300
...
[解决办法]
- SQL code
create proc proc_total@begindate char(10),@enddate char(10)as--declare @begindate char(10)--declare @enddate char(10)--set @begindate='2012.02.01'--set @enddate='2013.01.01'select tablea.*,tableb.dept as 备注 from (select distinct u_id, a.u_emid,a.u_name,left(@begindate,7)+'-'+left(@enddate,7) as 时间段, sum(ep_money) as 消费汇总 from userinfo a join expense b on a.u_id=b.ep_u_id where left(@begindate,7)<=(ep_year+'.' +(case when len(ep_month)=1 then '0'+ep_month else ep_month end)) and left(@enddate,7)>=(ep_year+'.' +(case when len(ep_month)=1 then '0'+ep_month else ep_month end)) group by u_id, a.u_emid,a.u_name)tablea join (select distinct ep_u_id,dept= stuff((select',' + ep_year+'.'+ep_month+'消费'+cast(ep_money as varchar(20)) from(select ep_u_id,ep_year,ep_month,sum(ep_money) as ep_money from expense where left(@begindate,7)<=(ep_year+'.' +(case when len(ep_month)=1 then '0'+ep_month else ep_month end)) and left(@enddate,7)>=(ep_year+'.' +(case when len(ep_month)=1 then '0'+ep_month else ep_month end)) group by ep_u_id,ep_year,ep_month)a where a.ep_u_id=b.ep_u_id for xml path('')),1,1,'')from (select ep_u_id,ep_year,ep_month,sum(ep_money) as ep_money from expense where left(@begindate,7)<=(ep_year+'.' +(case when len(ep_month)=1 then '0'+ep_month else ep_month end)) and left(@enddate,7)>=(ep_year+'.' +(case when len(ep_month)=1 then '0'+ep_month else ep_month end)) group by ep_u_id,ep_year,ep_month)b)tableB on tablea.u_id=tableb.ep_u_id
[解决办法]
- SQL code
gocreate table userinfo (u_id int,u_name varchar(4),u_emid int)insert into userinfoselect 1,'张三',123 union allselect 2,'李四',124 union allselect 3,'王五',125gocreate table expense (ep_id int,ep_u_id int,ep_year int,ep_month int,ep_money int)insert into expenseselect 71,3,2012,3,1300 union allselect 70,2,2012,3,1200 union allselect 69,1,2012,3,1200 union allselect 37,3,2012,2,800 union allselect 36,2,2012,2,1100 union allselect 35,1,2012,2,900 union allselect 3,3,2012,1,1000 union allselect 2,2,2012,1,1200 union allselect 1,1,2012,1,1100gocreate proc proc_expense( @username varchar(20), @begintime datetime, @endtime datetime)asbegin ;with maco as( select ep_u_id as u_emid, (select top 1 u_name from userinfo where u_id=t.ep_u_id) as u_name, replace(convert(varchar(7),@begintime,111)+'-'+convert(varchar(7),@endtime,111),'/','.') as 时间段, (select sum(ep_money) from expense where ep_u_id=t.ep_u_id) as 消费汇总, ep_year,ep_month,sum(ep_money) as 备注 from expense t where cast(ltrim(ep_year)+'-'+ltrim(ep_month)+'-01' as datetime ) between @begintime and @endtime group by ep_u_id,ep_year,ep_month) select u_emid, u_name,时间段,消费汇总, [values]=stuff((select ','+ltrim(ep_year)+'.'+ltrim(ep_month)+'消费'+ltrim(备注) from maco t where u_emid=maco.u_emid and u_name=maco.u_name and 时间段=maco.时间段 and 消费汇总=maco.消费汇总 for xml path('')), 1, 1, '') from maco where u_name=@username group by u_emid, u_name,时间段,消费汇总endexec proc_expense '张三','2012-01-01','2012-03-01'/*u_emid u_name 时间段 消费汇总 values----------- ------ -------------------- ----------- -----------------------------------------------1 张三 2012.01-2012.03 3200 2012.1消费1100,2012.2消费900,2012.3消费1200*/