SQL Story摘錄（八）————資料抽取 (轉)

SQL Story摘錄（八）————資料抽取 (轉)[@more@]

資料抽取

理論上的關係型，資料是以關係的形式存在。通常我們都可以把它們視為一種集合。這樣，資料一般是以無序的形式存在的。這種做法的好處自不用多加討論了，不過我們也得承認，有時這樣也會帶給我們一些麻煩。我最近就遇到這麼一件。

我在網上遇上我的一個老同學，他提出了這樣一個問題。有一個表（假設就叫myTable），表中有三個整型欄位（假設就叫A11，A12，A13），其上有一個唯一鍵。現在他想要在這個表的資料中取一些樣品。他希望從中A13的各個取值中，各取一條記錄。然後我問他，對A11和A12有什麼要求嗎？他說，沒有，怎麼取都可以，有沒有規律都行。我想當然的說，簡單，給我二十分鐘。

有一個不知你們聽過沒有。說很久以前，一個大數學家講課，有學生問他能不能證明四色定理。他說，那容易，我現在就證給你們……然後他寫啊寫啊……想啊想啊……一直想到下課也沒證出來，然後外面打了一個大大的響雷，他自嘲說，上帝也被我的狂妄激怒了……

什麼，你問我剛才的問題怎麼樣了？我告訴你，二十分種，不不，四十分鐘，不不，好像是一個小時……後來到半夜了，外面開始打雷……我對我的同學說，老兄，我錯了，讓我把這問題拿回去想想先……

以下是一個示例，我隨便錄了些資料進去

A11 A12 A13

----------- ----------- -----------

0 0 0

0 0 1

0 1 1

1 1 1

1 1 2

1 2 2

2 2 2

2 2 1

2 2 3

2 1 3

2 3 3

3 3 1

3 3 2

3 2 3

3 2 1

3 2 2

3 1 1

3 1 2

3 1 3

一開始，我是想用一個 MAX(A11), MAX(A12), A13 FROM myTable GROUP BY A13，後來用腳趾頭想了想，發現不可能。因為 MAX(A11)和MAX(A12)都只依賴於分組子句 GROUP BY A13。顯然這樣MAX(A11)和MAX(A12) 不一定會是同一條記錄中的資料。就像下面這樣：

A13

----------- ----------- -----------

0 0 0

3 3 1

3 3 2

3 3 3

注意，其實3, 3, 3這一行是不存在的。

然後我想，SELECT MAX(A11), MAX(A12), A13 FROM myTable GROUP BY A13, A12行不行？用 SERVER 試了試，也不對。 因為這樣的話，它會先按 A13, A12來分組所返回的結果集，所以會比我們要的資料多的多。就像下面這樣

A13

----------- ----------- -----------

0 0 0

0 0 1

3 1 1

3 1 2

3 1 3

3 2 1

3 2 2

3 2 3

3 3 1

3 3 2

2 3 3

諸如此類的傻事我還幹了很多，後來我終於想明白我要什麼資料了：取A12，A13，使得在所有按A13的值分組後，取每組中最大的A12，然後取整記錄，使得A11是A12和A13滿足前述條件後的最大的一個值。這樣，我寫出了一個語句：

SELECT

(SELECT MAX(I.A11) FROM myTable I WHERE I.A13 = O.A13 AND I.A12 = MAX(O.A12)) A11

, MAX(O.A12) A12

, O.A13 A13

FROM myTable O

GROUP BY O.A13

這行語句返回值如下：

A11 A12 A13

----------- ----------- -----------

0 0 0

3 3 1

3 3 2

2 3 3

同樣的，我們可以解釋它為最兩個結果集，一個是 SELECT MAX(A11), MAX(A12), A13 FROM myTable GROUP BY A13，一個是 SELECT MAX(A12), A13 FROM myTable GROUP BY A13, A12，然所按A12和A13進行等值聯接，這就是：

SELECT MAX(L.A11) A11

, MAX(L.A12) A12

, L.A13 A13

FROM myTable L

JOIN myTable R

ON L.A13 = R.A13

GROUP BY L.A13, L.A12

HAVING L.A12 = MAX(R.A12)

我個覺得這樣子比較清爽，不知各位以為如何？

事實上，這一類的資料抽取，不可能用一個簡單查詢得到，因為其中有一個欄位要同時參予分組和統計。只有（事實上是隻要）把我們的問題用SQL語言的思考方式描述清楚，答案也就得到了，一切就這麼簡單。

對於一類完全按行隨機抽取的問題，除了化為Top n問題解決外，還有一個好主意就是在支援物理行號的資料庫中，用rowid 或標識列之類的技術來解決，這樣比較簡單，就不多說了。

補充一句，以前我說過InterBase不支援自動標識列，但實際上，它也可以間接的實現。在InterBase中，我們可以Create一種叫 generator的全域性變數，然後用一個，結合一個叫GEN_ID的，就可以做到。雖說有點麻煩，但並非不可能。詳細情況，大家可以查詢 InterBase有關技術文件。

歡迎來信和我討論技術問題：lx1978@263。