select user_id, data_process_date, jbf = sum(case when acct_item_code in(30,88,-1,-100,100,27,200,99,80,28,14,2,1,29,400) then receive_fee end), shf = sum(case when acct_item_code in(3,22,23,24,25,51,121) then receive_fee end), mythf = sum(case when acct_item_code in(6,17,18,123,124,127) then receive_fee end), dxx = sum(case when acct_item_code in(4,132,77) then receive_fee end, ctthf = sum(case when acct_item_code in(5,7,31,122,125,126,128,139,130,131) then receive_fee end), cxtx = sum(case when acct_item_code = 34 then receive_fee end), wapf = sum(case when acct_item_code = 9 then receive_fee end), ldxs = sum(case when acct_item_code = 35 then receive_fee end), sxf = sum(case when acct_item_code = 11 then receive_fee end), yyzx = sum(case when acct_item_code = 32 then receive_fee end), cl = sum(case when acct_item_code = 71 then receive_fee end), hjzy = sum(case when acct_item_code = 0 then receive_fee end), f12530 = sum(case when acct_item_code = 0 then receive_fee end), qtzzyw = sum(case when acct_item_code in (8,45) then receive_fee end), qqh = sum(case when acct_item_code = 12 then receive_fee end), qprsf = sum(case when acct_item_code = 13 then receive_fee end), yxhd = sum(case when acct_item_code = 33 then receive_fee end), mxf = sum(case when acct_item_code = 16 then receive_fee end), xxf = sum(case when acct_item_code in (134,40,39,41,44) then receive_fee end), f12582 = sum(case when acct_item_code = 57 then receive_fee end), qtf = sum(case when acct_item_code in (19,15,10,36,37,38,39,42,43,133,69,75,56,111,49,42,50,51,52,53,54,447,483) then receive_fee end) from BN_Acc_Billdetail group by a.user_id,a.data_process_date
select user_id, data_process_date, jbf = sum(case when acct_item_code in(30,88,-1,-100,100,27,200,99,80,28,14,2,1,29,400) then receive_fee end), shf = sum(case when acct_item_code in(3,22,23,24,25,51,121) then receive_fee end), mythf = sum(case when acct_item_code in(6,17,18,123,124,127) then receive_fee end), dxx = sum(case when acct_item_code in(4,132,77) then receive_fee end, ctthf = sum(case when acct_item_code in(5,7,31,122,125,126,128,139,130,131) then receive_fee end), cxtx = sum(case when acct_item_code = 34 then receive_fee end), wapf = sum(case when acct_item_code = 9 then receive_fee end), ldxs = sum(case when acct_item_code = 35 then receive_fee end), sxf = sum(case when acct_item_code = 11 then receive_fee end), yyzx = sum(case when acct_item_code = 32 then receive_fee end), cl = sum(case when acct_item_code = 71 then receive_fee end), hjzy = sum(case when acct_item_code = 0 then receive_fee end), f12530 = sum(case when acct_item_code = 0 then receive_fee end), qtzzyw = sum(case when acct_item_code in (8,45) then receive_fee end), qqh = sum(case when acct_item_code = 12 then receive_fee end), qprsf = sum(case when acct_item_code = 13 then receive_fee end), yxhd = sum(case when acct_item_code = 33 then receive_fee end), mxf = sum(case when acct_item_code = 16 then receive_fee end), xxf = sum(case when acct_item_code in (134,40,39,41,44) then receive_fee end), f12582 = sum(case when acct_item_code = 57 then receive_fee end), qtf = sum(case when acct_item_code in (19,15,10,36,37,38,39,42,43,133,69,75,56,111,49,42,50,51,52,53,54,447,483) then receive_fee end) from BN_Acc_Billdetail group by user_id,data_process_date
create table #BN_Acc_Billdetail(acct_item_code int,receive_fee money,user_id int,late_fee_balance_date datetime) insert into #BN_Acc_Billdetail select 1,324.8,123,'2005-6-30' insert into #BN_Acc_Billdetail select 2,342 ,123,'2005-6-30' insert into #BN_Acc_Billdetail select 3,4423 ,123,'2005-6-30' insert into #BN_Acc_Billdetail select 1,23 ,234,'2005-6-30'select user_id, late_fee_balance_date, jbf = sum(case when acct_item_code in(30,88,-1,-100,100,27,200,99,80,28,14,2,1,29,400) then receive_fee end), shf = sum(case when acct_item_code in(3,22,23,24,25,51,121) then receive_fee end), mythf = sum(case when acct_item_code in(6,17,18,123,124,127) then receive_fee end), dxx = sum(case when acct_item_code in(4,132,77) then receive_fee end), ctthf = sum(case when acct_item_code in(5,7,31,122,125,126,128,139,130,131) then receive_fee end), cxtx = sum(case when acct_item_code = 34 then receive_fee end), wapf = sum(case when acct_item_code = 9 then receive_fee end), ldxs = sum(case when acct_item_code = 35 then receive_fee end), sxf = sum(case when acct_item_code = 11 then receive_fee end), yyzx = sum(case when acct_item_code = 32 then receive_fee end), cl = sum(case when acct_item_code = 71 then receive_fee end), hjzy = sum(case when acct_item_code = 0 then receive_fee end), f12530 = sum(case when acct_item_code = 0 then receive_fee end), qtzzyw = sum(case when acct_item_code in (8,45) then receive_fee end), qqh = sum(case when acct_item_code = 12 then receive_fee end), qprsf = sum(case when acct_item_code = 13 then receive_fee end), yxhd = sum(case when acct_item_code = 33 then receive_fee end), mxf = sum(case when acct_item_code = 16 then receive_fee end), xxf = sum(case when acct_item_code in (134,40,39,41,44) then receive_fee end), f12582 = sum(case when acct_item_code = 57 then receive_fee end), qtf = sum(case when acct_item_code in (19,15,10,36,37,38,39,42,43,133,69,75,56,111,49,42,50,51,52,53,54,447,483) then receive_fee end) from #BN_Acc_Billdetail group by user_id,late_fee_balance_date
user_id,
data_process_date,
jbf = sum(case when acct_item_code in(30,88,-1,-100,100,27,200,99,80,28,14,2,1,29,400) then receive_fee end),
shf = sum(case when acct_item_code in(3,22,23,24,25,51,121) then receive_fee end),
mythf = sum(case when acct_item_code in(6,17,18,123,124,127) then receive_fee end),
dxx = sum(case when acct_item_code in(4,132,77) then receive_fee end,
ctthf = sum(case when acct_item_code in(5,7,31,122,125,126,128,139,130,131) then receive_fee end),
cxtx = sum(case when acct_item_code = 34 then receive_fee end),
wapf = sum(case when acct_item_code = 9 then receive_fee end),
ldxs = sum(case when acct_item_code = 35 then receive_fee end),
sxf = sum(case when acct_item_code = 11 then receive_fee end),
yyzx = sum(case when acct_item_code = 32 then receive_fee end),
cl = sum(case when acct_item_code = 71 then receive_fee end),
hjzy = sum(case when acct_item_code = 0 then receive_fee end),
f12530 = sum(case when acct_item_code = 0 then receive_fee end),
qtzzyw = sum(case when acct_item_code in (8,45) then receive_fee end),
qqh = sum(case when acct_item_code = 12 then receive_fee end),
qprsf = sum(case when acct_item_code = 13 then receive_fee end),
yxhd = sum(case when acct_item_code = 33 then receive_fee end),
mxf = sum(case when acct_item_code = 16 then receive_fee end),
xxf = sum(case when acct_item_code in (134,40,39,41,44) then receive_fee end),
f12582 = sum(case when acct_item_code = 57 then receive_fee end),
qtf = sum(case when acct_item_code in (19,15,10,36,37,38,39,42,43,133,69,75,56,111,49,42,50,51,52,53,54,447,483) then receive_fee end)
from
BN_Acc_Billdetail
group by
a.user_id,a.data_process_date
user_id,
data_process_date,
jbf = sum(case when acct_item_code in(30,88,-1,-100,100,27,200,99,80,28,14,2,1,29,400) then receive_fee end),
shf = sum(case when acct_item_code in(3,22,23,24,25,51,121) then receive_fee end),
mythf = sum(case when acct_item_code in(6,17,18,123,124,127) then receive_fee end),
dxx = sum(case when acct_item_code in(4,132,77) then receive_fee end,
ctthf = sum(case when acct_item_code in(5,7,31,122,125,126,128,139,130,131) then receive_fee end),
cxtx = sum(case when acct_item_code = 34 then receive_fee end),
wapf = sum(case when acct_item_code = 9 then receive_fee end),
ldxs = sum(case when acct_item_code = 35 then receive_fee end),
sxf = sum(case when acct_item_code = 11 then receive_fee end),
yyzx = sum(case when acct_item_code = 32 then receive_fee end),
cl = sum(case when acct_item_code = 71 then receive_fee end),
hjzy = sum(case when acct_item_code = 0 then receive_fee end),
f12530 = sum(case when acct_item_code = 0 then receive_fee end),
qtzzyw = sum(case when acct_item_code in (8,45) then receive_fee end),
qqh = sum(case when acct_item_code = 12 then receive_fee end),
qprsf = sum(case when acct_item_code = 13 then receive_fee end),
yxhd = sum(case when acct_item_code = 33 then receive_fee end),
mxf = sum(case when acct_item_code = 16 then receive_fee end),
xxf = sum(case when acct_item_code in (134,40,39,41,44) then receive_fee end),
f12582 = sum(case when acct_item_code = 57 then receive_fee end),
qtf = sum(case when acct_item_code in (19,15,10,36,37,38,39,42,43,133,69,75,56,111,49,42,50,51,52,53,54,447,483) then receive_fee end)
from
BN_Acc_Billdetail
group by
user_id,data_process_date
user_id, data_process_date, acct_item_code, receive_fee
我把表的结构贴出来
BN_Acc_Billdetail表:
acct_item_code receive_fee user_id late_fee_balance_date
1 324.8 123 2005-6-31
2 342 123 2005-6-31
3 4423 123 2005-6-31
1 23 234 2005-6-31
expenselist表:user_id(varchar(16))user_time(datetime(8))jbf,shf,mythf,dxx,ctthf,cxtx,wapf,ldxs,sxf,yyzx,cl,hjzy,f12530,qtzzyw,qqh,gprsf,yxhd,mwf,xxf,f12582,qtf这些都是money(8)
insert into #BN_Acc_Billdetail select 1,324.8,123,'2005-6-30'
insert into #BN_Acc_Billdetail select 2,342 ,123,'2005-6-30'
insert into #BN_Acc_Billdetail select 3,4423 ,123,'2005-6-30'
insert into #BN_Acc_Billdetail select 1,23 ,234,'2005-6-30'select
user_id,
late_fee_balance_date,
jbf = sum(case when acct_item_code in(30,88,-1,-100,100,27,200,99,80,28,14,2,1,29,400) then receive_fee end),
shf = sum(case when acct_item_code in(3,22,23,24,25,51,121) then receive_fee end),
mythf = sum(case when acct_item_code in(6,17,18,123,124,127) then receive_fee end),
dxx = sum(case when acct_item_code in(4,132,77) then receive_fee end),
ctthf = sum(case when acct_item_code in(5,7,31,122,125,126,128,139,130,131) then receive_fee end),
cxtx = sum(case when acct_item_code = 34 then receive_fee end),
wapf = sum(case when acct_item_code = 9 then receive_fee end),
ldxs = sum(case when acct_item_code = 35 then receive_fee end),
sxf = sum(case when acct_item_code = 11 then receive_fee end),
yyzx = sum(case when acct_item_code = 32 then receive_fee end),
cl = sum(case when acct_item_code = 71 then receive_fee end),
hjzy = sum(case when acct_item_code = 0 then receive_fee end),
f12530 = sum(case when acct_item_code = 0 then receive_fee end),
qtzzyw = sum(case when acct_item_code in (8,45) then receive_fee end),
qqh = sum(case when acct_item_code = 12 then receive_fee end),
qprsf = sum(case when acct_item_code = 13 then receive_fee end),
yxhd = sum(case when acct_item_code = 33 then receive_fee end),
mxf = sum(case when acct_item_code = 16 then receive_fee end),
xxf = sum(case when acct_item_code in (134,40,39,41,44) then receive_fee end),
f12582 = sum(case when acct_item_code = 57 then receive_fee end),
qtf = sum(case when acct_item_code in (19,15,10,36,37,38,39,42,43,133,69,75,56,111,49,42,50,51,52,53,54,447,483) then receive_fee end)
from
#BN_Acc_Billdetail
group by
user_id,late_fee_balance_date