17,081
社区成员
发帖
与我相关
我的任务
分享
--卡号 支付金额 余额 支付日期
with T as (
select '1511710' KH, 27.9 ZF, 172.1 YE, to_date('2006-10-3','yyyy-mm-dd') ZFRQ from dual union all
select '1511710', 103, 69.1, to_date('2007-1-2','yyyy-mm-dd') from dual union all
select '1511710', 69.1, 0, to_date('2007-5-1','yyyy-mm-dd') from dual union all
select '1511710', 282, 218, to_date('2008-1-12','yyyy-mm-dd') from dual union all
select '1511710', 105.2, 112.8, to_date('2008-11-11','yyyy-mm-dd') from dual union all
select '1511710', 53, 59.8, to_date('2009-3-5','yyyy-mm-dd') from dual union all
select '1512833', 100, 0, to_date('2006-12-20','yyyy-mm-dd') from dual union all
select '1512833', 37.5, 262.5, to_date('2007-4-10','yyyy-mm-dd') from dual union all
select '1512833', 118, 144.5, to_date('2007-7-2','yyyy-mm-dd') from dual union all
select '1512833', 95.3, 49.2, to_date('2007-10-21','yyyy-mm-dd') from dual union all
select '1512833', 49.2, 0, to_date('2008-2-15','yyyy-mm-dd') from dual union all
select '1513545', 105, 395, to_date('2007-8-22','yyyy-mm-dd') from dual union all
select '1513545', 200, 195, to_date('2009-10-5','yyyy-mm-dd') from dual )
-- 上面是测试数据,可以忽略
SELECT KH,YE ,ZFRQ
FROM T
WHERE (KH,ZFRQ) IN (SELECT KH,MAX(ZFRQ) FROM T WHERE TO_CHAR(ZFRQ,'YYYY') <= '2007' GROUP BY KH) ;
----行专列
select 卡号,
sum(case when zfdate='2006' then sum(zfye) else 0 end) as '2006支付余额',
.....
from tb
group by卡号
----行专列
select 卡号,
sum(case when zfdate='2006' then sum(zfye) else 0 end as '2006支付余额',
.....
from tb
group by卡号