如何将ROWNUM与嵌套查询一起使用

SELECT * FROM(
SELECT * FROM(
SELECT PART_NO, SRC_PART_NO, CTNM_ENG, DESCRIPTION, USER_ID, REG_DT, CHG_DT, FLAG,       
(select count(*) from ( SELECT PART_NO, SRC_PART_NO, CTNM_ENG, DESCRIPTION, USER_ID, REG_DT, CHG_DT, FLAG
FROM GM_PART_LIST
WHERE PART_NO LIKE  '%' || '%' AND SRC_PART_NO LIKE  '%' || '%' AND CTNM_ENG LIKE  'BOLT'|| '%'
AND 1 = 1) ) as total_count -- Nested subquery that return total count of record set. plug in same where conditions.
FROM GM_PART_LIST
WHERE PART_NO LIKE  '%' || '%' AND SRC_PART_NO LIKE  '%' || '%' AND CTNM_ENG LIKE  'BOLT'|| '%'
AND 1 = 1
ORDER BY PART_NO ASC))
WHERE ROWNUM BETWEEN 2 AND 202; 

How is it that with the above query if I search between 1 and 200 It pulls records fine but when I switch it to 2 or another integer above 1 it fails to query any records? is this a syntax issue? thank you in advance for any help anyone can offer.

如果我在1到200之间搜索，如何使用上面的查询它是如何将记录拉得很好但当我将其切换为2或大于1的另一个整数时它无法查询任何记录？这是一个语法问题吗？提前感谢您提供任何帮助。

1 个解决方案

#1

ROWNUM is assigned when a row is evaluated for the where conditions. The first row from the row source is retrieved and given ROWNUM=1. If one of the were conditions is ROWNUM > 1, this row will not be selected.

在评估where条件的行时，将分配ROWNUM。检索行源的第一行并给出ROWNUM = 1。如果其中一个条件是ROWNUM> 1，则不会选择此行。

Then ROWNUM=1 is reassigned to the next row (which again will fail the where clause) and so on. This is because in the end ROWNUM must run consecutively from 1, it will not be a sequence with gaps. So any condition that doesn't allow ROWNUM to be 1 (example: where mod(ROWNUM, 2) = 0) will produce zero rows, and for exactly the same reason.

然后将ROWNUM = 1重新分配给下一行（这将再次使where子句失败），依此类推。这是因为最后ROWNUM必须从1连续运行，它不会是一个有间隙的序列。所以任何不允许ROWNUM为1的条件（例如：其中mod（ROWNUM，2）= 0）将产生零行，并且出于完全相同的原因。