sqlserverwith

『壹』 sql中使用WITH 語句的查詢

sql
with
as
用法(適用sqlserver，好像oracle也適用)
Server
2005中提供了公用表表達式（CTE），使用CTE，可以使SQL語句的可維護性，同時，CTE要比表變數的效率高得多。
下面是CTE的語法：
[
WITH
<common_table_expression>
[
,n
]
]
<
common_table_expression>::=
expression_name
[
(
column_name
[
,n
]
)
]
AS
(
CTE_query_definition
)
現在使用CTE來解決上面的問題，SQL語句如下：
with
cr
as
(
select
CountryRegionCode
from
person.CountryRegion
where
Name
like
'C%'
)
select
*
from
person.StateProvince
where
CountryRegionCode
in
(select
*
from
cr)
其中cr是一個公用表表達式，該表達式在使用上與表變數類似，只是SQL
Server
2005在處理公用表表達式的方式上有所不同。
在使用CTE時應注意如下幾點：
1.
CTE後面必須直接跟使用CTE的SQL語句（如select、insert、update等），否則，CTE將失效。如下面的SQL語句將無法正常使用CTE：
with
cr
as
(
select
CountryRegionCode
from
person.CountryRegion
where
Name
like
'C%'
)
select
*
from
person.CountryRegion
--
應將這條SQL語句去掉
--
使用CTE的SQL語句應緊跟在相關的CTE後面
--
select
*
from
person.StateProvince
where
CountryRegionCode
in
(select
*
from
cr)
2.
CTE後面也可以跟其他的CTE，但只能使用一個with，多個CTE中間用逗號（,）分隔，如下面的SQL語句所示：
with
cte1
as
(
select
*
from
table1
where
name
like
'abc%'
),
cte2
as
(
select
*
from
table2
where
id
>
20
),
cte3
as
(
select
*
from
table3
where
price
<
100
)
select
a.*
from
cte1
a,
cte2
b,
cte3
c
where
a.id
=
b.id
and
a.id
=
c.id
3.
如果CTE的表達式名稱與某個數據表或視圖重名，則緊跟在該CTE後面的SQL語句使用的仍然是CTE，當然，後面的SQL語句使用的就是數據表或視圖了，如下面的SQL語句所示：
--
table1是一個實際存在的表
with
table1
as
(
select
*
from
persons
where
age
<
30
)
select
*
from
table1
--
使用了名為table1的公共表表達式
select
*
from
table1
--
使用了名為table1的數據表
4.
CTE
可以引用自身，也可以引用在同一
WITH
子句中預先定義的
CTE。不允許前向引用。
5.
不能在
CTE_query_definition
中使用以下子句：
（1）COMPUTE
或
COMPUTE
BY
（2）ORDER
BY（除非指定了
TOP
子句）
（3）INTO
（4）帶有查詢提示的
OPTION
子句
（5）FOR
XML
（6）FOR
BROWSE
6.
如果將
CTE
用在屬於批處理的一部分的語句中，那麼在它之前的語句必須以分號結尾，如下面的SQL所示：
declare
@s
nvarchar(3)
set
@s
=
'C%'
;
--
必須加分號
with
t_tree
as
(
select
CountryRegionCode
from
person.CountryRegion
where
Name
like
@s
)
select
*
from
person.StateProvince
where
CountryRegionCode
in
(select
*
from
t_tree)
7、CTE除了可以簡化嵌套SQL語句外，還可以進行遞歸調用

『貳』 sqlserver中使用 with as ，後面不能用if else嗎

不能使用，with as是可以嵌套使用的

還有where後面不可以使用 case when

『叄』 sqlserver update 可以使用with嗎

可以，如：

;WITHtmpTableAS(
SELECTROW_NUMBER()OVER(ORDERBY(id))ASpx,tmpfield1FROM#a
)
UPDATEtmpTableSETtmpfield1=px
GO

『肆』 sqlserver with snapshot 有什麼作用

資料庫快照為你現有的資料庫創建了一個資料庫的殼，然後無論何時當數據頁被修改的時候，改變也同時被寫入稀疏文件(sparse file)當中。當人們獲取數據的時候，數據中沒有變化的部分是從原始資料庫中得到的，而改變的部分則是從稀疏文件中獲得。
稀疏文件和資料庫快照
當資料庫快照被創建的時候，第一次的創建是十分迅速的。因為實際上只是創建了一個用來記錄被修改文件的殼。隨著時間的推移，文件不斷的被修改，這些修改頁都將被寫進稀疏文件。你的主資料庫中修改的文件越多，就有越多的文件被寫入稀疏文件。因此，有越來越多的磁碟空間被用來保存你的主資料庫和快照的資料庫，也增加了你伺服器的磁碟輸入輸出的次數。
稀疏文件被寫入大小為64KB的分組塊當中。每一個分組塊增量能包含8個大小為8KB的數據頁。所以，每次在你的主資料庫中有任何的數據改變，都會先把數據頁拷貝到稀疏文件當中，然後再將主資料庫中文件的變化寫入稀疏文件。一旦數據頁被寫入稀疏文件，他們就不再需要被寫出來。因為頁面的全部內容被保護起來，讓其處於當快照建立時的狀態。
為了實現優化磁碟並消除磁碟沖突，在主資料庫以外的獨立的驅動器和陣列中創建稀疏文件是一個明知之舉。原因有二：
其一，當快照被建立的時候，沒有數據被寫入稀疏文件。從快照進行的所有的數據訪問實際上都是在主資料庫文件當中的。隨著時間的推移，你會通過在不同的陣列和磁碟上從主文件資料庫讀取未被修改過的文件和從稀疏文件讀取修改過的數據的方法來減少輸入輸出的負擔。
其二，根據你資料庫數據的易變動性和數據變化的數量，你可以通過將在主資料庫的讀取工作和稀疏文件的寫入工作分離來減少輸入輸出的瓶頸大小。
使用資料庫快照
在這里你一定要記住的事情就是，你的查詢請求訪問的依然是你的主資料庫。當初始的快照被建立的時候，其實僅建立了一個空的殼子。所有的數據請求都是在主資料庫文件中被完成的。隨著時間的流逝和文件不斷地被修改，就有一些數據請求從初始的資料庫文件中分離出來指向了稀疏文件。所以，盡管看上去它是一個獨立的資料庫，那些根本的數據仍然是源於主資料庫。
鑒於此，你需要確定不要試圖去進行你日常活動范圍以外的查詢。這樣說吧，你創建了一個快照，接著你進行了讀寫的操作，並對每個人做了記錄。當那些記錄被執行查詢操作時，他們仍然繼續影響著主資料庫。所以你要保證任何新的活動都不會影響主數據的活動。
另外，你需要記住到底有哪些數據是被寫入稀疏文件里的，而不是認為所有可能的數據都被寫進了稀疏文件。基本上，當快照被創立時，主資料庫的大小就是快照稀疏文件的潛在大小。如果稀疏文件中的數據量已經達到甚至超過資料庫的一半時，也許再創造一個資料庫的完整拷貝來取代現有的快照是一個更好的主意。
綜上所述，我認為，資料庫快照是一個非常新的功能。我也希望在SQL Server2005的所有版本，而不僅僅在企業版和開發版中可以應用這個功能。有一個沒有討論的地方就是我們沒有討論有關對資料庫鏡像使用快照。其實，無論是鏡像還是原資料庫，快照都給了你最好的方法。因為鏡像是離線的，你並不能訪問那些數據，所以說無論是鏡像還是原資料庫，它都給了你最好的方法。花一些時間去理解快照是如何應用於你的環境中的，並且確認你監視著維護快照的影響以及通過快照進行的數據存儲。

『伍』 sqlserver with 語法

一．sqlserver 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短語里的數據放入一個全局臨時表裡。很多查詢通過這種方法都可以提高速度。
二．使用方法
先看下面一個嵌套的查詢語句：
select * from person.StateProvince where CountryRegionCode in
(select CountryRegionCode from person.CountryRegion where Name like 'C%')
declare @t table(CountryRegionCode nvarchar(3))
insert into @t(CountryRegionCode) (select CountryRegionCode from person.CountryRegion where Name like 'C%')
select * from person.StateProvince where CountryRegionCode
in (select * from @t)

『陸』 請教Sqlserver 2008中使用with as語句遞歸查詢的問題。

Transact-SQL 中的遞歸 CTE 的結構與其他編程語言中的遞歸常式相似。盡管其他語言中的遞歸常式返回標量值，但遞歸 CTE 可以返回多行。
遞歸 CTE 由下列三個元素組成：
常式的調用。
遞歸 CTE 的第一個調用包括一個或多個由 UNION ALL、UNION、EXCEPT 或 INTERSECT 運算符聯接的 CTE_query_definitions。由於這些查詢定義形成了 CTE 結構的基準結果集，所以它們被稱為「定位點成員」。
CTE_query_definitions 被視為定位點成員，除非它們引用了 CTE 本身。所有定位點成員查詢定義必須放置在第一個遞歸成員定義之前，而且必須使用 UNION ALL 運算符聯接最後一個定位點成員和第一個遞歸成員。
常式的遞歸調用。
遞歸調用包括一個或多個由引用 CTE 本身的 UNION ALL 運算符聯接的 CTE_query_definitions。這些查詢定義被稱為「遞歸成員」。
終止檢查。
終止檢查是隱式的；當上一個調用中未返回行時，遞歸將停止。
如果遞歸 CTE 組合不正確，可能會導致無限循環。例如，如果遞歸成員查詢定義對父列和子列返回相同的值，則會造成無限循環。在測試遞歸查詢的結果時，
可以通過在 INSERT、UPDATE、DELETE 或 SELECT 語句的 OPTION 子句中使用 MAXRECURSION 提示和 0 到 32,767 之間的值，來限制特定語句允許的遞歸級數。有關詳細信息，請參閱查詢提示 (Transact-SQL) 和 WITH common_table_expression_r(Transact-SQL)。

偽代碼和語義
遞歸 CTE 結構必須至少包含一個定位點成員和一個遞歸成員。以下偽代碼顯示了包含一個定位點成員和一個遞歸成員的簡單遞歸 CTE 的組件。
WITH cte_name ( column_name [,...n] )
AS
(
CTE_query_definition –- Anchor member is defined.
UNION ALL
CTE_query_definition –- Recursive member is defined referencing cte_name.
)
-- Statement using the CTE
SELECT *
FROM cte_name
遞歸執行的語義如下：
將 CTE 表達式拆分為定位點成員和遞歸成員。
運行定位點成員，創建第一個調用或基準結果集 (T0)。
運行遞歸成員，將 Ti 作為輸入，將 Ti+1 作為輸出。
重復步驟 3，直到返回空集。
返回結果集。這是對 T0 到 Tn 執行 UNION ALL 的結果。

If the MAXRECURSION query hint is not specified, the default limit is 100.