WITH T AS (SELECT 1 ID, 'a' CONT FROM DUAL UNION ALL SELECT 2 ID, 'aa' CONT FROM DUAL UNION ALL SELECT 3 ID, 'b' CONT FROM DUAL UNION ALL SELECT 4 ID, 'c' CONT FROM DUAL UNION ALL SELECT 5 ID, 'd' CONT FROM DUAL UNION ALL SELECT 6 ID, 'ae' CONT FROM DUAL) SELECT T3.* FROM (SELECT T.*, ROUND(ROWNUM / 2) RN FROM T) T3, (SELECT T2.RN FROM (SELECT T1.*, ROUND(ROWNUM / 2) RN FROM T T1) T2 WHERE T2.ID = 6) T4 WHERE T3.RN = T4.RN;
WITH T AS (SELECT 1 ID, 'a' CONT FROM DUAL UNION ALL SELECT 2 ID, 'aa' CONT FROM DUAL UNION ALL SELECT 3 ID, 'b' CONT FROM DUAL UNION ALL SELECT 4 ID, 'c' CONT FROM DUAL UNION ALL SELECT 5 ID, 'd' CONT FROM DUAL UNION ALL SELECT 6 ID, 'ae' CONT FROM DUAL) SELECT T3.* FROM (SELECT T.*, ROUND(ROWNUM / 2) RN FROM T) T3, (SELECT T2.RN FROM (SELECT T1.*, ROUND(ROWNUM / 2) RN FROM T T1) T2 WHERE T2.ID = 6) T4 WHERE T3.RN = T4.RN;这样是不行的,如果有很多条数据?
WITH T AS (SELECT 1 ID, 'a' CONT FROM DUAL UNION ALL SELECT 2 ID, 'aa' CONT FROM DUAL UNION ALL SELECT 3 ID, 'b' CONT FROM DUAL UNION ALL SELECT 4 ID, 'c' CONT FROM DUAL UNION ALL SELECT 5 ID, 'd' CONT FROM DUAL UNION ALL SELECT 6 ID, 'ae' CONT FROM DUAL) SELECT T3.* FROM (SELECT T.*, ROUND(ROWNUM / 2) RN FROM T) T3, (SELECT T2.RN FROM (SELECT T1.*, ROUND(ROWNUM / 2) RN FROM T T1) T2 WHERE T2.ID = 6) T4 WHERE T3.RN = T4.RN;这样是不行的,如果有很多条数据? 什么很多条数据? 你的意思是加入按N条数据作为一页显示?
其实思路都给你了,你只需要换一下而已。 比如每页显示3条:WITH T AS (SELECT 1 ID, 'a' CONT FROM DUAL UNION ALL SELECT 2 ID, 'aa' CONT FROM DUAL UNION ALL SELECT 3 ID, 'b' CONT FROM DUAL UNION ALL SELECT 4 ID, 'c' CONT FROM DUAL UNION ALL SELECT 5 ID, 'd' CONT FROM DUAL UNION ALL SELECT 6 ID, 'ae' CONT FROM DUAL) SELECT T3.ID, T3.CONT FROM (SELECT T.*, CEIL(ROWNUM / 3) RN FROM T) T3, (SELECT T2.RN FROM (SELECT T1.*, CEIL(ROWNUM / 3) RN FROM T T1) T2 WHERE T2.ID = 4) T4 WHERE T3.RN = T4.RN ORDER BY t3.id;
(SELECT 1 ID, 'a' CONT
FROM DUAL
UNION ALL
SELECT 2 ID, 'aa' CONT
FROM DUAL
UNION ALL
SELECT 3 ID, 'b' CONT
FROM DUAL
UNION ALL
SELECT 4 ID, 'c' CONT
FROM DUAL
UNION ALL
SELECT 5 ID, 'd' CONT
FROM DUAL
UNION ALL
SELECT 6 ID, 'ae' CONT
FROM DUAL)
SELECT T3.*
FROM (SELECT T.*, ROUND(ROWNUM / 2) RN FROM T) T3,
(SELECT T2.RN
FROM (SELECT T1.*, ROUND(ROWNUM / 2) RN FROM T T1) T2
WHERE T2.ID = 6) T4
WHERE T3.RN = T4.RN;
(SELECT 1 ID, 'a' CONT
FROM DUAL
UNION ALL
SELECT 2 ID, 'aa' CONT
FROM DUAL
UNION ALL
SELECT 3 ID, 'b' CONT
FROM DUAL
UNION ALL
SELECT 4 ID, 'c' CONT
FROM DUAL
UNION ALL
SELECT 5 ID, 'd' CONT
FROM DUAL
UNION ALL
SELECT 6 ID, 'ae' CONT
FROM DUAL)
SELECT T3.*
FROM (SELECT T.*, ROUND(ROWNUM / 2) RN FROM T) T3,
(SELECT T2.RN
FROM (SELECT T1.*, ROUND(ROWNUM / 2) RN FROM T T1) T2
WHERE T2.ID = 6) T4
WHERE T3.RN = T4.RN;这样是不行的,如果有很多条数据?
(SELECT 1 ID, 'a' CONT
FROM DUAL
UNION ALL
SELECT 2 ID, 'aa' CONT
FROM DUAL
UNION ALL
SELECT 3 ID, 'b' CONT
FROM DUAL
UNION ALL
SELECT 4 ID, 'c' CONT
FROM DUAL
UNION ALL
SELECT 5 ID, 'd' CONT
FROM DUAL
UNION ALL
SELECT 6 ID, 'ae' CONT
FROM DUAL)
SELECT T3.*
FROM (SELECT T.*, ROUND(ROWNUM / 2) RN FROM T) T3,
(SELECT T2.RN
FROM (SELECT T1.*, ROUND(ROWNUM / 2) RN FROM T T1) T2
WHERE T2.ID = 6) T4
WHERE T3.RN = T4.RN;这样是不行的,如果有很多条数据?
什么很多条数据?
你的意思是加入按N条数据作为一页显示?
比如每页显示3条:WITH T AS
(SELECT 1 ID, 'a' CONT
FROM DUAL
UNION ALL
SELECT 2 ID, 'aa' CONT
FROM DUAL
UNION ALL
SELECT 3 ID, 'b' CONT
FROM DUAL
UNION ALL
SELECT 4 ID, 'c' CONT
FROM DUAL
UNION ALL
SELECT 5 ID, 'd' CONT
FROM DUAL
UNION ALL
SELECT 6 ID, 'ae' CONT
FROM DUAL)
SELECT T3.ID, T3.CONT
FROM (SELECT T.*, CEIL(ROWNUM / 3) RN FROM T) T3,
(SELECT T2.RN
FROM (SELECT T1.*, CEIL(ROWNUM / 3) RN FROM T T1) T2
WHERE T2.ID = 4) T4
WHERE T3.RN = T4.RN
ORDER BY t3.id;
恩,是的,基本思路就是根据输入id的rownum找到这条数据在第几页,然后跳转到这一页
恩,是的,基本思路就是根据输入id的rownum找到这条数据在第几页,然后跳转到这一页
以上这个SQL应该可以的,你可以测试一下