我有一张表 id shuzi
1 93.5
2 82.5
3 76.5
4 65
5 54
6 63.5
7 70.5
8 53.5
9 58.5
10 68.5
如何能查找相邻3个id号所对应的sum值最大呢
(1,2,3)(2,3,4)(3,4,5)
1 93.5
2 82.5
3 76.5
4 65
5 54
6 63.5
7 70.5
8 53.5
9 58.5
10 68.5
如何能查找相邻3个id号所对应的sum值最大呢
(1,2,3)(2,3,4)(3,4,5)
解决方案 »
- 求解一个查询问题! where 的问题
- 新学oracle 10G 看到一段代码的解释,怎么也不明白,请教!
- Oracle 出了这样的问题,高手们,乍办?
- 谁能给我一份oracle 9i的class12.zip?
- 小布oracle10g+rac实验的系统安装包
- 帮忙把一段sqlserver的函数转成oracle.
- 哪位老师帮忙解释一下函数纯度的问题
- ORA-03106错误,求解
- 帮帮忙,我都弄了一天了!现在还没吃饭!ODBC的问题!
- SQL deveoper 执行PL/SQL 弹出数据库连接窗口,输入密码后又显示已断开连接
- 请教:如何添加数据
- 怎么把select查出来的值,如果字段A相等,把字段B合并?
SELECT MAX(a) FROM (
SELECT SUM(a) a FROM person WHERE ID<4
UNION SELECT SUM(a) a FROM person WHERE ID<5 AND ID>1
UNION SELECT SUM(a) a FROM person WHERE ID<6 AND ID>2
UNION SELECT SUM(a) a FROM person WHERE ID<7 AND ID>3
……………………………………………………………………………………
UNION SELECT SUM(a) a FROM person WHERE ID<11 AND ID>7
)
随手写的,这个方法比较笨,单纯的SQL是不可能实现的吧,但用PL-SQL很容易实现只要把上面id的范围写成动态的就可以。
SELECT '01' AS ID,93.5 AS SHUZI FROM DUAL
UNION ALL
SELECT '02' AS ID,82.5 AS SHUZI FROM DUAL
UNION ALL
SELECT '03' AS ID,76.5 AS SHUZI FROM DUAL
UNION ALL
SELECT '04' AS ID,65 AS SHUZI FROM DUAL
UNION ALL
SELECT '05' AS ID,54 AS SHUZI FROM DUAL
UNION ALL
SELECT '06' AS ID,63.5 AS SHUZI FROM DUAL
UNION ALL
SELECT '07' AS ID,70.5 AS SHUZI FROM DUAL
UNION ALL
SELECT '08' AS ID,53.5 AS SHUZI FROM DUAL
UNION ALL
SELECT '09' AS ID,58.5 AS SHUZI FROM DUAL
UNION ALL
SELECT '10' AS ID,68.5 AS SHUZI FROM DUAL
)
SELECT GREATEST(SHUZI, NS, NNS) AS RESULTSHUZI
FROM (SELECT SHUZI,
LEAD(SHUZI, 1) OVER(ORDER BY ID) AS NS,
LEAD(SHUZI, 2) OVER(ORDER BY ID) AS NNS
FROM TEST)
WHERE GREATEST(SHUZI, NS, NNS) IS NOT NULL================================================================
1 93.5
2 82.5
3 76.5
4 65
5 70.5
6 70.5
7 70.5
8 68.5
WITH tb AS (
SELECT 1 id,93.5 shuzi FROM DUAL UNION ALL
SELECT 2 id,82.5 shuzi FROM DUAL UNION ALL
SELECT 3 id,76.5 shuzi FROM DUAL UNION ALL
SELECT 4 id,65.5 shuzi FROM DUAL UNION ALL
SELECT 5 id,54.5 shuzi FROM DUAL
)
SELECT MAX(n.seq_id) KEEP(dense_rank FIRST ORDER BY sum_shuzi DESC) seq_id,
MAX(n.sum_shuzi) sum_shuzi
FROM (
SELECT MAX(m.seq_id) seq_id,
SUM(m.shuzi) sum_shuzi
FROM (SELECT t.*,
CONNECT_BY_ROOT(t.ID) root,
'(' || SUBSTR(SYS_CONNECT_BY_PATH(t.ID, ','), 2) || ')' seq_id,
LEVEL lv
FROM tb t
CONNECT BY PRIOR t.ID = t.ID - 1
AND LEVEL <= 3) m
GROUP BY m.root
HAVING MAX(m.lv) = 3
) nSEQ_ID SUM_SHUZI
-------------------------------------------------------------------------------- ----------
(1,2,3) 252.5
SELECT ID, SUMSHUZI
FROM (SELECT SHUZI + LEAD(SHUZI, 1) OVER(ORDER BY ID) + LEAD(SHUZI, 2) OVER(ORDER BY ID) AS SUMSHUZI,
ID || ',' || LEAD(ID, 1) OVER(ORDER BY ID) || ',' || LEAD(ID, 2) OVER(ORDER BY ID) AS ID
FROM TEST)
WHERE ROWNUM = 1
====================================
1 01,02,03 252.5