SELECT ROWNUM AS MYROWNUM, A.*, CASE WHEN B.BILL_NO IS NULL THEN A.STATUS ELSE '已退料' END AS STATUS1 FROM MATERIAL_RETURN_MES A LEFT JOIN MATERIAL_RETURN B ON A.ASN_NO = B.BILL_NO ORDER BY ASN_CREATION_TIME DESC 以上sql语句查询到重复数据了，怎么去掉重复数据

你可以使用 DISTINCT 关键字来去除重复数据，如下所示：

SELECT DISTINCT ROWNUM AS MYROWNUM, A.*, 
    CASE WHEN B.BILL_NO IS NULL THEN A.STATUS ELSE '已退料' END AS STATUS1 
FROM MATERIAL_RETURN_MES A 
LEFT JOIN MATERIAL_RETURN B ON A.ASN_NO = B.BILL_NO 
ORDER BY ASN_CREATION_TIME DESC

这样就可以去掉重复数据了。

相关问题

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 子句中。

select rownum,a.* from (select rownum rn,表.* from 表 where rownum >= 5)a where a.rn<=10

根据您提供的查询语句，它是用于在Oracle数据库中获取从第5行到第10行的数据。该查询使用了两个嵌套的子查询，其中第一个子查询用于为每一行分配一个唯一的行号（rn），第二个子查询用于选择行号在5到10之间的数据。

请注意，您提供的查询语句中的"表"是一个占位符，您需要将其替换为实际的表名。另外，这个查询语句使用了Oracle特定的语法，并不适用于所有数据库系统。

如果您有任何其他问题，请随时提问。