精华内容
下载资源
问答
  • with tableName的用法

    2017-04-28 11:54:08
    有时候在Oracle查询时需要建立临时表,可以简化查询...用法with tableName as(sql语句) select * from tableName... 例如: with a as --建立临时表 (select sum(pt.c_all) sumall, q.c_createsupp from t_quo

    有时候在Oracle查询时需要建立临时表,可以简化查询,使读者更加清楚明白,在一个查询sql中,可以使用临时表。

    用法:with tableName as(sql语句)  select * from tableName...

    例如:

    with a as   --建立临时表
     (select sum(pt.c_all) sumall, q.c_createsupp
        from t_quotebill_part pt
       inner join t_quotebill q
          on pt.c_quotebill = q.c_id
       where q.c_enquirebill = 7741809513
       group by q.c_createsupp)
    select c_createsupp suppcode --使用临时表
      from a
     where a.sumall in (select min(sumall) from a)


    展开全文
  • Oracle with语句的用法

    千次阅读 2011-11-30 14:44:45
    Oracle with语句是经常可以见到的语句,下面就为您详细介绍Oracle with语句的用法,如果您对Oracle with语句感兴趣的话,不妨一看。 当查询中多次用到某一部分时,可以用Oracle with语句创建一个公共临时表。因为子...

    Oracle with语句是经常可以见到的语句,下面就为您详细介绍Oracle with语句的用法,如果您对Oracle with语句感兴趣的话,不妨一看。

    当查询中多次用到某一部分时,可以用Oracle with语句创建一个公共临时表。因为子查询在内存临时表中,避免了重复解析,所以执行效率会提高不少。临时表在一次查询结束自动清除。

    一般语法格式:

     
    1. with   
    2. alias_name1 as    (subquery1),  
    3. alias_name2 as    (subQuery2),  
    4. ……  
    5. alias_nameN as    (subQueryN)  
    6. select col1,col2…… col3   
    7.      from alias_name1,alias_name2……,alias_nameN 

    Oracle with语句的例子:

     
    1. SQL> WITH  
    2. Q1 AS (SELECT 3 + 5 S FROM DUAL),  
    3.     Q2 AS (SELECT 3 * 5 M FROM DUAL),  
    4.     Q3 AS (SELECT S, M, S + M, S * M FROM Q1, Q2)  
    5. SELECT * FROM Q3; 

    输出结果:

     
    1. S M S+M S*M  
    2. ---------- ---------- ---------- ----------  
    3. 8 15 23 120  
    4.  

    来源:http://database.51cto.com/art/201010/231528.htm

     

    Starting in Oracle9i release 2 we see an incorporation of the SQL-99 "WITH clause", a tool for materializing subqueries to save Oracle from having to re-compute them multiple times.

    The SQL "WITH clause" is very similar to the use of Global temporary tables (GTT), a technique that is often used to improve query speed for complex subqueries. Here are some important notes about the Oracle "WITH clause":

       • The SQL "WITH clause" only works on Oracle 9i release 2 and beyond.
       • Formally, the "WITH clause" is called subquery factoring
       • The SQL "WITH clause" is used when a subquery is executed multiple times
       • Also useful for recursive queries (SQL-99, but not Oracle SQL)

    To keep it simple, the following example only references the aggregations once, where the SQL "WITH clause" is normally used when an aggregation is referenced multiple times in a query.

    We can also use the SQL-99 "WITH clause" instead of temporary tables. The Oracle SQL "WITH clause" will compute the aggregation once, give it a name, and allow us to reference it (maybe multiple times), later in the query.

    The SQL-99 "WITH clause" is very confusing at first because the SQL statement does not begin with the word SELECT. Instead, we use the "WITH clause" to start our SQL query, defining the aggregations, which can then be named in the main query as if they were "real" tables:

    WITH
    subquery_name
    AS
    (the aggregation SQL statement)
    SELECT
    (query naming subquery_name);


    Retuning to our oversimplified example, let's replace the temporary tables with the SQL "WITH  clause" (Note:  You may find a faster execution plan by using Global Temporary tables, depending on your release of Oracle):

    WITH sum_sales AS
      ( select /*+ materialize */  sum(quantity) all_sales from stores ),
    number_stores AS 

    ( select /*+ materialize */  count(*) nbr_stores from stores ),
    sales_by_store AS
      ( select /*+ materialize */   store_name, sum(quantity) store_sales from  store natural join sales )
    SELECT   store_name
    FROM   store,   sum_sales,   number_stores,   sales_by_store
    where   store_sales > (all_sales / nbr_stores);


    Note the use of the Oracle undocumented "materialize" hint in the "WITH clause". The Oracle materialize hint is used to ensure that the Oracle cost-based optimizer materializes the temporary tables that are created inside the "WITH" clause. This is not necessary in Oracle10g, but it helps ensure that the tables are only created one time.

    It should be noted that the "WITH clause" does not yet fully-functional within Oracle SQL and it does not yet support the use of "WITH clause" replacement for "CONNECT BY" when performing recursive queries.

    To see how the "WITH clause" is used in ANSI SQL-99 syntax, here is an excerpt from Jonathan Gennick's great work "Understanding the WITH Clause" showing the use of the SQL-99 "WITH clause" to traverse a recursive bill-of-materials hierarchy

    The SQL-99 "WITH clause" is very confusing at first because the SQL statement does not begin with the word SELECT. Instead, we use the "WITH clause" to start our SQL query, defining the aggregations, which can then be named in the main query as if they were "real" tables:

    WITH
    subquery_name
    AS
    (the aggregation SQL statement)
    SELECT
    (query naming subquery_name);


    Retuning to our oversimplified example, let's replace the temporary tables with the SQL "WITH" clause":

    Link: http://www.dba-oracle.com/t_with_clause.htm


    Improving Query Performance with the SQL WITH Clause

     

     

    Oracle9i significantly enhances both the functionality and performance of SQL to address the requirements of business intelligence queries. The SELECT statement's WITH clause, introduced in Oracle9i, provides powerful new syntax for enhancing query performance. It optimizes query speed by eliminating redundant processing in complex queries.

    Consider a lengthy query which has multiple references to a single subquery block. Processing subquery blocks can be costly, so recomputing a block every time it is referenced in the SELECT statement is highly inefficient. The WITH clause enables a SELECT statement to define the subquery block at the start of the query, process the block just once, label the results, and then refer to the results multiple times.

    The WITH clause, formally known as the subquery factoring clause, is part of the SQL-99 standard. The clause precedes the SELECT statement of a query and starts with the keyword "WITH." The WITH is followed by the subquery definition and a label for the result set. The query below shows a basic example of the clause:

    WITH channel_summary AS
      ( SELECT channels.channel_desc,
           SUM(amount_sold) AS channel_total
        FROM sales, channels
        WHERE sales.channel_id = channels.channel_id
        GROUP BY channels.channel_desc )
    SELECT channel_desc, channel_total
    FROM channel_summary
    WHERE channel_total >
      ( SELECT SUM(channel_total) * 1/3
        FROM channel_summary );

    This query uses the WITH clause to calculate the sum of sales for each sales channel and label the results as channel_summary. Then it checks each channel's sales total to see if any channel's sales are greater than one third of the total sales. By using the new clause, the channel_summary data is calculated just once, avoiding an extra scan through the large sales table.

    Although the primary purpose of the WITH clause is performance improvement, it also makes queries easier to read, write and maintain. Rather than duplicating a large block repeatedly through a SELECT statement, the block is localized at the very start of the query. Note that the clause can define multiple subquery blocks at the start of a SELECT statement: when several blocks are defined at the start, the query text is greatly simplified and its speed vastly improved.

    The SQL WITH clause in Oracle9i significantly improves performance for complex business intelligence queries. Together with the many other SQL enhancements in Oracle9i, the WITH clause extends Oracle's leadership in business intelligence.

    来源:http://www.eygle.com/digest/2009/04/oracle_with_clause.html

     

    展开全文
  • with as的用法

    2011-03-11 09:57:00
    WITH是临时的结果集的命名跟AS搭配使用,称为公用表表达式 (CTE): <br /> WITH a(n,b) AS (SELECT [name],balance FROM bankaccounts) SELECT * FROM a;  
    WITH是临时的结果集的命名跟AS搭配使用,称为公用表表达式 (CTE):

    WITH a(n,b) AS (SELECT [name],balance FROM bankaccounts)
    SELECT * FROM a;

     

    展开全文
  • 临时表 with tableName的用法

    千次阅读 2018-03-22 13:39:09
    有时候在Oracle查询时需要建立临时表...用法with tableName as(sql语句) select * from tableName...例如:[sql] view plain copy print?with a as --建立临时表 (select sum(pt.c_all) sumall, q.c_...
    

    有时候在Oracle查询时需要建立临时表,可以简化查询,使读者更加清楚明白,在一个查询sql中,可以使用临时表。

    用法:with tableName as(sql语句)  select * from tableName...

    例如:

    1. with a as   --建立临时表  
    2.  (select sum(pt.c_all) sumall, q.c_createsupp  
    3.     from t_quotebill_part pt  
    4.    inner join t_quotebill q  
    5.       on pt.c_quotebill = q.c_id  
    6.    where q.c_enquirebill = 7741809513  
    7.    group by q.c_createsupp)  
    8. select c_createsupp suppcode --使用临时表  
    9.   from a  
    10.  where a.sumall in (select min(sumall) from a)
    展开全文
  • [转]Getting Started with Hadoop, Part 1

    千次阅读 2007-09-09 17:56:00
    Getting Started with Hadoop, Part 1作者:Richard来源:http://huang.yunsong.net/2006/getting_started_with_hadoop_part1.html我一直都对分布式文件系统非常感兴趣,特别喜欢研究如何在庞大的廉价的异构系统集群...
  • ffmpeg中sws_scale()用法实例 视频编码 2009-06-30 10:02:27 阅读1185 评论0 字号:大中小 订阅 /*  * Copyright (C) 2003 Michael Niedermayer michaelni@gmx.at>  *  * This file is part of...
  • 《PCL点云库学习&VS2010(X64)》Part 34 旋转平移矩阵用法 1、变换与投影矩阵讲解: https://en.wikipedia.org/wiki/Transformation_matrix http://web.iitd.ac.in/~hegde/cad/lecture/L6_3dtrans.pdf 点云变换主要...
  • LINQ to SQL系列Part 9-Using a Custom LINQ Expression with the control本文英文原版:...
  • systemd用法

    千次阅读 2018-01-09 16:00:25
    part (e.g.  name@.service ).  string  is called the  instance identifier , and is similar to an argument that is passed to the template unit when called with the  systemctl  command: in the ...
  • in 和with用法 in后接衣服,也可接颜色,可作定语或和be连用作谓语。 The man in blue is our teacher. 蓝衣服的那个人是我的老师。 with表示穿戴,只能作定语,而且只能和眼镜、手套等连用,不能接衣服。 ...
  • go errgroup 用法示例

    千次阅读 2017-12-16 12:15:51
    // A Group is a collection of goroutines working on subtasks that are part of // the same overall task. // // A zero Group is valid and does not cancel on error. type Group struct { cancel...
  • wait调用中的status变量值用于检查子进程是否已经退出,这是检测子进程是被ptrace暂停还是退出的典型用法。想知道WIFEXITED宏的细节,请查看man手册中的wait(2)。 读取寄存器的值 如果想在系统调用的入口或...
  • authconfig 用法

    千次阅读 2017-04-24 16:04:33
    linux 下authconfig命令用法
  • sfdisk 用法

    千次阅读 2015-04-14 15:29:51
    sfdisk -f $device >& /dev/null $part1_offset,$part1_size,83,* //第一分区:从$part1_offset开始,$part1_size个柱面,LINUX_NATIVE (83)类型,启动分区 ,$part2_size,L,- //第二分区:接着上一个
  • Sqoop用法

    千次阅读 2015-06-10 08:46:08
    Sqoop的用法 1. 系统配置 电脑1(Lenovo E420),win7 64位系统,8G内存,此电脑虚拟机上运行两个ubuntu14.10系统。 电脑2(Lenovo SL410),win7 64位系统,4G内存,此电脑虚拟机上运行一个ubuntu14.10系统。...
  • MultipleInputs用法

    千次阅读 2015-04-16 16:07:40
    15/04/16 16:06:08 INFO mapred.Merger: Down to the last merge-pass, with 2 segments left of total size: 127 bytes 15/04/16 16:06:08 INFO mapred.LocalJobRunner: 15/04/16 16:06:09 INFO mapred.Task: Task...
  • 最近在工作中使用到了scrollTo与scrollBy,因此在这准备对它们的用法以及TouchSlop与VelocityTracker做一下整理与总结,以便加深理解,以下是本篇的主要内容,至于Scroller类的解析以及用法,我会放在下一篇文件记录...
  • Retrofit用法详解

    万次阅读 多人点赞 2016-08-06 23:11:33
    // 上传多个文件 @Multipart @POST("upload") Call uploadMultipleFiles( @Part("description") RequestBody description, @Part MultipartBody.Part file1, @Part MultipartBody.Part file2); } 接下来我们还需要...
  • for each 的用法

    万次阅读 2018-03-31 11:44:43
    } Java 中新增的 for each 的用法JDK1.5加入的增强for和循环.for each语句使用总结增强for(part1:part2){part3}; part2中是一个数组对象,或者是带有泛性的集合. part1定义了一个局部变量,这个局部变量的类型与p.....
  • ActivityInstrumentationTestCase用法

    千次阅读 2013-03-24 20:04:36
    Part1: http://schemas.android.com/apk/res/android"  package="com.example.hellojni.tests"  android:versionCode="1"  android:versionName="1.0">  <!-- We add an app
  • 英语介词用法

    千次阅读 2015-08-18 20:31:50
    (2)用小刀(with a knife);(3)用手工(by hand);(4)用墨水(in ink)等。所以,千万不要以为记住介词的一两种意思就掌握了这个介词的用法,其实介词的用法非常广泛,搭配能力很强,越是常用的介词,其含义...
  • sgdisk基本用法

    万次阅读 2017-07-28 17:09:55
    sgdisk基本用法简介sgdisk是Linux下操作GPT分区的工具,就像fdisk是操作MBR分区的工具。关于GPT和MBR的区别请参考: ...
  • 增强for(part1:part2){part3}; part2中是一个数组对象,或者是带有泛性的集合. part1定义了一个局部变量,这个局部变量的类型与part2中的对象元素的类型是一致的. part3当然还是循环体. foreach语句是java5的新特征之...
  • FND_MESSAGE的用法

    千次阅读 2012-04-24 10:46:23
    关于FND_MESSAGE的详细用法可以参见 http://docs.oracle.com/cd/E18727_01/doc.121/e12897/T302934T462354.htm 这里只列出几个常用的Example,方便查用 FND_MESSAGE.SET_NAME Retrieves your message from ...
  • strncpy用法

    千次阅读 2017-01-05 22:56:54
    (2)正确用法如下:先memset 0 整个数组,strncpy传入的长度为目标数组最大长度减一。(拷贝正确) #include #include #include #include int main() { char src[20]; char des[5]; memset(des,0,...
  • sqlmap用法

    万次阅读 2016-07-28 09:38:25
    Sqlamp Usage https://github.com/sqlmapproject/sqlmap/wiki/Usage Usage: python sqlmap.py [options] Options: -h, --help Show basic help message and exit -hh Show ad
  • datetimepicker用法

    万次阅读 2016-05-04 08:38:27
    Added views to select the time part . 依赖 需要bootstrap的下拉菜单组件 ( dropdowns.less ) 的某些样式,还有bootstrap的sprites ( sprites.less  and associated images) 中的箭头图标。...
  • BLAST用法

    千次阅读 2016-08-31 18:47:49
    7 = tabular with comment lines, 8 = Text ASN.1, 9 = Binary ASN.1 10 = Comma-separated values Options 6, 7, and 10 can be additionally configured to produce a custom format specified by ...
  • python print(%用法和format用法

    千次阅读 多人点赞 2019-11-16 10:16:41
    % 用法 1.整数 %o —— oct 八进制 %d —— dec 十进制 %x —— hex 十六进制 print('%o' % 20) 24 print('%d' % 20) 20 print('%x' % 20) 14 2.浮点数 %f ——保留小数点后面六位有效数字 %.3f,保留3位小数...
  • SPGridView 用法范例

    千次阅读 2008-08-31 15:12:00
    以前探討過SPGridView的用法 , 不過資料不見了 , 轉貼 powlo 的SPGridView用法給大家參考Click along to build this lovely Web Part, taking data directly from a standard .NET DataSet: SharePoint lists and ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 22,397
精华内容 8,958
关键字:

partwith的用法