1.Create constraints / 建立條件約束
就是限制該欄位可以儲存的值得條件，例如限制 gender 欄位只可輸入'M' 或 'N'，其他值不允許。範例：
先建立二個新欄位，gender & age
建立條件約束
限制 age 欄位
2. select... into... from... 來快速複製/建立 資料表
範例：將 demo 資料表複製一份 為 demotest 資料表
select * into demotest from demo
3.暫存資料表 VS 檢視表
暫存資料表：會暫時將所得到的資料暫時儲存在硬碟，等到該連線結束，就會刪除該暫存資料表。適合在在短時間內多次使用。
檢視表：不儲存資料，僅儲存語法。每次使用時都會執行該檢視表的語法一次，如果有較多的查詢與運算在該檢視表內，在短時間內多次使用就會消耗較多的資源。
PS：其實上述二者執行第一次的成本都是一樣多的。在同一連結未中斷前，執行二次以上，暫存資料表會較省資源。
建立檢視表語法：
SELECT dbo.客戶.客戶名稱, SUM(T.小計) AS 客戶總額
FROM dbo.客戶 INNER JOIN
(SELECT dbo.訂單.客戶編號, dbo.訂單細目.數量 * dbo.書籍.單價 AS 小計
FROM dbo.客戶 AS 客戶_1 INNER JOIN
dbo.訂單 ON 客戶_1.客戶編號 = dbo.訂單.客戶編號 INNER JOIN
dbo.訂單細目 ON dbo.訂單.訂單序號 = dbo.訂單細目.訂單序號 INNER JOIN
dbo.書籍 ON dbo.訂單細目.書籍編號 = dbo.書籍.書籍編號
GROUP BY dbo.訂單.客戶編號, dbo.訂單細目.數量 * dbo.書籍.單價) AS T ON dbo.客戶.客戶編號 = T.客戶編號
GROUP BY dbo.客戶.客戶名稱
建立暫存資料表語法：(使用 # 加上資料表名稱)
Select * Into #tmp客戶總額表 From (
SELECT dbo.客戶.客戶名稱, SUM(T.小計) AS 客戶總額
FROM dbo.客戶 INNER JOIN
(SELECT dbo.訂單.客戶編號, dbo.訂單細目.數量 * dbo.書籍.單價 AS 小計
FROM dbo.客戶 AS 客戶_1 INNER JOIN
dbo.訂單 ON 客戶_1.客戶編號 = dbo.訂單.客戶編號 INNER JOIN
dbo.訂單細目 ON dbo.訂單.訂單序號 = dbo.訂單細目.訂單序號 INNER JOIN
dbo.書籍 ON dbo.訂單細目.書籍編號 = dbo.書籍.書籍編號
GROUP BY dbo.訂單.客戶編號, dbo.訂單細目.數量 * dbo.書籍.單價) AS T ON dbo.客戶.客戶編號 = T.客戶編號
GROUP BY dbo.客戶.客戶名稱) T;
4.第15章
a.動態統計
1.數量累加
第一步：
Select b1.訂單序號, b1.數量, b2.訂單序號, b2.數量
From 書籍訂單 b1, 書籍訂單 b2
Where b1.訂單序號 >= b2.訂單序號
Order By b1.訂單序號
累加結果：
Select b1.訂單序號, b1.數量, SUM(b2.數量)
From 書籍訂單 b1, 書籍訂單 b2
Where b1.訂單序號 >= b2.訂單序號
Group By b1.訂單序號, b1.數量
order By b1.訂單序號
2.移動平均
第一步：
Select b1.訂單序號, b1.數量, b2.訂單序號, b2.數量
From 書籍訂單 b1, 書籍訂單 b2
Where b1.訂單序號 >= 5
And b1.訂單序號 between b2.訂單序號 and b2.訂單序號 + 4
Order By b1.訂單序號
說明：用下面的表解釋，希望可以幫助大家更容易了解
根據 where 以及 and 的條件，b1.訂單序號 要介於 b2.訂單序號 和 b2.訂單序號 + 4 之間。
這裡用數學計算式子來看，假設 b1.訂單序號 = X，b2.訂單序號 = n，上面的條件式就變成 n <= X <= n + 4
左邊的表是當 b1.訂單序號 = 5 時，b2.訂單序號 有可能是 1至 36 (因為該資料表共有36筆資料)。所以
b2.訂單序號 = 1 、 b2.訂單序號 + 4 = 5 -----> 1 <= 5 <= 5
b2.訂單序號 = 2 、 b2.訂單序號 + 4 = 6 -----> 2 <= 5 <= 6
b2.訂單序號 = 3 、 b2.訂單序號 + 4 = 7 -----> 3 <= 5 <= 7
b2.訂單序號 = 4 、 b2.訂單序號 + 4 = 8 -----> 4 <= 5 <= 8
b2.訂單序號 = 5 、 b2.訂單序號 + 4 = 9 -----> 5 <= 5 <= 9
b2.訂單序號 = 6 、 b2.訂單序號 + 4 = 10 -----> 6 <= 5 <= 10 (不成立)
..... 、 ........
但是只有當 b2.訂單序號 ( n ) 介於 1 至 5 時，條件才會成立。
同理，當 X = 6 時，n 等於 1 時，n + 4 = 5， -----> 1 <= 6 <= 5 (不成立)
所以 n 會是介於 2 至 6 之間。
另一個例子是相反的：
把 between b2.訂單序號 and b2.訂單序號 + 4
改成 between b2.訂單序號 - 4 and b2.訂單序號
如此 "移動窗格" ( b2.訂單序號) 就會變成 5 至 9，而不是原本的 1 至 5 了。
結果：
Select b1.訂單序號, Avg(b2.數量) as 前後5張訂單之平均數量
From 書籍訂單 b1, 書籍訂單 b2
Where b1.訂單序號 >= 5
And b1.訂單序號 between b2.訂單序號 and b2.訂單序號 + 4
Group by b1.訂單序號
Order By b1.訂單序號
3.排名
第一步語法：將 "書籍訂單 b2" count 出來共有36個，所以顯示出36。
select COUNT(數量) from 書籍訂單 b2
第二步語法：將 "書籍訂單 b1" select 出來共有36筆
select 訂單序號,數量 from dbo.書籍訂單 b1
結果：將第一第二步合起來，再用 where 篩選資料
下面二種寫法的結果一樣差別在使用 ranking、數量來排序。
第一種寫法：
select 訂單序號,數量 ,
(select COUNT(數量) from 書籍訂單 b2
where b2.數量 > b1.數量)+1 as ranking
from dbo.書籍訂單 b1
order by ranking
第二種寫法：
select 訂單序號,數量 ,
(select COUNT(數量) from 書籍訂單 b2
where b2.數量 > b1.數量)+1 as ranking
from dbo.書籍訂單 b1
order by 數量
說明：下面這句語法的意思
(select COUNT(數量) from 書籍訂單 b2 //從 b2 數出的數量
where b2.數量 > b1.數量) //當 b2 的數量> b1 的數量時
+1 as ranking //然後+1，因為排名第一顯示出來的是0，所以要加1。
No comments:
Post a Comment