SELECT p.PROJECT_NO,ROWNUM FROM MRP_PROJECT_PLAN p WHERE EXISTS(SELECT 'X' FROM FAULT_REPORT r WHERE r.PROJECT_NO = p.PROJECT_NO) AND ROWNUM > 5 AND ROWNUM < 15 GROUP BY p.PROJECT_NO,ROWNUM ORDER BY ROWNUM此SQL中 ROWNUM >0 < 10可以查出数据 如果改为ROWNUM > 5则查不出数据 怎么解决

在 Oracle 数据库中，ROWNUM 是一个伪列，它表示返回结果集中每一行的行号。在查询中使用 ROWNUM 时需要注意以下几点：

1. ROWNUM 是在结果集返回后才会分配的，因此在 WHERE 子句中使用 ROWNUM 没有意义，只能在结果集返回后使用 ROWNUM 进行过滤。

2. 当 ROWNUM > n 时，Oracle 数据库会返回前 n 行结果并将其丢弃，然后将行号重新开始计数。因此，如果您要选择特定范围的行，请使用子查询或其他方法，如使用 RANK 或 DENSE_RANK 函数。

在这个查询中，如果您想要选择第 6 - 15 行的数据，可以使用子查询将其包装起来，如下所示：

SELECT * FROM (
    SELECT p.PROJECT_NO, ROWNUM rn
    FROM MRP_PROJECT_PLAN p
    WHERE EXISTS (
        SELECT 'X' FROM FAULT_REPORT r WHERE r.PROJECT_NO = p.PROJECT_NO
    )
) WHERE rn BETWEEN 6 AND 15;

在这个查询中，首先选择 MRP_PROJECT_PLAN 表中存在故障报告的项目编号，然后使用子查询将结果集的行号 ROWNUM 存储在 rn 列中。最后，在外部查询中选择 rn 值在 6 到 15 之间的行，以选择特定范围内的行。

相关问题

优化以下SQL使其查询速度更快SELECT P.LOT_NO UNIT_ID, '0' AS UNIT_TYPE, '宗地' AS UNIT_TYPE_DES, NVL(BD.PARCEL_CODE_TMP, P.LOT_NO || P.SITE) UNIT_CODE, P.PARCEL_NO, P.LOC_CANTON, BD.REAL_UNIT_NO AS UNIT_CODE_GB, P.LU_LOCATION, P.SITE_KEY, P.CHG_TYPE, P.LU_AREA AS UNIT_AREA, P.PARCEL_CODE FROM V_LD_PARCEL P LEFT JOIN V_BD_PARCEL BD ON BD.LOT_NO = P.LOT_NO where 2 > 1 and not exists (select 1 from v_bk_reg_idx idx where idx.reg_unit_code = p.lot_no and idx.reg_unit_type = '0' and idx.buss_reg_type = '1101' and (idx.status_book = '1' or idx.status_book = '9')) and not exists(select 1 from v_bk_reg_idx idx where (idx.reg_unit_code, idx.reg_unit_type) in (select rise_id, '1' from bd_rise br, bd_parcel bp where br.par_lot_no = bp.lot_no and bp.lot_no = p.lot_no) and idx.buss_reg_type = '1200') and p.parcel_no = ?

首先，可以看到这个SQL语句中有两个子查询。这些子查询可能会导致性能问题，因此可以尝试优化它们。

以下是可能的优化措施：

1. 将子查询转换为JOIN语句

将第一个子查询转换为JOIN语句，可以避免使用NOT EXISTS，从而提高性能。例如：

SELECT P.LOT_NO UNIT_ID,
       '0' AS UNIT_TYPE,
       '宗地' AS UNIT_TYPE_DES,
       NVL(BD.PARCEL_CODE_TMP, P.LOT_NO || P.SITE) UNIT_CODE,
       P.PARCEL_NO,
       P.LOC_CANTON,
       BD.REAL_UNIT_NO AS UNIT_CODE_GB,
       P.LU_LOCATION,
       P.SITE_KEY,
       P.CHG_TYPE,
       P.LU_AREA AS UNIT_AREA,
       P.PARCEL_CODE
FROM V_LD_PARCEL P
LEFT JOIN V_BD_PARCEL BD ON BD.LOT_NO = P.LOT_NO
LEFT JOIN v_bk_reg_idx idx ON idx.reg_unit_code = p.lot_no
    AND idx.reg_unit_type = '0'
    AND idx.buss_reg_type = '1101'
    AND (idx.status_book = '1' OR idx.status_book = '9')
WHERE 2 > 1
    AND idx.reg_unit_code IS NULL
    AND P.PARCEL_NO = ?

同样，将第二个子查询转换为JOIN语句也可以提高性能：

SELECT P.LOT_NO UNIT_ID,
       '0' AS UNIT_TYPE,
       '宗地' AS UNIT_TYPE_DES,
       NVL(BD.PARCEL_CODE_TMP, P.LOT_NO || P.SITE) UNIT_CODE,
       P.PARCEL_NO,
       P.LOC_CANTON,
       BD.REAL_UNIT_NO AS UNIT_CODE_GB,
       P.LU_LOCATION,
       P.SITE_KEY,
       P.CHG_TYPE,
       P.LU_AREA AS UNIT_AREA,
       P.PARCEL_CODE
FROM V_LD_PARCEL P
LEFT JOIN V_BD_PARCEL BD ON BD.LOT_NO = P.LOT_NO
LEFT JOIN v_bk_reg_idx idx ON idx.reg_unit_code = p.lot_no
    AND idx.reg_unit_type = '0'
    AND idx.buss_reg_type = '1101'
    AND (idx.status_book = '1' OR idx.status_book = '9')
LEFT JOIN v_bk_reg_idx idx2 ON idx2.reg_unit_code = (SELECT rise_id FROM bd_rise br, bd_parcel bp WHERE br.par_lot_no = bp.lot_no AND bp.lot_no = p.lot_no)
    AND idx2.reg_unit_type = '1'
    AND idx2.buss_reg_type = '1200'
WHERE 2 > 1
    AND idx.reg_unit_code IS NULL
    AND idx2.reg_unit_code IS NULL
    AND P.PARCEL_NO = ?

2. 添加索引

为V_LD_PARCEL和V_BD_PARCEL表添加适当的索引，可以加速JOIN操作。例如：

CREATE INDEX idx_v_ld_parcel_parcel_no ON V_LD_PARCEL (PARCEL_NO);
CREATE INDEX idx_v_bd_parcel_lot_no ON V_BD_PARCEL (LOT_NO);

3. 减少查询返回的行数

如果查询返回的行数非常大，可以考虑分页或者限制返回的行数。这可以减少查询的执行时间。例如，可以添加以下子句来限制返回的行数：

WHERE ROWNUM <= 100

SELECT * FROM ( SELECT a.POLICY_NO AS businessNo, a.ENDORSE_SEQ_NO AS businessSerialNo, a.TOTAL_SERIAL_NO AS totalSerialNo, a.BILL_TYPE AS billType, a.ISSUE_COMPANY AS companyCode, a.PLAN_CCY AS currency, a.EXCHANGE_RATE AS exchangeRate, a.PLAN_FEE AS totalAmount, a.VAT AS taxAmount, a.BUSINESS_NO AS proposalNo, CONVERT(decimal(16, 2), round(a.PLAN_FEE * a.EXCHANGE_RATE, 2)) AS exchangeTotalAmount, CONVERT(decimal(16, 2), round(a.VAT * a.EXCHANGE_RATE, 2)) AS exchangeTaxAmount, 'P' AS certiType, (CASE a.VAT WHEN '0' THEN 'N' ELSE 'Y' END) AS taxExemptFlag, a.PAY_NO AS payNo, ( SELECT top 1 g.LOSS_NO FROM GPLOSSFEE g WHERE g.POLICY_NO = a.POLICY_NO) AS lossNo FROM GPPOLICYPLAN a LEFT JOIN ( SELECT t.POLICY_NO, t.BUSINESS_SEQNO, t.PAY_NO, t.FEE_TYPE_CODE, t.TOTAL_SERIAL_NO FROM GPINPUTVATINVOICEREL t, GPPOLICYPLAN b WHERE t.BUSINESS_NO = b.POLICY_NO AND t.BUSINESS_SEQNO = b.ENDORSE_SEQ_NO AND t.PAY_NO = b.PAY_NO AND t.FEE_TYPE_CODE = b.BILL_TYPE AND t.TOTAL_SERIAL_NO = b.TOTAL_SERIAL_NO) p ON a.POLICY_NO = p.POLICY_NO AND a.ENDORSE_SEQ_NO = p.BUSINESS_SEQNO AND a.PAY_NO = p.PAY_NO AND a.BILL_TYPE = p.FEE_TYPE_CODE AND a.TOTAL_SERIAL_NO = p.TOTAL_SERIAL_NO WHERE (a.PAY_NO != p.PAY_NO OR p.PAY_NO IS NULL) AND a.PLAN_FEE < 0 AND a.BILL_TYPE != 'R10' AND a.COMPANY_CODE IN ( '729021' , '729020' , '729023' , '729022' , '729000' , '729061' , '729060' , '729041' , '729040' , '729081' , '729080' , '729079' , '729010' , '729031' , '729078' , '729077' , '729011' , '729050' , '729072' , '729071' , '729074' , '729052' , '729030' , '729051' , '729073' , '729070' , '86' ) ) WHERE rownum <= 5 oracl怎么改写

这个SQL查询语句可以改写为以下形式：

SELECT *
FROM (
    SELECT a.POLICY_NO AS businessNo,
           a.ENDORSE_SEQ_NO AS businessSerialNo,
           a.TOTAL_SERIAL_NO AS totalSerialNo,
           a.BILL_TYPE AS billType,
           a.ISSUE_COMPANY AS companyCode,
           a.PLAN_CCY AS currency,
           a.EXCHANGE_RATE AS exchangeRate,
           a.PLAN_FEE AS totalAmount,
           a.VAT AS taxAmount,
           a.BUSINESS_NO AS proposalNo,
           CAST(ROUND(a.PLAN_FEE * a.EXCHANGE_RATE, 2) AS DECIMAL(16, 2)) AS exchangeTotalAmount,
           CAST(ROUND(a.VAT * a.EXCHANGE_RATE, 2) AS DECIMAL(16, 2)) AS exchangeTaxAmount,
           'P' AS certiType,
           CASE a.VAT
               WHEN '0' THEN 'N'
               ELSE 'Y'
           END AS taxExemptFlag,
           a.PAY_NO AS payNo,
           (
               SELECT TOP 1 g.LOSS_NO
               FROM GPLOSSFEE g
               WHERE g.POLICY_NO = a.POLICY_NO
           ) AS lossNo
    FROM GPPOLICYPLAN a
    LEFT JOIN (
        SELECT t.POLICY_NO,
               t.BUSINESS_SEQNO,
               t.PAY_NO,
               t.FEE_TYPE_CODE,
               t.TOTAL_SERIAL_NO
        FROM GPINPUTVATINVOICEREL t
        INNER JOIN GPPOLICYPLAN b ON t.BUSINESS_NO = b.POLICY_NO
                                  AND t.BUSINESS_SEQNO = b.ENDORSE_SEQ_NO
                                  AND t.PAY_NO = b.PAY_NO
                                  AND t.FEE_TYPE_CODE = b.BILL_TYPE
                                  AND t.TOTAL_SERIAL_NO = b.TOTAL_SERIAL_NO
    ) p ON a.POLICY_NO = p.POLICY_NO
         AND a.ENDORSE_SEQ_NO = p.BUSINESS_SEQNO
         AND a.PAY_NO = p.PAY_NO
         AND a.BILL_TYPE = p.FEE_TYPE_CODE
         AND a.TOTAL_SERIAL_NO = p.TOTAL_SERIAL_NO
    WHERE (a.PAY_NO != p.PAY_NO OR p.PAY_NO IS NULL)
          AND a.PLAN_FEE < 0
          AND a.BILL_TYPE != 'R10'
          AND a.COMPANY_CODE IN ('729021', '729020', '729023', '729022', '729000', '729061', '729060', '729041', '729040', '729081', '729080', '729079', '729010', '729031', '729078', '729077', '729011', '729050', '729072', '729071', '729074', '729052', '729030', '729051', '729073', '729070', '86')
    ORDER BY ROWNUM
)
WHERE ROWNUM <= 5

主要的改动包括：

- 将转换数据类型的函数 CONVERT 改写为 CAST。
- 将子查询的 TOP 1 改成了 Oracle 中的 ROWNUM。
- 将 LEFT JOIN 改为 INNER JOIN，并将 JOIN 条件移到了 ON 子句中。