mysql增删改查语句_详解Oracle中with as语句以及与增删改查的结合使用

最新推荐文章于 2023-01-05 14:35:10 发布

weixin_39548733

最新推荐文章于 2023-01-05 14:35:10 发布

阅读量286

点赞数

文章标签： mysql增删改查语句 servlet增删改查实例

概述

WITH AS短语，也叫做子查询部分(subquery factoring)，可以做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNION ALL的不同部分，作为提供数据的部分。

特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用WITH AS短语，则只要执行一遍即可。如果WITH AS短语所定义的表名被调用两次以上，则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里，如果只是被调用一次，则不会。而提示materialize则是强制将WITH AS短语里的数据放入一个全局临时表里。

01 with as 语法

单个语法：

with tempName as (select ....)select ...

多个语法：

with tempName1 as (select ....)，tempName2 as (select ....)，tempName3 as (select ....) ...select ...

With查询语句不是以select开始的，而是以“WITH”关键字开头

可认为在真正进行查询之前预先构造了一个临时表TT，之后便可多次使用它做进一步的分析和处理。

02 WITH AS实例

例：从1-19中得到11-14。一般的sql如下：

select * from ( --模拟生一个20行的数据SELECT LEVEL AS lv FROM DUAL CONNECT BY LEVEL < 20) tt WHERE tt.lv > 10 AND tt.lv < 15

使用With as 的SQL为：

with TT as( SELECT LEVEL AS lv FROM DUAL CONNECT BY LEVEL < 20 ) select lv from TT WHERE lv > 10 AND lv < 15;

多个临时表实例：

WITHT3 AS(SELECT T1.ID, T1.CODE1, T2.DESCRIPTIONFROM TB_DATA T1, TB_CODE T2WHERE T1.CODE1 = T2.CODE),T4 AS(SELECT T1.ID, T1.CODE2, T2.DESCRIPTIONFROM TB_DATA T1, TB_CODE T2WHERE T1.CODE2 = T2.CODE)SELECT T3.ID, T3.DESCRIPTION, T4.DESCRIPTIONFROM T3, T4WHERE T3.ID = T4.IDORDER BY ID;

03 WITH Clause方法的优点

增加了SQL的易读性，如果构造了多个子查询，结构会更清晰；更重要的是：“一次分析，多次使用”，这也是为什么会提供性能的地方，达到了“少读”的目标。

上面第一种使用子查询的方法表被扫描了两次，而使用WITH Clause方法，表仅被扫描一次。这样可以大大的提高数据分析和查询的效率。

另外，观察WITH Clause方法执行计划，其中“SYS_TEMP_XXXX”便是在运行过程中构造的中间统计结果临时表。

04 WITH AS 与增删改查结合用法

4.1与select查询语句结合使用

查询同一个单据编号对应的借款单和核销单中，借款金额不相等的单据

with verificationInfo as (select ment.fnumber from shenzhenjm.t_finance_expenseremburseitem t left join shenzhenjm.t_finance_expenserembursement ment on ment.fid = t.fkrembursementid where 1 = 1 group by ment.fnumber), loanInfo as (select ment.fnumber from shenzhenjm.t_finance_expenseremburseitem2 t left join shenzhenjm.t_finance_expenserembursement ment on ment.fid = t.fkrembursementid where 1 = 1 group by ment.fnumber) select * from verificationInfo v, loanInfo l where l.fnumber = v.fnumber;

4.2与insert结合使用

如下的with as语句，不能放在insert前，而是放在紧接着要调用的地方前

insert into T_finance_ExpenseRemburseItem2 (FID, FKREMBURSEMENTID, FAMOUNT, FKCREATEBYID, FCREATETIME, FKCUID, FKCOSTTYPEID, FCOSTTYPENAME)  with verificationInfo as (select ment.fnumber, sum(t.famount) vLoanSum, ment.fnumber "单据编号