以上sql查询到的还是重复数据，根据MATERIAL_RETURN_MES表中的ASN_NO字段信息进行去重

如果你根据 `MATERIAL_RETURN_MES` 表中的 `ASN_NO` 字段进行去重，那么可以使用 `GROUP BY` 子句来实现。具体的 SQL 语句如下所示：

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 
GROUP BY A.ASN_NO
ORDER BY A.ASN_CREATION_TIME DESC

这样就可以根据 `ASN_NO` 字段进行去重了。

相关问题

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 IF(B.BILL_NO IS NOT NULL, '已入库', '未入库') AS STATUS FROM MATERIAL_RETURN_MES A LEFT JOIN MATERIAL_RETURN B ON A.ASN_NO = B.BILL_NO WHERE A.STATUS = '已作废' OR B.BILL_NO IS NOT NULL SQL代码提示缺失右括号

请在 SQL 语句的最后加上一个右括号即可：

SELECT IF(B.BILL_NO IS NOT NULL, '已入库', '未入库') AS STATUS 
FROM MATERIAL_RETURN_MES A 
LEFT JOIN MATERIAL_RETURN B ON A.ASN_NO = B.BILL_NO 
WHERE A.STATUS = '已作废' OR B.BILL_NO IS NOT NULL