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