zhang276879619
zhang276879619
采纳率100%
2015-08-25 02:38 浏览 1.5k
已采纳

简单的数据库查询问题,求大神解决!!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”这句是用来做分页查询的,在这写上这个就什么也查不出来,删掉它就能查出数据。是位置写错了么?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答 默认 最新

  • 已采纳
    strutce 丵鹰 2015-08-25 02:50

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

    点赞 1 评论 复制链接分享
  • strutce 丵鹰 2015-08-25 03:21
     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
    
    点赞 1 评论 复制链接分享

相关推荐