用户个人信息表
userinfo
u_id 用户编号(自动编号)
u_name 用户姓名(如：张三，李四，王五,...)
u_emid 用户合同号(如：123,124,125,...)
如：
u_id u_name u_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
71 3 2012 3 1300
70 2 2012 3 1200
69 1 2012 3 1200
.
.
.
37 3 2012 2 800
36 2 2012 2 1100
35 1 2012 2 900
.
.
.
3 3 2012 1 1000
2 2 2012 1 1200
1 1 2012 1 1100
.
.
.
问题：如何汇总输出下列格式？能跨年度算上一年的吗？假如现在是2012.2.1，算出2011.11.1至2012.01.31的汇总？
该汇总数据供查询用。最好用SQL2005及以前版本能用的存储过程来汇总。
汇总ID u_emid u_name 时间段 消费汇总 备注
1 123 张三 2012.01-2012.03 3200.00 2012.01消费1100，2012.2消费900，2012.3消费1200
2 124 李四 2012.01-2012.03 3500.00 2012.01消费1200，2012.2消费1100，2012.3消费1200
3 125 王五 2012.01-2012.03 3100.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 [email protected] 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*/