oracle中的with和base,Oracle中WITH AS话语

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