部门 类别 年初余额 完成 年度农业处 一般预算 10000 100 2005-10-11
林牧处 一般预算 25000 1000 2005-02-01
农业处 一般预算 10000 100 2006-10-11
资金处 项目 25000 500 2006-02-01
农业处 一般预算 10000 100 2007-10-11
保护处 中央给钱 25000 1000 2007-02-01想按部门分类,查出:类别等于一般预算的 总的年初余额,和完成的钱数
部门1 sum(年初余额) sum(完成) 时间
部门2 sum(年初余额) sum(完成) 时间<年初全额按部门分组,全部加,完成的,只加一般预算的.>就是把部门分组,然后查出这个部的所有的年初余额(类别是一般预算的和不是的都包括),还有类别是年初预算的完成的总数.
林牧处 一般预算 25000 1000 2005-02-01
农业处 一般预算 10000 100 2006-10-11
资金处 项目 25000 500 2006-02-01
农业处 一般预算 10000 100 2007-10-11
保护处 中央给钱 25000 1000 2007-02-01想按部门分类,查出:类别等于一般预算的 总的年初余额,和完成的钱数
部门1 sum(年初余额) sum(完成) 时间
部门2 sum(年初余额) sum(完成) 时间<年初全额按部门分组,全部加,完成的,只加一般预算的.>就是把部门分组,然后查出这个部的所有的年初余额(类别是一般预算的和不是的都包括),还有类别是年初预算的完成的总数.
先不显示时间的
select 部门,sum(年初余额),sum(decode(类别,'一般预算',完成,0))
from table
group by 部门
from table
group by 部门,年度
(SELECT 1 user_id, 100 amt,
TO_DATE ('20080520010101', 'yyyymmddhh24miss') times
FROM DUAL
UNION ALL
SELECT 1 user_id, 100 amt,
TO_DATE ('20080520010141', 'yyyymmddhh24miss')
FROM DUAL
UNION ALL
SELECT 1 user_id, 100 amt,
TO_DATE ('20080520010301', 'yyyymmddhh24miss')
FROM DUAL
UNION ALL
SELECT 1 user_id, 100 amt,
TO_DATE ('20080520010401', 'yyyymmddhh24miss')
FROM DUAL
UNION ALL
SELECT 1 user_id, 100 amt,
TO_DATE ('20080520010501', 'yyyymmddhh24miss')
FROM DUAL
UNION ALL
SELECT 1 user_id, 100 amt,
TO_DATE ('20080520010601', 'yyyymmddhh24miss')
FROM DUAL
UNION ALL
SELECT 1 user_id, 100 amt,
TO_DATE ('20080520010801', 'yyyymmddhh24miss')
FROM DUAL
UNION ALL
SELECT 2 user_id, 100 amt,
TO_DATE ('20080520010101', 'yyyymmddhh24miss')
FROM DUAL
UNION ALL
SELECT 2 user_id, 100 amt,
TO_DATE ('20080520010141', 'yyyymmddhh24miss')
FROM DUAL
UNION ALL
SELECT 3 user_id, 100 amt,
TO_DATE ('20080520010301', 'yyyymmddhh24miss')
FROM DUAL
UNION ALL
SELECT 3 user_id, 100 amt,
TO_DATE ('20080520010401', 'yyyymmddhh24miss')
FROM DUAL
UNION ALL
SELECT 4 user_id, 100 amt,
TO_DATE ('20080520010501', 'yyyymmddhh24miss')
FROM DUAL
UNION ALL
SELECT 4 user_id, 100 amt,
TO_DATE ('20080520010601', 'yyyymmddhh24miss')
FROM DUAL
UNION ALL
SELECT 4 user_id, 100 amt,
TO_DATE ('20080520010801', 'yyyymmddhh24miss')
FROM DUAL)
插入测试数据
Row# USER_ID AMT TIMES1 1 100 2008/5/20 1:01:01
2 1 100 2008/5/20 1:01:41
3 1 100 2008/5/20 1:03:01
4 1 100 2008/5/20 1:04:01
5 1 100 2008/5/20 1:05:01
6 1 100 2008/5/20 1:06:01
7 1 100 2008/5/20 1:08:01
8 2 100 2008/5/20 1:01:01
9 2 100 2008/5/20 1:01:41
10 3 100 2008/5/20 1:03:01
11 3 100 2008/5/20 1:04:01
12 4 100 2008/5/20 1:05:01
13 4 100 2008/5/20 1:06:01
14 4 100 2008/5/20 1:08:01SELECT user_id, amt, times
FROM (SELECT user_id, amt, times,
LAG (times, 1, times - 1) OVER (PARTITION BY user_id, amt ORDER BY times)
pre_times,
LEAD (times, 1, SYSDATE + 1) OVER (PARTITION BY user_id, amt ORDER BY times)
after_times
FROM a)
WHERE (times - pre_times) * 1440 <= 1 OR (after_times - times) * 1440 <= 1结果
Row# USER_ID AMT TIMES1 1 100 2008/5/20 1:01:01
2 1 100 2008/5/20 1:01:41
3 1 100 2008/5/20 1:03:01
4 1 100 2008/5/20 1:04:01
5 1 100 2008/5/20 1:05:01
6 1 100 2008/5/20 1:06:01
7 2 100 2008/5/20 1:01:01
8 2 100 2008/5/20 1:01:41
9 3 100 2008/5/20 1:03:01
10 3 100 2008/5/20 1:04:01
11 4 100 2008/5/20 1:05:01
12 4 100 2008/5/20 1:06:01