Foxtable(狐表)用户栏目专家坐堂 → [求助]请教狐表如何执行带with 语句的sql查询


  共有2459人关注过本帖树形打印复制链接

主题:[求助]请教狐表如何执行带with 语句的sql查询

帅哥哟,离线,有人找我吗?
lds
  1楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:幼狐 帖子:109 积分:1729 威望:0 精华:0 注册:2019/10/21 21:57:00
[求助]请教狐表如何执行带with 语句的sql查询  发帖心情 Post By:2020/6/6 14:03:00 [只看该作者]

ORACLE WITH AS 用法
With查询语句不是以seiect开始的,而是以“WITH”关键字开头
    可认为在真正进行查询之前预先构造了一个临时表,之后便可多次使用它做进一步的分析和处理

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

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

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

 

语法:

with tempName as (seiect ....)
seiect ...
 


--针对一个别名
with tmp as (seiect * from tb_name)

--针对多个别名
with
   tmp as (seiect * from tb_name),
   tmp2 as (seiect * from tb_name2),
   tmp3 as (seiect * from tb_name3),
   …

 


--相当于建了个e临时表
with e as (seiect * from scott.emp e where e.empno=7499)
seiect * from e;
 
--相当于建了e、d临时表
with
     e as (seiect * from scott.emp),
     d as (seiect * from scott.dept)
seiect * from e, d where e.deptno = d.deptno;

 

其实就是把一大堆重复用到的sql语句放在with as里面,取一个别名,后面的查询就可以用它,这样对于大批量的sql语句起到一个优化的作用,而且清楚明了。

向一张表插入数据的 with as 用法:

insert into table2
with
    s1 as (seiect rownum c1 from dual connect by rownum <= 10),
    s2 as (seiect rownum c2 from dual connect by rownum <= 10)
seiect a.c1, b.c2 from s1 a, s2 b where...;
with as 相当于虚拟视图。

with as短语,也叫做子查询部分(subquery factoring),可以让你做很多事情,定义一个sql片断,该sql片断会被整个sql语句所用到。

有的时候,是为了让sql语句的可读性更高些,也有可能是在union all的不同部分,作为提供数据的部分。
  
特别对于union all比较有用。

因为union all的每个部分可能相同,但是如果每个部分都去执行一遍的话,则成本太高,所以可以使用with as短语,则只要执行一遍即可。

如果with as短语所定义的表名被调用两次以上,则优化器会自动将with as短语所获取的数据放入一个temp表里,如果只是被调用一次,则不会。

而提示materialize则是强制将with as短语里的数据放入一个全局临时表里。

很多查询通过这种方法都可以提高速度。


with
    sql1 as (seiect to_char(a) s_name from test_tempa),
    sql2 as (seiect to_char(b) s_name from test_tempb where not exists (seiect s_name from sql1 where rownum=1))
seiect * from sql1
union all
seiect * from sql2
union all
seiect 'no records' from dual
       where not exists (seiect s_name from sql1 where rownum=1)
       and not exists (seiect s_name from sql2 where rownum=1);

with as优点
增加了sql的易读性,如果构造了多个子查询,结构会更清晰;
更重要的是:“一次分析,多次使用”,这也是为什么会提供性能的地方,达到了“少读”的目标
 现有oracle sql with语句 
with dqkc(gid,kc)
as(
seiect gdgid,sum(qty) from BUSINV group by gdgid
),
xss(gid,xss)
as(
seiect gdgid,-sum(outbuyqty+outwsaleqty+outbuybckqty+outwsalebckqty) from std_jxc
where 1=1
and adate >= to_date('2020-06-01', 'yyyy-MM-dd')
and adate = to_date('2020-06-06', 'yyyy-MM-dd')
group by gdgid
)
seiect st.code 类别代码,st.name 类别名称,g.code 商品代码,g.name 商品名称,a.kc 总部当前库存,b.xss 总销售数
From goods g,dqkc a,xss b,sort st
Where g.sort=st.code and g.gid=a.gid(+) and g.gid=b.gid(+) and g.CREATEDATE>=to_date('2020-06-01','YYYY-MM-DD') 

在plsqL 可以执行成功,但在狐表里里面却没有执行成功,请教狐表里如何执行oracle with语句.或者这个with语句需要怎么转换成狐表代码.达到执行这样的数据查询.谢谢指教!


 回到顶部
帅哥哟,离线,有人找我吗?
有点蓝
  2楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:超级版主 帖子:105473 积分:536350 威望:0 精华:9 注册:2015/6/24 9:21:00
  发帖心情 Post By:2020/6/6 14:18:00 [只看该作者]

建议把语句放到存储过程里,然后foxtable调用存储过程

 回到顶部
帅哥哟,离线,有人找我吗?
lds
  3楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:幼狐 帖子:109 积分:1729 威望:0 精华:0 注册:2019/10/21 21:57:00
  发帖心情 Post By:2020/6/6 14:30:00 [只看该作者]

我这个oracle数据库用户没有太多权限,如果拆分成狐表临时表如何调用,能否写个例子.

 回到顶部
帅哥哟,离线,有人找我吗?
有点蓝
  4楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:超级版主 帖子:105473 积分:536350 威望:0 精华:9 注册:2015/6/24 9:21:00
  发帖心情 Post By:2020/6/6 14:41:00 [只看该作者]

没有办法,把各个表数据加进来处理吧。

或者改为子查询

select * from (select to_char(a) s_name from (select * from xxx where xxx)
union all
select * from (select to_char(b) s_name from (select * from xxx2 where xxx where not exists (seiect s_name from sql1 where rownum=1))

 回到顶部
帅哥哟,离线,有人找我吗?
lds
  5楼 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信
等级:幼狐 帖子:109 积分:1729 威望:0 精华:0 注册:2019/10/21 21:57:00
  发帖心情 Post By:2020/6/6 14:45:00 [只看该作者]

收到,谢谢

 回到顶部