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 rid, a.* FROM ( SELECT jg.V_YYJGBH, jg.V_YYJGNAME, jg.V_YYJGNAME AS code_name FROM YYXT_T_YYJGXX jg WHERE ) a mysql怎么写

在MySQL中，`rownum`并不是一个内置函数或关键字，因此我们需要使用其他方法来模拟`rownum`的功能。通常可以使用变量来实现类似的效果。以下是如何在MySQL中编写类似的SQL查询：

SELECT
    @rownum := @rownum + 1 AS rid,
    a.*
FROM
    (SELECT jg.V_YYJGBH, jg.V_YYJGNAME, jg.V_YYJGNAME AS code_name FROM YYXT_T_YYJGXX jg) a,
    (SELECT @rownum := 0) r;

在这个查询中，我们使用了两个子查询：
1. 子查询 `a` 用于选择 `YYXT_T_YYJGXX` 表中的数据。
2. 子查询 `r` 用于初始化变量 `@rownum`。

然后，我们在主查询中使用 `@rownum := @rownum + 1` 来生成行号 `rid`。

请注意，MySQL中的变量赋值和递增操作是在同一行中完成的，因此在主查询中我们将 `@rownum := @rownum + 1` 放在 `SELECT` 子句中。