sql复合查询

A. 如何把sql Server多行查询结果合并在一行中

如果你要取的值是AdminName, 表应该还有一个标识来做复合组键, 就是最最终想要结果戚伏卖的col title(admin1, admin2).

假如结构厅瞎如下高逗:

B. sqlite查询怎么优化

SQLite是个典型的嵌入式DBMS，它有很多优点，它是轻量级的，在编译之后很小，其中一个原因就是在查询优化方面比较简单，它只是运用索引机制来进行优化的，经过对SQLite的查询优化的分析以及对源代码的研究，我将SQLite的查询优总结如下：
一、影响查询性能的因素：
1． 对表中行的检索数目，越小越好
2． 排序与否。
3． 是否要对一个索引。
4． 查询语句的形式
二、几个查询优化的转换
1． 对于单个表的单个列而言，如果都有形如T.C=expr这样的子句，并且都是用OR操作符连接起来，形如： x = expr1 OR expr2 = x OR x = expr3 此时由于对于OR，在SQLite中不能利用索引来优化，所以可以将它转换成带有IN操作符的子句：x IN(expr1,expr2,expr3)这样就可以用索引进行优化，效果很明显，但是如果在都没有索引的情况下OR语句执行效率会稍优于IN语句的效率。
2． 如果一个子句的操作符是BETWEEN，在SQLite中同样不能用索引进行优化，所以也要进行相应的等价转换： 如：a BETWEEN b AND c可以转换成：(a BETWEEN b AND c) AND (a>=b) AND (a<=c)。 在上面这个子句中， (a>=b) AND (a<=c)将被设为dynamic且是(a BETWEEN b AND c)的子句，那么如果BETWEEN语句已经编码，那么子句就忽略不计，如果存在可利用的index使得子句已经满足条件，那么父句则被忽略。
3． 如果一个单元的操作符是LIKE，那么将做下面的转换：x LIKE ‘abc%’，转换成：x>=‘abc’ AND x<‘abd’。因为在SQLite中的LIKE是不能用索引进行优化的，所以如果存在索引的话，则转换后和不转换相差很远，因为对LIKE不起作用，但如果不存在索引，那么LIKE在效率方面也还是比不上转换后的效率的。
三、 几种查询语句的处理（复合查询）
1．查询语句为：<SelectA> <operator> <selectB> ORDER BY <orderbylist> ORDER BY
执行方法： is one of UNION ALL, UNION, EXCEPT, or INTERSECT. 这个语句的执行过程是先将selectA和selectB执行并且排序，再对两个结果扫描处理，对上面四种操作是不同的，将执行过程分成七个子过程：
outA: 将selectA的结果的一行放到最终结果集中
outB: 将selectA的结果的一行放到最终结果集中(只有UNION操作和UNION ALL操作，其它操作都不放入最终结果集中)
AltB: 当selectA的当前记录小于selectB的当前记录
AeqB: 当selectA的当前记录等于selectB的当前记录
AgtB: 当selectA的当前记录大于selectB的当前记录
EofA: 当selectA的结果遍历完
EofB: 当selectB的结果遍历完
下面就是四种操作的执行过程：
执行顺序

UNION ALL

UNION

EXCEPT

INTERSECT

AltB:

outA, nextA

outA, nextA

outA,nextA

nextA

AeqB:

outA, nextA

nextA

nextA

outA, nextA

AgtB:

outB, nextB

outB, nextB

nextB

nextB

EofA:

outB, nextB

outB, nextB

halt

halt

EofB:

outA, nextA

outA, nextA

outA,nextA

halt

2． 如果可能的话，可以把一个用到GROUP BY查询的语句转换成DISTINCT语句来查询，因为GROUP BY有时候可能会用到index，而对于DISTINCT都不会用到索引的 。
四、子查询扁平化
例子：SELECT a FROM (SELECT x+y AS a FROM t1 WHERE z<100) WHERE a>5
对这个SQL语句的执行一般默认的方法就是先执行内查询，把结果放到一个临时表中，再对这个表进行外部查询，这就要对数据处理两次，另外这个临时表没有索引，所以对外部查询就不能进行优化了，如果对上面的SQL进行处理后可以得到如下SQL语句：SELECT x+y AS a FROM t1 WHERE z<100 AND a>5，这个结果显然和上面的一样，但此时只需要对
数据进行查询一次就够了，另外如果在表t1上有索引的话就避免了遍历整个表。
运用flatten方法优化SQL的条件：
1.子查询和外查询没有都用集函数
2.子查询没有用集函数或者外查询不是个表的连接
3.子查询不是一个左外连接的右操作数
4.子查询没有用DISTINCT或者外查询不是个表的连接
5.子查询没有用DISTINCT或者外查询没有用集函数
6.子查询没有用集函数或者外查询没有用关键字DISTINCT
7.子查询有一个FROM语句
8.子查询没有用LIMIT或者外查询不是表的连接
9.子查询没有用LIMIT或者外查询没有用集函数
10.子查询没有用集函数或者外查询没用LIMIT
11.子查询和外查询不是同时是ORDER BY子句
12.子查询和外查询没有都用LIMIT
13.子查询没有用OFFSET
14.外查询不是一个复合查询的一部分或者子查询没有同时用关键字ORDER BY和LIMIT
15.外查询没有用集函数子查询不包含ORDER BY
16.复合子查询的扁平化：子查询不是一个复合查询，或者他是一个UNION ALL复合查询，但他是都由若干个非集函数的查询构成，他的父查询不是一个复合查询的子查询，也没有用集函数或者是DISTINCT查询，并且在FROM语句中没有其它的表或者子查询，父查询和子查询可能会包含WHERE语句，这些都会受到上面11、12、13条件的限制。
例： SELECT a+1 FROM (
SELECT x FROM tab
UNION ALL
SELECT y FROM tab
UNION ALL
SELECT abs(z*2) FROM tab2
) WHERE a!=5 ORDER BY 1
转换为：
SELECT x+1 FROM tab WHERE x+1!=5
UNION ALL
SELECT y+1 FROM tab WHERE y+1!=5
UNION ALL
SELECT abs(z*2)+1 FROM tab2 WHERE abs(z*2)+1!=5
ORDER BY 1
17.如果子查询是一个复合查询，那么父查询的所有的ORDER BY语句必须是对子查询的列的简单引用
18.子查询没有用LIMIT或者外查询不具有WHERE语句
子查询扁平化是由专门一个函数实现的，函数为：
static int flattenSubquery(
Parse *pParse, /* Parsing context */
Select *p, /* The parent or outer SELECT statement */
int iFrom, /* Index in p->pSrc->a[] of the inner subquery */
int isAgg, /* True if outer SELECT uses aggregate functions */
int subqueryIsAgg /* True if the subquery uses aggregate functions */
)
它是在Select.c文件中实现的。显然对于一个比较复杂的查询，如果满足上面的条件时对这个查询语句进行扁平化处理后就可以实现对查询的优化。如果正好存在索引的话效果会更好！

五、连接查询
在返回查询结果之前，相关表的每行必须都已经连接起来，在SQLite中，这是用嵌套循环实现的，在早期版本中，最左边的是最外层循环，最右边的是最内层循环，连接两个或者更多的表时，如果有索引则放到内层循环中，也就是放到FROM最后面，因为对于前面选中的每行，找后面与之对应的行时，如果有索引则会很快，如果没有则要遍历整个表，这样效率就很低，但在新版本中，这个优化已经实现。
优化的方法如下：
对要查询的每个表，统计这个表上的索引信息，首先将代价赋值为SQLITE_BIG_DBL（一个系统已经定义的常量）：
1) 如果没有索引，则找有没有在这个表上对rowid的查询条件：
1．如果有Rowid=EXPR，如果有的话则返回对这个表代价估计，代价计为零，查询得到的记录数为1，并完成对这个表的代价估计，
2．如果没有Rowid=EXPR 但有rowid IN (...)，而IN是一个列表，那么记录返回记录数为IN列表中元素的个数，估计代价为NlogN,
3．如果IN不是一个列表而是一个子查询结果，那么由于具体这个子查询不能确定，所以只能估计一个值，返回记录数为100，代价为200。
4．如果对rowid是范围的查询，那么就估计所有符合条件的记录是总记录的三分之一，总记录估计为1000000，并且估计代价也为记录数。
5．如果这个查询还要求排序，则再另外加上排序的代价NlogN
6．如果此时得到的代价小于总代价，那么就更新总代价，否则不更新。
2) 如果WHERE子句中存在OR操作符，那么要把这些OR连接的所有子句分开再进行分析。
1． 如果有子句是由AND连接符构成，那么再把由AND连接的子句再分别分析。
2． 如果连接的子句的形式是X<op><expr>，那么就再分析这个子句。
3． 接下来就是把整个对OR操作的总代价计算出来。
4． 如果这个查询要求排序，则再在上面总代价上再乘上排序代价NlogN
5． 如果此时得到的代价小于总代价，那么就更新总代价，否则不更新。
3) 如果有索引，则统计每个表的索引信息，对于每个索引：
1． 先找到这个索引对应的列号，再找到对应的能用到（操作符必须为=或者是IN（…））这个索引的WHERE子句，如果没有找到，则退出对每个索引的循环，如果找到，则判断这个子句的操作符是什么，如果是=，那么没有附加的代价，如果是IN（sub-select），那么估计它附加代价inMultiplier为25，如果是IN（list），那么附加代价就是N（N为list的列数）。
2． 再计算总的代价和总的查询结果记录数和代价。
3． nRow = pProbe->aiRowEst[i] * inMultiplier;/*计算行数*/
4． cost = nRow * estLog(inMultiplier);/*统计代价*/
5． 如果找不到操作符为=或者是IN（…）的子句，而是范围的查询，那么同样只好估计查询结果记录数为nRow/3，估计代价为cost/3。
6． 同样，如果此查询要求排序的话，再在上面的总代价上加上NlogN
7． 如果此时得到的代价小于总代价，那么就更新总代价，否则不更新。
4) 通过上面的优化过程，可以得到对一个表查询的总代价（就是上面各个代价的总和），再对第二个表进行同样的操作，这样如此直到把FROM子句中所有的表都计算出各自的代价，最后取最小的，这将作为嵌套循环的最内层，依次可以得到整个嵌套循环的嵌套顺序，此时正是最优的，达到了优化的目的。
5) 所以循环的嵌套顺序不一定是与FROM子句中的顺序一致，因为在执行过程中会用索引优化来重新排列顺序。
六、索引
在SQLite中，有以下几种索引：
1) 单列索引
2) 多列索引
3) 唯一性索引
4) 对于声明为：INTEGER PRIMARY KEY的主键来说，这列会按默认方式排序，所以虽然在数据字典中没有对它生成索引，但它的功能就像个索引。所以如果在这个主键上在单独建立索引的话，这样既浪费空间也没有任何好处。
运用索引的注意事项：
1) 对于一个很小的表来说没必要建立索引
2) 在一个表上如果经常做的是插入更新操作，那么就要节制使用索引
3) 也不要在一个表上建立太多的索引，如果建立太多的话那么在查询的时候SQLite可能不会选择最好的来执行查询，一个解决办法就是建立聚蔟索引
索引的运用时机：
1) 操作符：=、>、<、IN等
2) 操作符BETWEEN、LIKE、OR不能用索引，
如BETWEEN：SELECT * FROM mytable WHERE myfield BETWEEN 10 and 20;
这时就应该将其转换成：
SELECT * FROM mytable WHERE myfield >= 10 AND myfield <= 20;
此时如果在myfield上有索引的话就可以用了，大大提高速度

再如LIKE：SELECT * FROM mytable WHERE myfield LIKE 'sql%';
此时应该将它转换成：
SELECT * FROM mytable WHERE myfield >= 'sql' AND myfield < 'sqm';
此时如果在myfield上有索引的话就可以用了，大大提高速度

再如OR：SELECT * FROM mytable WHERE myfield = 'abc' OR myfield = 'xyz';
此时应该将它转换成：
SELECT * FROM mytable WHERE myfield IN ('abc', 'xyz');
此时如果在myfield上有索引的话就可以用了，大大提高速度
3) 有些时候索引都是不能用的，这时就应该遍历全表（程序演示）
SELECT * FROM mytable WHERE myfield % 2 = 1;
SELECT * FROM mytable WHERE substr(myfield, 0, 1) = 'w';
SELECT * FROM mytable WHERE length(myfield) < 5;

C. sql server 2008 like语句正则表达式查询问题

SELECT *
FROM tablename
WHERE TelNum LIKE '13%'
AND ISNUMERIC(TelNum)=0
AND LEN(TelNum)=11

-------------------------
--正则的

SELECT *
FROM tablename
WHERE TelNum LIKE '13%[d{9}$]'

这个我不确定，只是能过滤到非数字的，我估计我尝试的你应该都试过了

D. SQL server 中子查询的作用是什么

子查询的作用：x0dx0a1、方便理解。x0dx0a2、实现更复杂的查询。x0dx0a3、提高查询效率。x0dx0ax0dx0a扩展知识：x0dx0a1、当直接从SQL的数据库中查询（列出）某些数据很困难或办不到时，可以通过从“查询结果集”中再次提取数据集来实现复合查询。这个“查询结果集”就被叫做子查询。x0dx0a2、例，当表1与表2中以ID1关联，表2与表3以ID2关联，要以表1为基准复合查询三表中的数据时，就需要先查出表2与表3的子查询集，然后再与表1进行多表联查。

E. 一条复杂的SQL语句，为什么第一次查询很慢，而第二次查询却明显快了

第一次SQL要先编译，从磁盘读入缓存，再返回客户端。

第二次SQL不用编译，直接运行，会省时间，最省时间的是不用再读入缓存及查找了，直接返回客户端。

F. 怎么用sql语句在已有表上设置复合主键

例如：x0dx0a已有一个表test_key，其中a1列为主键。

在数据库提供的GUI环境中建立（以SQL7为例）。输入表信息后按Ctrl键同时选中多行，然后点上面的主键按钮就行了。通过SQL语句执行建立。又分两种，一是在建表语句中直接写，二是建表之后更改表结构。

CREATETABLE[UserRole](aINTNOTNULL，bINTNOTNULL，cINTNOTNULL，PRIMARYKEY(a，b))；这是在查询分析器里运行的。如果手工建表的话，只需按住Ctrl键同时选择两个字段就可以设置复合主键了。

首先一个表是不能有两个主键的。但是可以有两个字段组合成一个主键，这就是为什么有时候表里为什么会有两个字段都有主键的标志，那是因为他们组合成了一个主键了。

G. sql server 2000的查询方式有哪些

这个?
sql查询方式总结

简单查询
1，检索表中所有列
select * from <表名>
2，检索表中特定列
select <列名表列> from <表名>
3，重排列顺序
select cloumn_3,cloumn_1 from <表名>
4，用单引号加入字符串
select cloumn_3,cloumn_1 '所要加的字段名' from<表名>
5，改变列标题
select cloumn_3,cloumn_1 as '新列名' from <表名>
6，消除重复行
select distinct cloumn_3 from <表名>
7，返回有限的结果
select top n [percent]* from <表名>
8，使用算数运算符操纵列
example:
select cloumn_1+'('+cloumn_2+')' [as] '新列名'，cloumn_3*cloumn_4 '新列名' from <表名>
9，使用函数操纵列
--常用函数有substring()[取子字符串]、getdate()[返回当前日期和时间]、year(data)[返回日期中的年份]、convert()[类型转换]
example:
select distinct substring(title,1,5) from pubs
又如:返回当前服务器的用户名
select 'login'=suser_sname()
再如查询pubs数据库的titles表中所有书的价格信息，对于书名长度超过38个字符的，将其余部分截掉
use pubs
select convert(char(38),title)+'IS $'+ convert(varchar(10),price) from titles

选择行查询
1，基于比较选择行
example:
select 价格,* from goods where 价格>=1000
2,基于范围选择行
select * from goods where 价格 between 20 and 2000
--3,基于列表选择行
select * from goods where 品名 not in('方正A','联想')
4,基于字符串选择行
--百分号(%):代表任意多个字符
--下划线(_):代表单个字符
--中括号([]):代表指定范围内的单个字符
--复合符号([^]):代表不在指定范围内的单个字符
--5,基于未知选择行
--使用IS NULL IS not NULL
select 姓名,职业 from students where 联系电话 IS null
--6，基于多个搜索条件选择行
select 姓名,职业,联系电话,地址 from students
where (联系电话 like '%3%'or 联系电话 like '%5%')
and (地址 like '河南')
--7，结果排序
select * from goods
select 价格 from goods
order by 价格 ASC

生成总结查询
使用SQL查询语言，用户可以方便地为查询到的结果进行分组、计算或对其进行过滤等，从而得到总结性的数据结果集。
1，使用集合函数进行统计
(1)AVG 该函数用于计算查询结果的平均值。其语法格式如下:
AVG([all | distinct] 算数表达式) 其中各参数的意义如下:
all:全部数据都参与计算，为默认的选项。
distinct:消除重复的数据，只计算不同的值的平均值。
算数表达式:该表达式即可以只涉及一列，也可涉及多列
example:要计算Sales数据库中Goods表中的所有货品的平均价格
use Sales
select avg(价格) from goods
(2)min、max 这两个函数用于查询最小值和最大值，语法格式如下:
min([all | distinct] 算数表达式)
(3)sum 该函数用于计算查询到的数据值得总和
example:计算Pubs数据库中的titles表中所有商业类书籍的平均预付款和本年度迄今为止的全部销售额。
use pubs
select avg(advance),sum(ytd_sales) from titles where type='business'
(4)count 该函数可以计算查询到的结果的数目
example:查询sales数据库中customers表中客户地址的数量。
use sales
select count(distinct 地址) from customers
2，数据分组
(1)group by子句
在SQL Sever中，可以按照一定的条件对查询到的结果进行分组，然后对每一组数据计算统计信息。
说明:1,使用group by 子句能按列或表达式分组，一般与集合函数一起使用，每组差生一个值。
2,在包含group by子句的查询语句中，select语句后的所有字段列表，除集合函数外，都应该包含 在group by子句中，否则会出错。
3，对数据进行分组时，也可以用条件进行选择，这就是having子句。
(2)having子句
having子句可以用来向使用Group by子句的查询中添加过滤准则，即检查分组之后的各组是否满足条件。having子句语法与where子句一样，但两者之间还是存在一定区别，具体如下:
1,where子句的作用是检查每条记录是否满足条件，而having子句是检查分组之后的各组是否满足条件。
2,having子句是针对Group by子句的，没有Group by子句时不能使用having子句。
3,having子句可以在条件中包含集合函数，但where子句不行
exam:显示sales数据库中Goods表中存货量大于70的货品及库存数量，具体代码如下:
use sales
----select 品名,库存数量 from Goods where 库存数量>70
select 品名,库存数量=sum(库存量) from Goods group by 品名 having sum(库存量)>7
①where子句排出不满足条件的行
②group by子句收集满足where子句的搜索行，并将这些行分组
③having子句排出不符合其条件的组
此外，使用group by all能够显示所有的组，即便是被where子句排除的组也将会显示出来
3，计算汇总数据
(1)compute子句
可以使用该子句计算汇总数据，该汇总数据将作为附加的汇总列出现在结果集的最后
exam:在pubs数据库的titles表中查询类型是以cook结尾的书的价格和总价
use pubs
select type,price from titles where type like '%cook'
order by type,price
compute sum(price)
(2)compute by 子句
在查询中使用该子句可以按给定的条件将查询结果分组，并为每种结果计算汇总数据。
exam:在pubs数据库的titles表中查询类型是以cook结尾的书的价格和每种类型的总价
use pubs
select type,price from titles where type like '%cook'
order by type,price
compute sum(price) by type
(3)计算汇总数据时的注意事项
在使用compute子句和compute by 子句时,应注意下面的限制条件:
1,关键字distinct不允许同集合函数一起使用
2,compute子句中的列必须在select后面的选择列表
3,select into 不能和compute子句一块使用
4,若使用compute by子句，则必须使用order by子句。此时，compute by子句后出现的列必须与order by后出现的列相同，最少也应该是其子集，而且必须具有相同的从左到右的顺序并且以相同的表达式开头，不能跳过任何表达式。

http://hi..com/wmaple/blog/item/6de746d0d3ba708aa1ec9c1b.html

H. 列举一些sql高级查询语句

1.集合操作
学习oracle中集合操作的有关语句，掌握union,union all,minus,interest的使用,能够描述结合运算，并且能够将多个查询组合到一个查询中去，能够控制行返回的顺序。
包含集合运算的查询称为复合查询。见表格1-1
表1-1
Operator Returns content
UNION 由每个查询选择的所有不重复的行 并集不包含重复值
UNION ALL 由每个查询选择的所有的行，包括所有重复的行 完全并集包含重复值
INTERSECT 由每个查询选择的所有不重复的相交行 交集
MINUS 在第一个查询中，不在后面查询中，并且结果行不重复 差集

所有的集合运算与等号的优先级相同，如果SQL语句包含多个集合运算并且没有圆括号明确地指定另一个顺序，Oracle服务器将以从左到右的顺序计算。你应该使用圆括号来明确地指定带另外的集合运算的INTERSECT (相交) 运算查询中的赋值顺序。
Union all 效率一般比union高。
1.1.union和union all
UNION(联合)运算
UNION运算返回所有由任一查询选择的行。用UNION运算从多表返回所有行，但除去任何重复的行。
原则 :

􀂃?被选择的列数和列的数据类型必须是与所有用在查询中的SELECT语句一致。列的名字不必相同。
􀂃?联合运算在所有被选择的列上进行。
􀂃?在做重复检查的时候不忽略空(NULL)值。
􀂃?IN运算有比UNION运算高的优先级。
􀂃?在默认情况下，输出以SELECT子句的第一列的升序排序。

全联合(UNION ALL)运算
用全联合运算从多个查询中返回所有行。
原则

􀂃?和联合不同，重复的行不被过滤，并且默认情况下输出不排序。
􀂃?不能使用DISTINCT关键字。
使用：
Select statement union | union all Select statement;

1.2.intersect交集操作
相交运算
用相交运算返回多个查询中所有的公共行。 无重复行。
原则

􀂃?在查询中被 SELECT 语句选择的列数和数据类型必须与在查询中所使用的所有的 SELTCT 语句中的一样，但列的名字不必一样。
􀂃?相交的表的倒序排序不改变结果。
􀂃?相交不忽略空值。
使用：
Select statement intersect all Select statement;

1.3. minus差集操作
相减运算
用相减运算返回由第一个查询返回的行，那些行不出现在第二个查询中 (第一个SELECT语句减第二个SELECT语句)。
原则

􀂃?在查询中被SELECT语句选择的列数和数据类型必须与在查询中所使用的所有的SELTCT语句中的一样，但列的名字不必一样。
􀂃?对于MINUS运算，在WHERE子句中所有的列都必须在SELECT子句中。

集合运算的原则
?在两个SELECT列表中的表达式必须在数目上和数据类型上相匹配
?可以用圆括号改变执行的顺序
?ORDER BY子句：–只能出现在语句的最后–从第一个SELECT语句接收列名、别名，或者位置记号

注：?除了UNION ALL，重复行自动被清除
?在结果中的列名是第一个查询中出现的列名
?除了UNION ALL，默认情况下按升序顺序输出
2.exists和not exists的使用
2.1. exists的使用
Exists用于只能用于子查询，可以替代in，若匹配到结果，则退出内部查询，并将条件标志为true，传回全部结果资料,in不管匹配到匹配不到都全部匹配完毕，使用exists可以将子查询结果定为常量，不影响查询效果，而且效率高。如查询所有销售部门员工的姓名，对比如下：
IN is often better if the results of the subquery are very small
When you write a query using the IN clause, you're telling the rule-based optimizer that you want the inner query to drive the outer query.
When you write EXISTS in a where clause, you're telling the optimizer that you want the outer query to be run first, using each value to fetch a value from the inner query.
In many cases, EXISTS is better because it requires you to specify a join condition, which can invoke an INDEX scan. However, IN is often better if the results of the subquery are very small. You usually want to run the query that returns the smaller set of results first.

In和exists对比：
若子查询结果集比较小，优先使用in，若外层查询比子查询小，优先使用exists。因为若用in，则oracle会优先查询子查询，然后匹配外层查询，若使用exists，则oracle会优先查询外层表，然后再与内层表匹配。最优化匹配原则，拿最小记录匹配大记录。
使用in
select last_name, title
from s_emp
where dept_id in
(select id
from s_dept
where name='Sales');

使用exists
select last_name,title
from s_emp e
where exists
(select 'x' --把查询结果定为constant，提高效率
from s_dept s where s.id=e.dept_id and s.name='Sales');
2.2 not exists的使用
与exists 含义相反，也在子查询中使用，用于替代not in。其他一样。如查询不在销售部的员工姓名
select last_name,title
from s_emp e
where not exists
(select 'x' --把查询结果定为constant，提高效率
from s_dept s where s.id=e.dept_id and s.name='Sales');
3.with子句
9i新增语法
1.使用with子句可以让子查询重用相同的with查询块,通过select调用，一般在with查询用到多次情况下。

2.with子句的返回结果存到用户的临时表空间中，只做一次查询，提高效率。

3.有多个查询的时候，第1个用with，后面的不用with，并且用逗号隔开。

5.最后一个with子句与下面的查询之间不能有逗号，只通过右括号分割,查询必须用括号括起来

6.如果定义了with子句，而在查询中不使用，那么会报ora-32035错误：未引用在with子句中定义的查询名。（至少一个with查询的name未被引用，解决方法是移除未被引用的with查询）

7.前面的with子句定义的查询在后面的with子句中可以使用。
With子句目的是为了重用查询。

语法：
With alias_name as (select1), --as和select中的括号都不能省略
alias_name2 as (select2),--后面的没有with，逗号分割
…
alias_namen as (select n) –与下面的查询之间没有逗号
Select ….
如查询销售部门员工的姓名：
--with clause
with a as
(select id from s_dept where name='Sales' order by id)
select last_name,title
from s_emp where dept_id in (select * from a);--使用select查询别名

使用with子句，可以在复杂的查询中预先定义好一个结果集，然后在查询中反复使用，不使用会报错。而且with子句获得的是一个临时表，如果在查询中使用，必须采用select from with查询名，比如
With cnt as(select count(*) from table)
Select cnt+1 from al;
是错误的。必须是
With cnt as(select count(*) shumu from user_tables)
Select shumu+1 from cnt;
--直接引用with子查询中的列别名。

一个with查询的实例：
查询出部门的总薪水大于所有部门平均总薪水的部门。部门表s_dept，员工表s_emp。
分析：做这个查询，首先必须计算出所有部门的总薪水，然后计算出总薪水的平均薪水，再筛选出部门的总薪水大于所有部门总薪水平均薪水的部门。那么第1步with查询查出所有部门的总薪水，第2步用with从第1步获得的结果表中查询出平均薪水，最后利用这两次的with查询比较总薪水大于平均薪水的结果，如下：
with
--step1:查询出部门名和部门的总薪水
dept_costs as(
select a.name,sum(b.salary) dept_total
from
s_dept a,s_emp b
where a.id=b.dept_id
group by a.name
),
--step2:利用上一个with查询的结果，计算部门的平均总薪水
avg_costs as(
select sum(dept_total)/count(*) dept_avg
from dept_costs
)
--step3:从两个with查询中比较并且输出查询结果
select name,dept_total
from dept_costs
where
dept_total>
(
select dept_avg
from
avg_costs
)
order by name;

从上面的查询可以看出，前面的with查询的结果可以被后面的with查询重用，并且对with查询的结果列支持别名的使用，在最终查询中必须要引用所有with查询，否则会报错ora-32035错误。

再如有这样一个需求：一个查询，如果查询的结果行不满足是10的倍数，则补空行，直到是查询出的行数是10的倍数。例如：select * from trademark这个查询。
with cnt as (select 10-mod(count(*),10) shumu from trademark) –查询比10的倍数差几个空行
select id,name
from trademark
union all --空行加进去
select null,null --补空行
from al connect by rownum<=(select shumu from cnt); --10个中connect by可以使用子查询
10g之前的写法
with cnt as (select 10-mod(count(*),10) shumu from trademark) –查询比10的倍数差几个空行
select id,name
from trademark
union all --空行加进去
select null,null --补空行
from all_objects where rownum<=(select shumu from cnt);--使用all_objects行比较多

4.merge into合并资料
语法：(其中as可以省略)
MERGE INTO table_name AS table_alias
USING (table|view|sub_query) AS alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
col1 = col_val1,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list)—多个列以逗号分割 //可以不指定列
VALUES (column_values);

作用：将源数据(来源于实际的表,视图,子查询)更新或插入到指定的表中(必须实际存在),依赖于on条件,好处是避免了多个insert和update操作。Merge是一个目标性明确的操作符,不允许在一个merge语句中对相同的行insert或update操作。这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT＋UPDATE。例子如下：

drop table t;
CREATE TABLE T AS SELECT ROWNUM ID, A.* from DBA_OBJECTS A;

drop table t1;
CREATE TABLE T1 AS
SELECT ROWNUM ID, OWNER, TABLE_NAME, CAST('TABLE' AS VARCHAR2(100)) OBJECT_TYPE
from DBA_TABLES;

select * from dba_objects;
select * from dba_tables;

MERGE INTO T1 USING T
ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME AND T.OBJECT_TYPE = T1.OBJECT_TYPE)
WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME, T.OBJECT_TYPE);--insert后面不写表示插入全部列

MERGE INTO T1 USING T
ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME)
WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME, T.OBJECT_TYPE);--常见错误，连接条件不能获得稳定的行，可以使用下面的用子查询

MERGE INTO T1
USING (SELECT OWNER, OBJECT_NAME, MAX(ID) ID from T GROUP BY OWNER, OBJECT_NAME) T
ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME)
WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME);

SELECT ID, OWNER, OBJECT_NAME, OBJECT_TYPE from T
MINUS
SELECT * from T1;

drop table subs;
create table subs(msid number(9),
ms_type char(1),
areacode number(3)
);

drop table acct;
create table acct(msid number(9),
bill_month number(6),
areacode number(3),
fee number(8,2) default 0.00);

insert into subs values(905310001,0,531);
insert into subs values(905320001,1,532);
insert into subs values(905330001,2,533);
commit;

merge into acct a --操作的表
using subs b on (a.msid=b.msid)--使用原始数据来源的表，并且制定条件，条件必须有括号
when matched then
update set a.areacode=b.areacode--当匹配的时候，执行update操作，和直接update的语法不一样，不需要制定表名
when not matched then--当不匹配的时候，执行insert操作，也不需要制定表名，若指定字段插入，则在insert后用括号标明，不指定是全部插入
insert(msid,bill_month,areacode) values(b.msid,'200702',b.areacode);

另外，MERGE语句的UPDATE不能修改用于连接的列，否则会报错
select * from acct;
select * from subs;
--10g新特性，单个操作
merge into acct a
using subs b on(a.msid=b.msid)
when not matched then--只有单个not matched的时候，只做插入，不做更新，只有单个matched的时候，只做更新操作
insert(a.msid,a.bill_month,a.areacode) values(b.msid,'200702',b.areacode);

update acct set areacode=800 where msid=905320001;

delete from acct where areacode=533 or areacode=531;

insert into acct values(905320001,'200702',800,0.00);

--删除重复行
delete from subs b where b.rowid<(
select max(a.rowid) from subs a where a.msid=b.msid and a.ms_type=b.ms_type and a.areacode=b.areacode);

--10g新特性，merge操作之后，只有匹配的update操作才可以，用delete where子句删除目标表中满足条件的行。
merge into acct a
using subs b on (a.msid=b.msid)
when MATCHED then
update set a.areacode=b.areacode
delete where (b.ms_type!=0)
when NOT MATCHED then
insert(msid,bill_month,areacode)
values(b.msid,'200702',b.areacode)
where b.ms_type=0;
--10g新特性，满足条件的插入和更新
merge into acct a
using subs b on (a.msid=b.msid)
when MATCHED then
update set a.areacode=b.areacode
where b.ms_type=0
when NOT MATCHED then
insert(msid,bill_month,areacode)
values(b.msid,'200702',b.areacode)
where b.ms_type=0;

select * from subs where ms_type=0;

I. sql复合查询语句

selectt0.itemcode,t0.quantity--,t1.*,t1.docstatus,t1.canceled,t1.docdate
frompch1t0
leftjoinopcht1ont0.[DocEntry]=t1.[DocEntry]
where
t1.docdate<'2017-12-01'--条件1
andt0.itemcode='GD01002'-----条件2

union----关键部分，字段一样时，可以通过union链接成一个语句,当部分查询字段没有时，可以根据类型补空或者0

select--t0.itemcode,t0.quantity--,t1.*,t1.docstatus,t1.canceled,t1.docdate
sum(t0.quantity)
fromign1t0
leftjoinoignt1ont0.[DocEntry]=t1.[DocEntry]
WHERE
t1.docdate<'2017-12-01'--条件1
andt0.itemcode='GD01002'-----条件2
groupbyt0.itemcode

……--后面继续就行

--第二种，建临时表
if(object_id('temp..#a')>0)
droptable#a
createtable#a
(
itemcodevarchar(100),
quantityint,
docstatusint,
canceledint,
docdatedate
)

insertinto#a(quantity,docstatus,docstatus,canceled,docdate)
selectt0.itemcode,t0.quantity--,t1.*,t1.docstatus,t1.canceled,t1.docdate
frompch1t0
leftjoinopcht1ont0.[DocEntry]=t1.[DocEntry]
where
t1.docdate<'2017-12-01'--条件1
andt0.itemcode='GD01002'-----条件2

insertinto#a(quantity,docstatus,docstatus,canceled,docdate)
select--t0.itemcode,t0.quantity--,t1.*,t1.docstatus,t1.canceled,t1.docdate
sum(t0.quantity)
fromign1t0
leftjoinoignt1ont0.[DocEntry]=t1.[DocEntry]
WHERE
t1.docdate<'2017-12-01'--条件1
andt0.itemcode='GD01002'-----条件2
groupbyt0.itemcode

……--继续插入数据

--最后查询
select*from#a

--关于存储过程
Createprocsp_Test
(
@ddate,
@codevarchar(100)
)
as
begin

--这里只放一个语句，用于参数的示例，只需要将上面的语句放到存储过程中，并将参数替换就可以了
select--t0.itemcode,t0.quantity--,t1.*,t1.docstatus,t1.canceled,t1.docdate
sum(t0.quantity)
fromign1t0
leftjoinoignt1ont0.[DocEntry]=t1.[DocEntry]
WHERE
t1.docdate<@d--条件1
andt0.itemcode=@code-----条件2
groupbyt0.itemcode
end