我有一个表test,有如下字段(和记录):
addTime(日期) A(字段A) B(字段B) C(字段C)
2008-8-1 20 30 40
2008-8-2 10 30 40
2008-8-3 13 39 56
2008-8-4 25 56 43
2008-8-5 36 30 25
2008-8-6 20 36 67
2008-8-7 20 90 40字段addtime为日期型
其余为number型我需要用存储过程求出一个月汇总和一个年汇总月汇总的在页面上显示是:
月份 month_A month_B month_C
2008-8 200 90 200
2008-9 0 0 0其中:month_A 是指定月份A 字段值的求和
month_B 是指定月(有记录的)B字段最后一天的值,例如现在数据的8月份最大的日期是8月7号,所2008-8-7是8月最后一天记录。
month_C和month_A 是类似的。 这个月份会是一个参数,到时候是动态的,可以动态显示不同的月份,或者是多个月份的。
年汇总比月汇总还麻烦一点:
年份 year_A year_B year_C
2008年 200 90 200
2009年 100 40 37year_A 指定年份说有A字段的求和
year_B 指定年份B字段最后一天的值
year_C 指定年份中C字段到目前(有记录)月份最后一天值得求和。这个最麻烦,要分别把数据库里本年度的每个月最后一天的值求和。这个年份也是一个参数,为空的话,就是显示库中有记录的所有年份。想用各用存储过程,分别把年汇总和月汇总能拼成这样一个数据集,请大家帮忙。
addTime(日期) A(字段A) B(字段B) C(字段C)
2008-8-1 20 30 40
2008-8-2 10 30 40
2008-8-3 13 39 56
2008-8-4 25 56 43
2008-8-5 36 30 25
2008-8-6 20 36 67
2008-8-7 20 90 40字段addtime为日期型
其余为number型我需要用存储过程求出一个月汇总和一个年汇总月汇总的在页面上显示是:
月份 month_A month_B month_C
2008-8 200 90 200
2008-9 0 0 0其中:month_A 是指定月份A 字段值的求和
month_B 是指定月(有记录的)B字段最后一天的值,例如现在数据的8月份最大的日期是8月7号,所2008-8-7是8月最后一天记录。
month_C和month_A 是类似的。 这个月份会是一个参数,到时候是动态的,可以动态显示不同的月份,或者是多个月份的。
年汇总比月汇总还麻烦一点:
年份 year_A year_B year_C
2008年 200 90 200
2009年 100 40 37year_A 指定年份说有A字段的求和
year_B 指定年份B字段最后一天的值
year_C 指定年份中C字段到目前(有记录)月份最后一天值得求和。这个最麻烦,要分别把数据库里本年度的每个月最后一天的值求和。这个年份也是一个参数,为空的话,就是显示库中有记录的所有年份。想用各用存储过程,分别把年汇总和月汇总能拼成这样一个数据集,请大家帮忙。
解决方案 »
- 安装了Oracle 9i,请问为什么Enterprise Manager Console打不开
- oracle的system表空间占用率过大
- mount.ocfs2: Device name specified was not found while opening device /dev/sdb2
- 非顺序插入表中 ROWNUM用来排序不准确
- oracle处理数据的极限?
- oracle能实现select col1,* from table吗
- 关于Oracle数据类型的问题
- 关于toad修改数据库的问题
- 为什么的我的Oracle监听器会经常异常退出??
- 为什么按oracle8一开始就出错
- oracle中sysdba用户通过odbc数据源连接,怎样配置?
- 专门配置一台透明网关服务器连接Sql Server 2K,跟Oracle数据库服务器分开,DBLink也建好了,调用不了,老是提示“TNS: 无法处理服务名”
(SELECT TO_DATE ('2008-8-1', 'yyyy-mm-dd') addtime, 20 a, 30 b, 40 c
FROM DUAL
UNION ALL
SELECT TO_DATE ('2008-8-2', 'yyyy-mm-dd') addtime, 10, 30, 40
FROM DUAL
UNION ALL
SELECT TO_DATE ('2008-8-3', 'yyyy-mm-dd') addtime, 13, 39, 56
FROM DUAL
UNION ALL
SELECT TO_DATE ('2008-8-4', 'yyyy-mm-dd') addtime, 25, 56, 43
FROM DUAL
UNION ALL
SELECT TO_DATE ('2008-8-5', 'yyyy-mm-dd') addtime, 36, 30, 25
FROM DUAL
UNION ALL
SELECT TO_DATE ('2008-8-6', 'yyyy-mm-dd') addtime, 20, 36, 67
FROM DUAL
UNION ALL
SELECT TO_DATE ('2008-8-7', 'yyyy-mm-dd') addtime, 20, 90, 40
FROM DUAL)
SELECT m.months, NVL (t.months_a, 0) months_a, NVL (months_b, 0) months_b,
NVL (months_c, 0) months_c
FROM (SELECT TO_CHAR (ADD_MONTHS (TO_DATE (:start_date, 'yyyy-mm'),
ROWNUM - 1
),
'yyyy-mm'
) months
FROM all_objects
WHERE ROWNUM <=
MONTHS_BETWEEN (TO_DATE (:end_date, 'yyyy-mm'),
TO_DATE (:start_date, 'yyyy-mm')
)
+ 1) m,
(SELECT TO_CHAR (addtime, 'yyyy-mm') months, SUM (a) months_a,
months_b, SUM (c) months_c
FROM (SELECT a.*,
LAST_VALUE (b) OVER (PARTITION BY TO_CHAR
(addtime,
'yyyy-mm'
) ORDER BY addtime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
months_b
FROM TEST a
WHERE addtime BETWEEN TO_DATE (:start_date, 'yyyy-mm')
AND TO_DATE (:end_date, 'yyyy-mm')
+ 0.99999)
GROUP BY TO_CHAR (addtime, 'yyyy-mm'), months_b) t
WHERE m.months = t.months(+)结果
Row# MONTHS MONTHS_A MONTHS_B MONTHS_C1 2008-08 144 90 311
2 2008-09 0 0 0
SELECT m.months, NVL (t.months_a, 0) months_a, NVL (months_b, 0) months_b,
NVL (months_c, 0) months_c
FROM (SELECT TO_CHAR (ADD_MONTHS (TO_DATE (:start_date, 'yyyy-mm'),
ADD_MONTHS --这个好像不是个函数吧
如果不能访问此表
可以用以下语句代替
SELECT TO_CHAR (ADD_MONTHS (TO_DATE (:start_date, 'yyyy-mm'),
ROWNUM - 1
),
'yyyy-mm'
) months
FROM dual
connect by ROWNUM <=
MONTHS_BETWEEN (TO_DATE (:end_date, 'yyyy-mm'),
TO_DATE (:start_date, 'yyyy-mm')
)
+ 1
months_b, SUM (c) months_c
FROM (SELECT a.*,
LAST_VALUE (b) OVER (PARTITION BY TO_CHAR
(addtime,
'yyyy-mm'
) ORDER BY addtime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
months_b
FROM TEST a
WHERE addtime BETWEEN TO_DATE (:start_date, 'yyyy-mm')
AND TO_DATE (:end_date, 'yyyy-mm')
+ 0.99999)
这段没太明白,尤其最后这个+0、99999,很少见过。
当然也可以用<TO_DATE (:end_date, 'yyyy-mm') +1
2008-8-1 20 30 40 x
2008-8-2 10 30 42 x
2008-8-1 11 32 44 y
2008-8-2 12 33 45 y
2008-8-3 13 39 56 x
2008-8-4 25 56 43 y
2008-8-5 36 30 25 y
2008-8-6 20 36 67 y
2008-8-7 20 90 40 x
2008-8-7 30 24 23 z
现在我的这汇总比原来又复杂了一点,多了个字段typename,里面有不同的值,汇总要按照这个typename进行区分。
如:月汇总会是:
月份 month_A month_B month_C typename
2008-8 200 90 200 x
2008-8 223 0 0 y
2008-9 200 90 200 x
2008-9 34 0 0 y这样
2008-8-1 20 30 40 x
2008-8-2 10 30 42 x
2008-8-1 11 32 44 y
2008-8-2 12 33 45 y
2008-8-3 13 39 56 x
2008-8-4 25 56 43 y
2008-8-5 36 30 25 y
2008-8-6 20 36 67 y
2008-8-7 20 90 40 x
2008-8-7 30 24 23 z
现在我的这汇总比原来又复杂了一点,多了个字段typename,里面有不同的值,汇总要按照这个typename进行区分。
如:月汇总会是:
月份 month_A month_B month_C typename
2008-8 200 90 200 x
2008-8 223 0 0 y
2008-9 200 90 200 x
2008-9 34 0 0 y 请大家帮忙回答下这个问题,能不能在hebo2005 sql上进行改造
NVL (months_c, 0) months_c,typename
FROM (SELECT TO_CHAR (ADD_MONTHS (TO_DATE (:start_date, 'yyyy-mm'),
ROWNUM - 1
),
'yyyy-mm'
) months
FROM all_objects
WHERE ROWNUM <=
MONTHS_BETWEEN (TO_DATE (:end_date, 'yyyy-mm'),
TO_DATE (:start_date, 'yyyy-mm')
)
+ 1) m,
(SELECT TO_CHAR (addtime, 'yyyy-mm') months, SUM (a) months_a,
months_b, SUM (c) months_c,typename
FROM (SELECT a.*,
LAST_VALUE (b) OVER (PARTITION BY TO_CHAR
(addtime,
'yyyy-mm'
),typename ORDER BY addtime ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
months_b
FROM TEST a
WHERE addtime BETWEEN TO_DATE (:start_date, 'yyyy-mm')
AND TO_DATE (:end_date, 'yyyy-mm')
+ 0.99999)
GROUP BY TO_CHAR (addtime, 'yyyy-mm'), months_b,typename ) t
WHERE m.months = t.months(+)