这是sql 语句:
select
to_char(datpay_time,'yyyy-mm-dd') as paytime, decode(vchpay_type,'99bill','快钱充值','card','充值卡充值','其它'), sum(numpay_rmb) as money, count(*) from
chg_charge_log
where
vchstate=4
group by
to_char(datpay_time,'yyyy-mm-dd'), vchpay_type
order by to_char(datpay_time,'yyyy-mm-dd') asc
这是结果:
1 2008-09-18 快钱充值 23192 427
2 2008-09-18 充值卡充值 59480 1688
3 2008-09-19 快钱充值 14223 257
4 2008-09-19 充值卡充值 41330 1277
5 2008-09-20 快钱充值 19588 318
6 2008-09-20 充值卡充值 52400 1620
7 2008-09-21 快钱充值 16792 265
8 2008-09-21 充值卡充值 35550 1113我想转成下面的格式,请高工给点思路
1 2008-09-18 快钱充值 23192 427 充值卡充值 59480 1688
3 2008-09-19 快钱充值 14223 257 充值卡充值 41330 1277
5 2008-09-20 快钱充值 19588 318 充值卡充值 52400 1620
7 2008-09-21 快钱充值 16792 265 充值卡充值 35550 1113
select
to_char(datpay_time,'yyyy-mm-dd') as paytime, decode(vchpay_type,'99bill','快钱充值','card','充值卡充值','其它'), sum(numpay_rmb) as money, count(*) from
chg_charge_log
where
vchstate=4
group by
to_char(datpay_time,'yyyy-mm-dd'), vchpay_type
order by to_char(datpay_time,'yyyy-mm-dd') asc
这是结果:
1 2008-09-18 快钱充值 23192 427
2 2008-09-18 充值卡充值 59480 1688
3 2008-09-19 快钱充值 14223 257
4 2008-09-19 充值卡充值 41330 1277
5 2008-09-20 快钱充值 19588 318
6 2008-09-20 充值卡充值 52400 1620
7 2008-09-21 快钱充值 16792 265
8 2008-09-21 充值卡充值 35550 1113我想转成下面的格式,请高工给点思路
1 2008-09-18 快钱充值 23192 427 充值卡充值 59480 1688
3 2008-09-19 快钱充值 14223 257 充值卡充值 41330 1277
5 2008-09-20 快钱充值 19588 318 充值卡充值 52400 1620
7 2008-09-21 快钱充值 16792 265 充值卡充值 35550 1113
'快钱充值' kqcz,sum(decode(vchpay_type,'99bill',numpay_rmb,o)) zs1,sum(decode(vchpay_type,'99bill',1,o)) c1,
'充值卡充值' kcz,sum(decode(vchpay_type,'card',numpay_rmb,o)) zs2,sum(decode(vchpay_type,'card',1,o)) c1
from chg_charge_log
where vchstate = 4
group by to_char(datpay_time, 'yyyy-mm-dd')
order by to_char(datpay_time, 'yyyy-mm-dd') asc
,max(充值卡充值) as 充值卡充值,sum(money1) as money1,sum(count1) as as count1
from
(select to_char(datpay_time,'yyyy-mm-dd') as paytime, vchpay_type as '快钱充值',
sum(numpay_rmb) as money, count(*) as count,'' as '充值卡充值',0 as money1,0 as count1
from chg_charge_log
where vchstate=4 and vchpay_type='99bill'
group by to_char(datpay_time,'yyyy-mm-dd'), vchpay_type
union
select to_char(datpay_time,'yyyy-mm-dd') as paytime, '' as '快钱充值',0 as money,0 as count,
vchpay_type as '充值卡充值',sum(numpay_rmb) as money1, count(*) as count1
from chg_charge_log
where vchstate=4 and vchpay_type='card'
group by to_char(datpay_time,'yyyy-mm-dd'), vchpay_type
)a
group by paytime
SELECT TO_CHAR(DATPAY_TIME, 'YYYY-MM-DD') AS PAYTIME,
MAX(DECODE(VCHPAY_TYPE, '99BILL', 'KUAIQIAN', 'OTHER')) "KUAIQIAN",
SUM(DECODE(VCHPAY_TYPE, '99BILL', NUMPAY_RMB, 0)) "SUM_KUAIQIAN",
SUM(DECODE(VCHPAY_TYPE, '99BILL', 1, 0)) "COUNT_KUAIQIAN",
MAX(DECODE(VCHPAY_TYPE, 'CARD', 'CHONGZHIKA', 'OTHER')) "CHONGZHIKA",
SUM(DECODE(VCHPAY_TYPE, 'CARD', NUMPAY_RMB, 0)) "SUM_CHONGZHIKA",
SUM(DECODE(VCHPAY_TYPE, '99BILL', 1, 0)) "COUNT_CHONGZHIKA"
FROM CHG_CHARGE_LOG
WHERE VCHSTATE = 4
GROUP BY TO_CHAR(DATPAY_TIME, 'YYYY-MM-DD');
--try it.select to_char(datpay_time, 'yyyy-mm-dd') as paytime,
'快钱充值',
sum(decode(numpay_rmb,
decode(vchpay_type, '99bill', numpay_rmb, 0),
numpay_rmb,
0)) as money1,
count(decode(numpay_rmb,
decode(vchpay_type, '99bill', numpay_rmb, 0),
numpay_rmb,
0)) as count1,
'充值卡充值',
sum(decode(numpay_rmb,
decode(vchpay_type, 'card', numpay_rmb, 0),
numpay_rmb,
0)) as money2,
count(decode(numpay_rmb,
decode(vchpay_type, 'card', numpay_rmb, 0),
numpay_rmb,
0)) as count2 from chg_charge_log
where vchstate = 4
group by to_char(datpay_time, 'yyyy-mm-dd'), vchpay_type
order by to_char(datpay_time, 'yyyy-mm-dd') asc;
select to_char(datpay_time, 'yyyy-mm-dd') as paytime,
'快钱充值',
sum(decode(numpay_rmb,
decode(vchpay_type, '99bill', numpay_rmb, 0),
numpay_rmb,
0)) as money1,
count(decode(numpay_rmb,
decode(vchpay_type, '99bill', numpay_rmb, 0),
numpay_rmb,
0)) as count1,
'充值卡充值',
sum(decode(numpay_rmb,
decode(vchpay_type, 'card', numpay_rmb, 0),
numpay_rmb,
0)) as money2,
count(decode(numpay_rmb,
decode(vchpay_type, 'card', numpay_rmb, 0),
numpay_rmb,
0)) as count2 from chg_charge_log
where vchstate = 4
group by to_char(datpay_time, 'yyyy-mm-dd')
order by to_char(datpay_time, 'yyyy-mm-dd') asc;