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的字段

这是因为在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 略有不同，因此需要将临时变量的计算放在子查询中，并使用外层查询来获取这些变量的值。最终的结果集是从子查询中返回的。

神通数据库下 一下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

这是一个比较复杂的 SQL 语句，主要是用于对文档信息表进行排序和排名操作，并且使用了 MySQL 中的用户变量。

下面是这个 SQL 语句的详细解释：

SELECT
    @ROWNUM := @ROWNUM + 1 AS TEMPRANK, -- 对每一行进行排名，使用用户变量 @ROWNUM
    @INCRNUM := CASE
        WHEN @ROWTOTAL = OBJ.YLCOUNT + 1 THEN @INCRNUM -- 如果当前行的下载数量和上一行一样，则排名也相同
        WHEN @ROWTOTAL := OBJ.YLCOUNT + 1 THEN @ROWNUM -- 否则，将 @ROWNUM 赋值给 @INCRNUM
    END AS FILERANK, -- 最终排名结果
    OBJ.* -- 选择所有表格列
FROM (
    SELECT 
        DOCINFO.TITLE, -- 文档标题
        DOCINFO.DOC_ID AS DOCID, -- 文档 ID
        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 -- 用户 ID 对应的作者 ID
        AND sys_stru.STRU_ID = U.DEPARTMENT_ID -- 用户所在部门对应的组织结构 ID
        AND O.ORGAN_ID = sys_stru.ORGAN_ID -- 组织表中的组织 ID 对应的组织结构 ID
    GROUP BY
        DOCINFO.DOC_ID, -- 文档 ID
        U.USER_NAME -- 用户名
    ORDER BY
        DOCINFO.DOWNLOAD_NUM DESC, -- 下载数量降序排列
        DOCINFO.TITLE DESC -- 标题降序排列
) OBJ, (SELECT @ROWNUM := 0, @ROWTOTAL := NULL, @INCRNUM := 0) R -- 初始化用户变量

这个 SQL 语句的作用是将文档信息表中的文档按照下载数量和标题进行降序排列，并且对每个文档进行排名，最终返回排序后的结果集。