SQL> SQL> -------你比较的是年吧,那就不能用sysdate-fdate啊,这里需要修改下 SQL> select FCstmName, 2 fnote, 3 case 4 when EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM fdate) < 1 then 5 '1年以内' 6 when 1 <= EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM fdate) and 7 EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM fdate) < 2 then 8 '1~2年' 9 when 2 <= EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM fdate) and 10 EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM fdate) < 3 then 11 '2-3年' 12 else 13 '3年以上' 14 end FLimitYear, 15 tfvalue 16 from (select t.*, 17 b.tfvalue, 18 row_number() over(partition by t.fcstmid order by fdate desc, FcreateTime desc) rn 19 from t_testdb t, 20 (select a.fcstmid, (a.fvalue + b.fbegvalue) tfvalue 21 from (select fcstmid, sum(fvalue) fvalue 22 from t_testdb 23 group by fcstmid) a, 24 (select fcstmid, sum(fbegvalue) fbegvalue 25 from T_InitTestDB 26 group by fcstmid) b 27 where a.fcstmid = b.fcstmid(+)) b 28 where t.fcstmid = b.fcstmid) 29 where rn = 1 30 /FCSTMNAME FNOTE FLIMITYEAR TFVALUE -------------------------------------------- -------------------------------------------- ---------- ---------- 珠海致达公司 摘要6 1年以内 5800 贺州 摘要4 1~2年 1400
水泥公司 SQL>
我也刚弄出来。。比楼上复杂select tmpDB.FCstmName 客户或供应商,tmpDB.FNote 摘要或成因, CASE WHEN to_number(to_char(sysdate,'yyyy'))-to_number(to_char(FDate,'yyyy')) <1 THEN '1年以内' WHEN (to_number(to_char(sysdate,'yyyy'))-to_number(to_char(FDate,'yyyy')))>=1 AND (to_number(to_char(sysdate,'yyyy'))-to_number(to_char(FDate,'yyyy')))<2 THEN '1-2年' WHEN (to_number(to_char(sysdate,'yyyy'))-to_number(to_char(FDate,'yyyy')))>=2 AND (to_number(to_char(sysdate,'yyyy'))-to_number(to_char(FDate,'yyyy')))<3 THEN '2-3年' WHEN (to_number(to_char(sysdate,'yyyy'))-to_number(to_char(FDate,'yyyy')))>=3 AND (to_number(to_char(sysdate,'yyyy'))-to_number(to_char(FDate,'yyyy')))<4 THEN '3-4年' WHEN (to_number(to_char(sysdate,'yyyy'))-to_number(to_char(FDate,'yyyy')))>=4 AND (to_number(to_char(sysdate,'yyyy'))-to_number(to_char(FDate,'yyyy')))<5 THEN '4-5年' ELSE '五年以上' END 发生年限 , tmpDB.FsumValue 总额 from ( select a.FcstmID,a.FcstmName,a.Fnote,a.FDate,a.sum_Fvalue+b.sum_FbegValue FsumValue from ( select * from ( select Fnote,FDate,FcstmID,FcstmName,sum(Fvalue)over (partition by FcstmID ) sum_FValue , Row_number()over (partition by FcstmID order by FDate Desc,FCreateTime Desc nulls last ) rm from t_testDb order by FDate desc nulls last ,FCreateTime desc nulls last ) where rm=1 ) a left join (select sum(FbegValue) sum_FbegValue,FCstmID from T_initTestDB group By FCstmID) b on a.FcstmID=b.FcstmID )tmpDB
EXTRACT(YEAR FROM SYSDATE)这个函数非常有用,非常感谢! 你的思路和我是差不多的,只是汇总的顺序不一样。
select tmpDB.FCstmName 客户或供应商,tmpDB.FNote 摘要或成因, CASE WHEN (extract(year from sysdate)-extract(year from FDate)) <1 THEN '1年以内' WHEN (extract(year from sysdate)-extract(year from FDate))>=1 AND (extract(year from sysdate)-extract(year from FDate))<2 THEN '1-2年' WHEN (extract(year from sysdate)-extract(year from FDate))>=2 AND (extract(year from sysdate)-extract(year from FDate))<3 THEN '2-3年' WHEN (extract(year from sysdate)-extract(year from FDate))>=3 AND (extract(year from sysdate)-extract(year from FDate))<4 THEN '3-4年' WHEN (extract(year from sysdate)-extract(year from FDate))>=4 AND (extract(year from sysdate)-extract(year from FDate))<5 THEN '4-5年' ELSE '五年以上' END 发生年限 , tmpDB.FsumValue 总额 from ( select a.FcstmID,a.FcstmName,a.Fnote,a.FDate,a.sum_Fvalue+b.sum_FbegValue FsumValue from ( select * from ( select Fnote,FDate,FcstmID,FcstmName,sum(Fvalue)over (partition by FcstmID ) sum_FValue , Row_number()over (partition by FcstmID order by FDate Desc,FCreateTime Desc nulls last ) rm from t_testDb order by FDate desc nulls last ,FCreateTime desc nulls last ) where rm=1 ) a left join (select sum(FbegValue) sum_FbegValue,FCstmID from T_initTestDB group By FCstmID) b on a.FcstmID=b.FcstmID )tmpDB
SQL>
SQL> -------你比较的是年吧,那就不能用sysdate-fdate啊,这里需要修改下
SQL> select FCstmName,
2 fnote,
3 case
4 when EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM fdate) < 1 then
5 '1年以内'
6 when 1 <= EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM fdate) and
7 EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM fdate) < 2 then
8 '1~2年'
9 when 2 <= EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM fdate) and
10 EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM fdate) < 3 then
11 '2-3年'
12 else
13 '3年以上'
14 end FLimitYear,
15 tfvalue
16 from (select t.*,
17 b.tfvalue,
18 row_number() over(partition by t.fcstmid order by fdate desc, FcreateTime desc) rn
19 from t_testdb t,
20 (select a.fcstmid, (a.fvalue + b.fbegvalue) tfvalue
21 from (select fcstmid, sum(fvalue) fvalue
22 from t_testdb
23 group by fcstmid) a,
24 (select fcstmid, sum(fbegvalue) fbegvalue
25 from T_InitTestDB
26 group by fcstmid) b
27 where a.fcstmid = b.fcstmid(+)) b
28 where t.fcstmid = b.fcstmid)
29 where rn = 1
30 /FCSTMNAME FNOTE FLIMITYEAR TFVALUE
-------------------------------------------- -------------------------------------------- ---------- ----------
珠海致达公司 摘要6 1年以内 5800
贺州 摘要4 1~2年 1400
水泥公司
SQL>
CASE WHEN to_number(to_char(sysdate,'yyyy'))-to_number(to_char(FDate,'yyyy')) <1 THEN '1年以内'
WHEN (to_number(to_char(sysdate,'yyyy'))-to_number(to_char(FDate,'yyyy')))>=1 AND (to_number(to_char(sysdate,'yyyy'))-to_number(to_char(FDate,'yyyy')))<2 THEN '1-2年'
WHEN (to_number(to_char(sysdate,'yyyy'))-to_number(to_char(FDate,'yyyy')))>=2 AND (to_number(to_char(sysdate,'yyyy'))-to_number(to_char(FDate,'yyyy')))<3 THEN '2-3年'
WHEN (to_number(to_char(sysdate,'yyyy'))-to_number(to_char(FDate,'yyyy')))>=3 AND (to_number(to_char(sysdate,'yyyy'))-to_number(to_char(FDate,'yyyy')))<4 THEN '3-4年'
WHEN (to_number(to_char(sysdate,'yyyy'))-to_number(to_char(FDate,'yyyy')))>=4 AND (to_number(to_char(sysdate,'yyyy'))-to_number(to_char(FDate,'yyyy')))<5 THEN '4-5年'
ELSE '五年以上'
END 发生年限 , tmpDB.FsumValue 总额
from
(
select a.FcstmID,a.FcstmName,a.Fnote,a.FDate,a.sum_Fvalue+b.sum_FbegValue FsumValue from
(
select * from (
select Fnote,FDate,FcstmID,FcstmName,sum(Fvalue)over (partition by FcstmID ) sum_FValue ,
Row_number()over (partition by FcstmID order by FDate Desc,FCreateTime Desc nulls last ) rm from t_testDb
order by FDate desc nulls last ,FCreateTime desc nulls last
) where rm=1
) a
left join
(select sum(FbegValue) sum_FbegValue,FCstmID from T_initTestDB group By FCstmID) b
on a.FcstmID=b.FcstmID
)tmpDB
EXTRACT(YEAR FROM SYSDATE)这个函数非常有用,非常感谢!
你的思路和我是差不多的,只是汇总的顺序不一样。
select tmpDB.FCstmName 客户或供应商,tmpDB.FNote 摘要或成因,
CASE WHEN (extract(year from sysdate)-extract(year from FDate)) <1 THEN '1年以内'
WHEN (extract(year from sysdate)-extract(year from FDate))>=1 AND (extract(year from sysdate)-extract(year from FDate))<2 THEN '1-2年'
WHEN (extract(year from sysdate)-extract(year from FDate))>=2 AND (extract(year from sysdate)-extract(year from FDate))<3 THEN '2-3年'
WHEN (extract(year from sysdate)-extract(year from FDate))>=3 AND (extract(year from sysdate)-extract(year from FDate))<4 THEN '3-4年'
WHEN (extract(year from sysdate)-extract(year from FDate))>=4 AND (extract(year from sysdate)-extract(year from FDate))<5 THEN '4-5年'
ELSE '五年以上'
END 发生年限 , tmpDB.FsumValue 总额
from
(
select a.FcstmID,a.FcstmName,a.Fnote,a.FDate,a.sum_Fvalue+b.sum_FbegValue FsumValue from
(
select * from (
select Fnote,FDate,FcstmID,FcstmName,sum(Fvalue)over (partition by FcstmID ) sum_FValue ,
Row_number()over (partition by FcstmID order by FDate Desc,FCreateTime Desc nulls last ) rm from t_testDb
order by FDate desc nulls last ,FCreateTime desc nulls last
) where rm=1
) a
left join
(select sum(FbegValue) sum_FbegValue,FCstmID from T_initTestDB group By FCstmID) b
on a.FcstmID=b.FcstmID
)tmpDB