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