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就会大大提高效率。而且代码的可读性也好。