Oracle中WITH AS语句

在SQL规范中有了with as 语句，该语句实质上就是创建临时视图，来帮助你简化语句并使语句结构更清晰更容易阅读。

例子：将选课结果表中一门课都不能抽中的学生查询出来。

WITH T_LXMD AS (
        SELECT XKJG.JXBID , KC.KCMC, JXB.BJMC , XKJG.XH, TT.XM, DW.YXMC, ZY.ZYMC, XKJG.XKZY  FROM T_XKGL_XKJG XKJG
            LEFT JOIN T_XJ_JBXX TT ON XKJG.XH = TT.XH
            LEFT JOIN T_DM_DW DW ON TT.XSYXDM = DW.DWDM
            LEFT JOIN T_PY_ZY ZY ON ZY.ZYDM = TT.ZYM
            LEFT JOIN T_JXRWGL_JXB JXB ON JXB.JXBID = XKJG.JXBID
            LEFT JOIN T_PY_KC KC ON KC.KCDM = XKJG.KCDM
        WHERE (XKJG.JXBID IN (SELECT JXBID FROM T_XKGL_JXBXKZYTJXX T WHERE T.XXYZYRS+ T.XXEZYRS >= T.KXRL)
               AND XKJG.XKZY = 3 )
             OR
             (XKJG.JXBID IN (SELECT JXBID FROM T_XKGL_JXBXKZYTJXX T WHERE (T.XXYZYRS) >= T.KXRL)
               AND XKJG.XKZY > 1 )

  )
  SELECT DISTINCT XH,XM,YXMC,ZYMC  FROM T_LXMD WHERE XH NOT IN(
    SELECT DISTINCT XH FROM T_XKGL_XKJG T WHERE
          NOT EXISTS (SELECT 1 FROM T_LXMD LXMD WHERE LXMD.JXBID = T.JXBID AND LXMD.XH = T.XH)
  )

 利用with as创建临时表，如果你后面需要不止一次地引用这个结果，那么WITH就会大大提高效率。而且代码的可读性也好。