sql的统计

Question

有一张数据表如下：
卡号 支付金额 余额 支付日期
1511710 27.9 172.1 2006-10-3
1511710 103 69.1 2007-1-2
1511710 69.1 0 2007-5-1
1511710 282 218 2008-1-12
1511710 105.2 112.8 2008-11-11
1511710 53 59.8 2009-3-5
1512833 100 0 2006-12-20
1512833 37.5 262.5 2007-4-10
1512833 118 144.5 2007-7-2
1512833 95.3 49.2 2007-10-21
1512833 49.2 0 2008-2-15
1513545 105 395 2007-8-22
1513545 200 195 2009-10-5
.......
我想统计每年底各个卡片的余额，应该怎么写这个语句。
如统计2006、2007、2008、2009年底的余额，对应的累计余额应该是：
sum2006=172.1+0
sum2007=0+49.2+395+...
sum2008=112.8+0+395+...
sum2009=59.8+0+195+...
注意：
1、2006年只有2张，所以只要统计2个数据
2、卡片1513545在2008年没有消费，所以2008年底的余额和2007年底是一样的
3、同理，卡片1512833在2008年后没有再使用，它在2009年底的余额就是2008底的余额
各位大虾，拜托了。

Answer 1

汗，这条SQL真的好复杂，弄了半天才弄出来。收你60一点也不贵。--卡号 支付金额 余额 支付日期
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 to_char(A.NF - 1,'YYYY'),sum(T.YE) 
 from 
     -- 分组求出每个日期、每张卡的最后一笔交易记录
    (select NF,KH,Max(ZFRQ) MZFRQ
      from 
        -- 日期表与数据表交叉，分别求出每个日期对应的数据
        (select NF,T.*
          from T,
               -- 构造个日期表，你可以在这里加更多的日期
               (select to_date('2007-1-1','yyyy-mm-dd') NF from dual union
               select to_date('2008-1-1','yyyy-mm-dd') from dual union
               select to_date('2009-1-1','yyyy-mm-dd') from dual union
               select to_date('2010-1-1','yyyy-mm-dd') from dual )
        where T.ZFRQ < NF)
    group by NF,KH) A,
    T
where A.KH = T.KH and A.MZFRQ = T.ZFRQ
group by A.NF
order by A.NF;

Answer 2

同问。
一边说是“统计每年底各个卡片的余额”，一边有在sum2006=172.1+0把尾数为10和33卡的余额加一起了。
啥情况？

Answer 3

SELECT T1.*,T2.IYEAR
FROM 数据表 T1, (
SELECT '2006' AS IYEAR FROM DUAL
UNION SELECT '2007' AS IYEAR FROM DUAL
UNION SELECT '2008' AS IYEAR FROM DUAL
UNION SELECT '2009' AS IYEAR FROM DUAL
.
.
.
) T2 
WHERE 
-- 根据卡号=自身,年度<=目标年的数据个数判断是否当年最后的支付记录
(SELECT COUNT(*) FROM 数据表 WHERE 卡号=T1.卡号 AND 支付日期>T1.支付日期 AND TO_CHAR(支付日期,'YYYY')<=T2.IYEAR) =0 
AND TO_CHAR(T1.支付日期,'YYYY')<=T2.IYEAR
ORDER BY T1.卡号,T2.IYEAR

Answer 4

select to_char(支付日期,'YYYY') 年份,sum(余额) 年末余额
from 
(
select t.*,row_num() over (partition by 卡号 order by 支付日期 desc) as id from tb t
) aa
where aa.id=1;

Answer 5

select to_char(支付日期,'YYYY') 年份,sum(余额) 年末余额
from 
(
select t.*,row_num() over (partition by 卡号 order by 支付日期 desc) as id from tb t
) aa
where aa.id=1
group by to_char(支付日期,'YYYY');

Answer 6

SELECT SUM(T1.余额),T2.IYEAR
FROM 数据表 T1, (
SELECT '2006' AS IYEAR FROM DUAL
UNION SELECT '2007' AS IYEAR FROM DUAL
UNION SELECT '2008' AS IYEAR FROM DUAL
UNION SELECT '2009' AS IYEAR FROM DUAL
.
.
.
) T2 
WHERE 
-- 根据卡号=自身,年度<=目标年的数据个数判断是否当年最后的支付记录
(SELECT COUNT(*) FROM 数据表 WHERE 卡号=T1.卡号 AND 支付日期>T1.支付日期 AND TO_CHAR(支付日期,'YYYY')<=T2.IYEAR) =0 
AND TO_CHAR(T1.支付日期,'YYYY')<=T2.IYEAR
GROUP BY T2.IYEAR

Answer 7

没那么麻烦啊SELECT SUM(余额) over (PARTITION BY to_char(支付日期,'yyyy')) 
FROM 数据表 ;

Answer 8

只求1个年度的算法：
SQL code--卡号 支付金额 余额 支付日期
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) ;