求SQL语句。如何汇总表中的数据?请诸位高手帮忙!谢谢!

IEEE_China 2012-04-24 10:27:36
用户个人信息表
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
...

...全文
289 12 打赏 收藏 转发到动态 举报
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
IEEE_China 2012-04-24
  • 打赏
  • 举报
回复
大家都忙着啊?怎么没人回答呢?
版主呢?高手呢?
IEEE_China 2012-04-24
  • 打赏
  • 举报
回复
感谢楼上的回答。
十三门徒 2012-04-24
  • 打赏
  • 举报
回复
create table userinfo
(
u_id int,
u_name varchar(20),
u_emid varchar(10)
)
insert into userinfo
select 1, '张三', '123' union all
select 2, '李四', '124' union all
select 3, '王五', '125'



drop table expense
go
create table expense
(ep_id int identity(1,1),
ep_u_id int,
ep_year char(4),
ep_month char(2),
ep_money float)
insert into expense
select 3, '2012', '3', 1300 union all
select 2, '2012', '3', 1200 union all
select 1, '2012', '3', 1200 union all
select 3, '2012', '4', 1300 union all
select 2, '2012', '4', 1200 union all
select 1, '2012', '5', 1200 union all
select 3, '2012', '6', 1300 union all
select 2, '2012', '7', 1200 union all
select 1, '2012', '8', 1200 union all
select 3, '2011', '3', 1300 union all
select 2, '2011', '3', 1200 union all
select 1, '2011', '2', 1200 union all
select 3, '2011', '3', 1300 union all
select 2, '2011', '3', 1200 union all
select 1, '2011', '3', 1200

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

exec proc_total @begindate='2011.01.01',@enddate='2012.12.01'

1	123	张三	2011.01-2012.12	6000	2011.2 消费1200,2011.3 消费1200,2012.3 消费1200,2012.5 消费1200,2012.8 消费1200
2 124 李四 2011.01-2012.12 6000 2011.3 消费2400,2012.3 消费1200,2012.4 消费1200,2012.7 消费1200
3 125 王五 2011.01-2012.12 6500 2011.3 消费2600,2012.3 消费1300,2012.4 消费1300,2012.6 消费1300
叶子 2012-04-24
  • 打赏
  • 举报
回复

go
create table userinfo (u_id int,u_name varchar(4),u_emid int)
insert into userinfo
select 1,'张三',123 union all
select 2,'李四',124 union all
select 3,'王五',125

go
create table expense (ep_id int,ep_u_id int,ep_year int,ep_month int,ep_money int)
insert into expense
select 71,3,2012,3,1300 union all
select 70,2,2012,3,1200 union all
select 69,1,2012,3,1200 union all
select 37,3,2012,2,800 union all
select 36,2,2012,2,1100 union all
select 35,1,2012,2,900 union all
select 3,3,2012,1,1000 union all
select 2,2,2012,1,1200 union all
select 1,1,2012,1,1100

go

create proc proc_expense
(
@username varchar(20),
@begintime datetime,
@endtime datetime
)
as
begin
;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,时间段,消费汇总
end

exec 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
*/
叶子 2012-04-24
  • 打赏
  • 举报
回复
[Quote=引用 6 楼 的回复:]

叶子,这个能写成带参数查询的吗?
我的意思就是可以汇总出任意一个用户在任意时间段的消费情况。
既可以按时间段查询,也可以按用户、时间段组合查询。

按时间段查询的话,汇总出所有用户在前2个月(时间段可以是参数)的消费情况;
按用户、时间段查询,汇总出该用户在该时间段(时间段可以是参数)的消费情况。


比如,现在是2012.2,我想查询某用户在前2个月的消费汇总情况,汇总列表也是……
[/Quote]

把开始时间和结束时间写成变量,然后封装到存储过程即可。
IEEE_China 2012-04-24
  • 打赏
  • 举报
回复
wangjunjie321

怎么调试不出结果呢
十三门徒 2012-04-24
  • 打赏
  • 举报
回复
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

存储过程 传2个参数进去 格式为'2012.01.01' '2012.12.31'
IEEE_China 2012-04-24
  • 打赏
  • 举报
回复
叶子,这个能写成带参数查询的吗?
我的意思就是可以汇总出任意一个用户在任意时间段的消费情况。
既可以按时间段查询,也可以按用户、时间段组合查询。

按时间段查询的话,汇总出所有用户在前2个月(时间段可以是参数)的消费情况;
按用户、时间段查询,汇总出该用户在该时间段(时间段可以是参数)的消费情况。


比如,现在是2012.2,我想查询某用户在前2个月的消费汇总情况,汇总列表也是这个样式。
如这个用户查询结果如下:

汇总ID u_emid u_name 时间段 消费汇总 备注
32 180 韩飞 2011.12-2012.1 3000.00 2011.12消费1600,2012.1消费1400
叶子 2012-04-24
  • 打赏
  • 举报
回复
--少了一个字段,修正一下


--> 测试数据: @userinfo
declare @userinfo table (u_id int,u_name varchar(4),u_emid int)
insert into @userinfo
select 1,'张三',123 union all
select 2,'李四',124 union all
select 3,'王五',125

--> 测试数据: @expense
declare @expense table (ep_id int,ep_u_id int,ep_year int,ep_month int,ep_money int)
insert into @expense
select 71,3,2012,3,1300 union all
select 70,2,2012,3,1200 union all
select 69,1,2012,3,1200 union all
select 37,3,2012,2,800 union all
select 36,2,2012,2,1100 union all
select 35,1,2012,2,900 union all
select 3,3,2012,1,1000 union all
select 2,2,2012,1,1200 union all
select 1,1,2012,1,1100

;with maco as(
select
ep_u_id as 汇总ID,
(select top 1 u_name from @userinfo where u_id=t.ep_u_id) as u_name,
(select top 1 u_emid from @userinfo where u_id=t.ep_u_id) as u_emid,
'2012.01-2012.03' 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 group by ep_u_id,ep_year,ep_month)

select
汇总ID,u_emid, u_name,时间段,消费汇总,
备注=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
group by 汇总ID,u_emid, u_name,时间段,消费汇总

/*
汇总ID u_emid u_name 时间段 消费汇总 备注
----------- ----------- ------ --------------- ----------- -----------------------------------------------
1 123 张三 2012.01-2012.03 3200 2012.1消费1100,2012.2消费900,2012.3消费1200
2 124 李四 2012.01-2012.03 3500 2012.1消费1200,2012.2消费1100,2012.3消费1200
3 125 王五 2012.01-2012.03 3100 2012.1消费1000,2012.2消费800,2012.3消费1300
*/
叶子 2012-04-24
  • 打赏
  • 举报
回复

--> 测试数据: @userinfo
declare @userinfo table (u_id int,u_name varchar(4),u_emid int)
insert into @userinfo
select 1,'张三',123 union all
select 2,'李四',124 union all
select 3,'王五',125

--> 测试数据: @expense
declare @expense table (ep_id int,ep_u_id int,ep_year int,ep_month int,ep_money int)
insert into @expense
select 71,3,2012,3,1300 union all
select 70,2,2012,3,1200 union all
select 69,1,2012,3,1200 union all
select 37,3,2012,2,800 union all
select 36,2,2012,2,1100 union all
select 35,1,2012,2,900 union all
select 3,3,2012,1,1000 union all
select 2,2,2012,1,1200 union all
select 1,1,2012,1,1100

;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,
'2012.01-2012.03' 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 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
group by u_emid, u_name,时间段,消费汇总

/*
u_emid u_name 时间段 消费汇总 values
----------- ------ --------------- ----------- -----------------------------------------------
1 张三 2012.01-2012.03 3200 2012.1消费1100,2012.2消费900,2012.3消费1200
2 李四 2012.01-2012.03 3500 2012.1消费1200,2012.2消费1100,2012.3消费1200
3 王五 2012.01-2012.03 3100 2012.1消费1000,2012.2消费800,2012.3消费1300
*/

叶子 2012-04-24
  • 打赏
  • 举报
回复

--> 测试数据: @userinfo
declare @userinfo table (u_id int,u_name varchar(4),u_emid int)
insert into @userinfo
select 1,'张三',123 union all
select 2,'李四',124 union all
select 3,'王五',125

--> 测试数据: @expense
declare @expense table (ep_id int,ep_u_id int,ep_year int,ep_month int,ep_money int)
insert into @expense
select 71,3,2012,3,1300 union all
select 70,2,2012,3,1200 union all
select 69,1,2012,3,1200 union all
select 37,3,2012,2,800 union all
select 36,2,2012,2,1100 union all
select 35,1,2012,2,900 union all
select 3,3,2012,1,1000 union all
select 2,2,2012,1,1200 union all
select 1,1,2012,1,1100

select
ep_u_id as u_emid,
(select top 1 u_name from @userinfo where u_id=t.ep_u_id) as u_name,
'2012.01-2012.03' 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 group by ep_u_id,ep_year,ep_month
/*
u_emid u_name 时间段 消费汇总 ep_year ep_month 备注
----------- ------ --------------- ----------- ----------- ----------- -----------
1 张三 2012.01-2012.03 3200 2012 1 1100
1 张三 2012.01-2012.03 3200 2012 2 900
1 张三 2012.01-2012.03 3200 2012 3 1200
2 李四 2012.01-2012.03 3500 2012 1 1200
2 李四 2012.01-2012.03 3500 2012 2 1100
2 李四 2012.01-2012.03 3500 2012 3 1200
3 王五 2012.01-2012.03 3100 2012 1 1000
3 王五 2012.01-2012.03 3100 2012 2 800
3 王五 2012.01-2012.03 3100 2012 3 1300
*/

--然后再列值合并一下
IEEE_China 2012-04-24
  • 打赏
  • 举报
回复
大家帮忙看看

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧