Oracle中SELECT ROWNUM, CASE WHEN d1.sum_deduct > d2.count THEN d1.department END AS department, NVL(d1.defect_desc, '') AS defect_desc FROM hryt_gca_vehicle_audit_defect d1 JOIN hryt_gca_vehicle_issue d2 ON d1.department = d2.department WHERE TO_CHAR(d1.create_date,'yyyy mm iw') = TO_CHAR(SYSDATE, 'yyyy mm iw') AND d1.department IS NOT null;为什么还能查出为null的字段
时间: 2023-05-11 19:06:58 浏览: 58
这是因为在SQL中使用了JOIN操作,JOIN操作会将两个表中符合条件的记录连接起来,如果某个表中没有符合条件的记录,那么连接后的结果中就会出现null值。在这个查询中,使用了LEFT JOIN,所以即使d1.department为null,也会被包含在结果中。
相关问题
Oracle 下SQL语句怎么写 SELECT @ROWNUM := @ROWNUM + 1 AS TEMPRANK, @INCRNUM := CASE WHEN @ROWTOTAL = OBJ.YLCOUNT + 1 THEN @INCRNUM WHEN @ROWTOTAL := OBJ.YLCOUNT + 1 THEN @ROWNUM END AS FILERANK, OBJ.* FROM ( SELECT DOCINFO.TITLE, DOCINFO.DOC_ID AS DOCID, DOCINFO.DOC_TYPE AS DOCTYPE, U.USER_NAME AS AUTHORNAME, CONCAT( U.USER_NAME, '/', CASE WHEN O.SHORT_NAME = '' THEN '无' ELSE O.SHORT_NAME END ) AS USERNAME, IFNULL( DOCINFO.DOWNLOAD_NUM, 0 ) AS YLCOUNT FROM DOC_INFO DOCINFO, SYS_USERS U, SYS_ORGAN O, sys_stru WHERE DOCINFO.VALID_FLAG = '1' AND IFNULL( DOCINFO.DOWNLOAD_NUM, 0 ) != 0 AND U.USER_ID = DOCINFO.AUTHOR_ID AND sys_stru.STRU_ID = U.DEPARTMENT_ID AND O.ORGAN_ID = sys_stru.ORGAN_ID GROUP BY DOCINFO.DOC_ID, U.USER_NAME ORDER BY DOCINFO.DOWNLOAD_NUM DESC, DOCINFO.TITLE DESC ) OBJ, ( SELECT @ROWNUM := 0, @ROWTOTAL := NULL, @INCRNUM := 0 ) R
在 Oracle 中,可以将该 SQL 语句写成如下形式:
```
SELECT TEMPRANK, FILERANK, TITLE, DOCID, DOCTYPE, AUTHORNAME, USERNAME, YLCOUNT
FROM (
SELECT DOCINFO.TITLE, DOCINFO.DOC_ID AS DOCID, DOCINFO.DOC_TYPE AS DOCTYPE,
U.USER_NAME AS AUTHORNAME, CONCAT(U.USER_NAME, '/', CASE WHEN O.SHORT_NAME = '' THEN '无' ELSE O.SHORT_NAME END) AS USERNAME,
IFNULL(DOCINFO.DOWNLOAD_NUM, 0) AS YLCOUNT,
@ROWNUM := @ROWNUM + 1 AS TEMPRANK,
@INCRNUM := CASE
WHEN @ROWTOTAL = OBJ.YLCOUNT + 1 THEN @INCRNUM
WHEN @ROWTOTAL := OBJ.YLCOUNT + 1 THEN @ROWNUM
END AS FILERANK
FROM DOC_INFO DOCINFO, SYS_USERS U, SYS_ORGAN O, sys_stru
WHERE DOCINFO.VALID_FLAG = '1'
AND IFNULL(DOCINFO.DOWNLOAD_NUM, 0) != 0
AND U.USER_ID = DOCINFO.AUTHOR_ID
AND sys_stru.STRU_ID = U.DEPARTMENT_ID
AND O.ORGAN_ID = sys_stru.ORGAN_ID
GROUP BY DOCINFO.DOC_ID, U.USER_NAME
ORDER BY DOCINFO.DOWNLOAD_NUM DESC, DOCINFO.TITLE DESC
) OBJ, (SELECT @ROWNUM := 0, @ROWTOTAL := NULL, @INCRNUM := 0) R;
```
在 Oracle 中,变量需要在 SQL 语句外面进行声明和初始化。这里使用了 `@ROWNUM`、`@ROWTOTAL` 和 `@INCRNUM` 三个变量,它们都在 SQL 语句的最后一行进行了声明和初始化。
此外,由于 Oracle 对于 SQL 语句的执行顺序与 MySQL 略有不同,因此需要将临时变量的计算放在子查询中,并使用外层查询来获取这些变量的值。最终的结果集是从子查询中返回的。
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
```
这样就可以去掉重复数据了。