问题描述

简单的数据库查询问题，求大神解决！！HELP!

SELECT buyplan.buyPlanNo,handState,buyplan.unitNo,buyplan.ownNo,ownof.ownName,
unit.unitname,goodsNo,buyTypeNo,buyWayNo,goodsName,onePrice,calculateUnit,buyCount,
totalPrice,technologyIndex,operatePeople,newAddTime,newAddPeople,updateTime,updatePeople,
groupNo,thisBuyBudget,buyReportState
FROM (SELECT ROWNUM rn,buyPlanNo,unitNo,ownNo,goodsNo,buyTypeNo,buyWayNo,goodsName,
onePrice,calculateUnit,buyCount, totalPrice,technologyIndex,operatePeople,
newAddTime,newAddPeople,updateTime,updatePeople, groupNo,thisBuyBudget,buyReportState
FROM g3_buyplan WHERE 1=1 ) buyplan,g3_process process,g3_unit unit,g3_ownoffice ownof
WHERE buyplan.unitno=unit.unitno AND buyplan.ownno=ownof.ownno AND buyplan.buyplanno=process.buyplanno
AND rn > 0 AND rn <= 10 AND handstate = 2

字段有点多，不过前面的那些个字段大神们可以忽略，主要是最后那句话：
“AND rn > 0 AND rn <= 10”这句是用来做分页查询的，在这写上这个就什么也查不出来，删掉它就能查出数据。是位置写错了么？

解决方案

rn 没有对应的结果集啊
oracle数据库 select * from 表名 where 条件 把这怎么查询表的语句当成子查询 select * from （子查询） WHERE ROWNUM<=1
把你的整查询语句除了AND rn > 0 AND rn <= 10 都当成一个子查询 如上描述

解决方案二：

 SELECT
    *
FROM
    (
        SELECT
            buyplan.buyPlanNo,
            handState,
            buyplan.unitNo,
            buyplan.ownNo,
            ownof.ownName,
            unit.unitname,
            goodsNo,
            buyTypeNo,
            buyWayNo,
            goodsName,
            onePrice,
            calculateUnit,
            buyCount,
            totalPrice,
            technologyIndex,
            operatePeople,
            newAddTime,
            newAddPeople,
            updateTime,
            updatePeople,
            groupNo,
            thisBuyBudget,
            buyReportState
        FROM
            (
                SELECT
                    ROWNUM rn,
                    buyPlanNo,
                    unitNo,
                    ownNo,
                    goodsNo,
                    buyTypeNo,
                    buyWayNo,
                    goodsName,
                    onePrice,
                    calculateUnit,
                    buyCount,
                    totalPrice,
                    technologyIndex,
                    operatePeople,
                    newAddTime,
                    newAddPeople,
                    updateTime,
                    updatePeople,
                    groupNo,
                    thisBuyBudget,
                    buyReportState
                FROM
                    g3_buyplan
                WHERE
                    1 = 1
            ) buyplan,
            g3_process process,
            g3_unit unit,
            g3_ownoffice ownof
        WHERE
            buyplan.unitno = unit.unitno
        AND buyplan.ownno = ownof.ownno
        AND buyplan.buyplanno = process.buyplanno
        AND handstate = 2
    )
WHERE
    rn <= 10