34,575
社区成员
发帖
与我相关
我的任务
分享
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
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
*/
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
--> 测试数据: @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
*/
--> 测试数据: @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
*/
--> 测试数据: @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
*/
--然后再列值合并一下