一條大而全SQL與函數之間的抉擇：誰是更為有效SQL？，更為有效sql

一條大而全SQL與函數之間的抉擇：誰是更為有效SQL？，更為有效sql

轉載請註明出處：轉載請註明出處：jiq•欽's technical Blog

特別注意，這裡討論的是Oracle11g資料庫，對於SQL Server，MYSQL等其他資料庫不一定適用。

1、問題描述

當前系統中有兩張表，一張是產品表，一張是附件表格。

PRODUCT（PRODUCTID，PRODUCTNAME，PRODUCTPRICE，PICTURE）

ATTACHMENT（ATTACHMENTID，FILEPATH，BUSINESSTYPE，BUSINESSID）

其中產品表包含產品ID，產品名稱，產品價格，圖片等欄位，附件表格包含附件ID，附件全路徑，附件所屬模組編號（比如產品模組是105），附件所屬記錄ID（這裡就是產品ID）。

需求是在查詢全部產品介面顯示所有產品的列表，並且每一項記錄後面都需要一個“是否有視頻”的欄位指示當前產品記錄是否包含視頻。此外還要在上方的查詢條件地區能夠按照有無視頻進行產品的查詢。

2、解決思路

涉及左連結，Regex、函數、去重、ROWNUM等概念

方式1： 一條大而全的SQL語句

我做這個的時候，首先想到要用一個比較複雜的SQL語句拼出來一條SQL語句，能夠查出最終要顯示的所有產品記錄。

所以首先想到左連結，因為需要保障左邊產品表的所有記錄都能夠顯示出來，不會有任何遺漏，所以寫了這樣一個SQL語句：

select T.*,A.BUSINESSTYPEfrom PRODUCT T left join ATTACHMENT AonT.PRODUCTID=A.BUSINESSID and regexp_like(A.FILEPATH, '^.*.(rmvb|avi|mtv|3gp|flv|mpeg|mov)')

將產品表和附件表格進行左連結，右邊的附件表格只有在滿足BUSINESSID等於左邊產品ID，並且附件是視頻檔案的情況下才串連到左表記錄。

注意：其中where條件中用到了Regex判斷，即regexp_like函數。

但是當一條產品有兩個視頻檔案的時候問題就出來了，比如ID為331的產品有兩個視頻附件，那麼ATTACHMENT表中就有兩條附件記錄滿足：

T.PRODUCTID=A.BUSINESSID and regexp_like(A.FILEPATH, '^.*.(rmvb|avi|mtv|3gp|flv|mpeg|mov)')

where條件，那麼這兩條附件記錄都會串連到左表，因為左連結並不是說以左表為基準，左表有多少條，串連出來就是多少條，在這種情況下串連出來的記錄數目就會比原有的左表的記錄數目多。這就導致串連結果是這樣：

PRODUCTID  ......   BUSINESSID BUSINESSTYPE 

331                                331                    105

331                                331                    105

你可能已經想到了，我們可以進行去重處理，於是出現下面語句：

select distinct T.*,A.BUSINESSTYPEfrom PRODUCT T left join ATTACHMENT AonT.PRODUCTID=A.BUSINESSID and regexp_like(A.FILEPATH, '^.*.(rmvb|avi|mtv|3gp|flv|mpeg|mov)')

因為看似上面的重複的記錄一模一樣，所以使用distinct 關鍵字來進行記錄去重處理。

很不幸這條語句編譯出錯，我開始也以為一個產品有兩個以上視頻的話，串連出來的重複的記錄是一模一樣的。

可惜在我這個情境下不一樣，因為產品表裡面有個PICTURE欄位，是BLOB類型，儲存圖片的位元據，不允許用distinct關鍵字去重。

可以通過下面的方式進行去重：

select * from(select distinct T.*,A.BUSINESSTYPE,ROW_NUMBER() OVER(PARTITION BY T.PRODUCTID order by T.PRODUCTNAME) rkfrom PRODUCT T left join ATTACHMENT AonT.PRODUCTID=A.BUSINESSID and regexp_like(A.FILEPATH, '^.*.(rmvb|avi|mtv|3gp|flv|mpeg|mov)'))where rk=1

即根據重複的PRODUCTID為基準，只要重複都只取第一條記錄。

就這樣一個滿足條件的SQL就已經構造出來了，查詢的結果就是產品表所有記錄有且僅列出來一次，每條產品記錄後面跟上一個欄位BUSINESSTYPE，如果這個欄位有值，標識這條產品記錄有視頻附件，如果沒有值標識沒有視頻附件。

至於查詢的時候，以有無視頻作為查詢條件，只需要在上面這條SQL外面再套一層，加上where BUSINESSTYPE='105'這個條件即可。

方式2： 藉助函數等進階特性解決

將上面的方法與項目組中更加有經驗的同事交流後發現自己這個方式不夠優雅，更多地是覺得自己在資料庫方面經驗還太淺，完全可以有很多優雅的方式解決這個問題。

我上面的解決方式首先太過複雜，其次是通過串連之後記錄中BUSINESSTYPE欄位有沒有值，是不是105來判斷該產品有無視頻。

更多時候我們可能不僅僅是需要保留串連後右邊表的某個欄位的值，而更多可能是需要針對某些列進行值的轉換！！！

比如我們這裡就想在產品表後面加一列，這列的值如果是0標識沒有視頻附件，如果是1標識有視頻附件。另外的情境還有我們就想在產品表後面加上一列，這列的值表示產品擁有的附件的個數，這些資訊哪兒來，肯定是從附件表格來，但是光靠兩個表的連結能做到嗎？

這個時候我們要藉助函數來實現：

select distinct T.*,FUNC_HASATTACHMENT(t.PRODUCTID)from PRODUCT T

看這種方式多優雅，可拓展性多強，後面還可以加更多函數，你想要知道什麼資訊都可以，理論上函數都能實現。

下面看這個函數如何根據產品ID的值，返回是否有附件（0-沒有有視頻附件，1-有視頻附件）

create OR REPLACE function FUNC_HASATTACHMENT(productID in decimal)return numberisnum number;result number;BEGINresult:=0;select count(ATTACHMENTID) into num from ATTACHMENT where BUSINESSTYPE='105' and BUSINESSID=productID and regexp_like(A.FILEPATH, '^.*.(rmvb|avi|mtv|3gp|flv|mpeg|mov)');if(num>0) then result:=1;else result:=0;endif;return result;END;

你看我們只需要傳入產品ID，根據這個ID在附件表格裡面搞一搞就能搞出來這個產品有沒有視頻附件。

看來我們考慮一個資料庫操作，並且感覺到稍微有點麻煩的時候，就要嘗試跳出“大而全SQL”這個怪圈，嘗試考慮用過程/函數，觸發器，視圖等進階特性來輔助解決！

下面附上函數的介紹：

CREATE [OR REPLACE] FUNCTION function_name (arg1 [ { IN | OUT | IN OUT }] type1 [DEFAULT value1], [arg2 [ { IN | OUT | IN OUT }] type2 [DEFAULT value1]], ...... [argn [ { IN | OUT | IN OUT }] typen [DEFAULT valuen]]) [ AUTHID DEFINER | CURRENT_USER ]RETURN return_type  IS | AS    <類型.變數的聲明部分> BEGIN    執行部分    RETURN expressionEXCEPTION    異常處理部分END function_name;

更多細節參考：http://blog.csdn.net/jiyiqinlovexx/article/details/13417455

查詢最佳化 --- 分批查詢：

因為我們的產品數量還是比較多，幾千條以上，而且還有很多圖片，不可能一次查詢返回所有圖片，所以需要分批查詢，每次點擊都只載入一部分。

模式時這樣的： 點擊查詢按鈕，前台就會請求伺服器上的RPC介面返回第一批500條資料，並且為本次查詢建立一個唯一的sessionID，每次介面上點擊“載入更多”按鈕的時候，用這個sessionID，以及當前已經返回到了第幾行這兩個參數調用伺服器上的RPC介面繼續返回下一批資料。

如果點擊載入全部按鈕，就在用戶端開啟一個後台工作者線程一直跑這個“載入更多”這個過程。

核心就是記錄當前這次查詢回話的sessionID，當前已經返回了多少條記錄了這些資訊，以及分批查詢用的SQL語句。

下面就是分批查詢的SQL語句，返回第500-1000條記錄：

select * from(select A.*,ROWNUM RN from(select distinct T.*,FUNC_HASATTACHMENT(t.PRODUCTID) from PRODUCT T) A where ROWNUM<={1000})where RN>500

註：程式中500和1000是作為參數傳遞進去的。

sql 語句中那些是彙總函式？

一下內容純屬抄襲~，希望有點用~

8.2 彙總函式的應用

彙總函式在資料庫資料的查詢分析中，應用十分廣泛。本節將分別對各彙總函式的應用進行說明。
8.2.1 求和函數——SUM()

求和函數SUM( )用於對資料求和，返回選取結果集中所有值的總和。文法如下。

SELECT SUM(column_name)

FROM table_name

說明：SUM()函數只能作用於數值型資料，即列column_name中的資料必須是數值型的。

執行個體1 SUM函數的使用

從TEACHER表中查詢所有男教師的工資總數。TEACHER表的結構和資料可參見5.2.1節的表5-1，下同。執行個體代碼：

SELECT SUM(SAL) AS BOYSAL

FROM TEACHER

WHERE TSEX='男'

運行結果8.1所示。

圖8.1 TEACHER表中所有男教師的工資總數

執行個體2 SUM函數對NULL值的處理

從TEACHER表中查詢年齡大於40歲的教師的工資總數。執行個體代碼：

SELECT SUM(SAL) AS OLDSAL

FROM TEACHER

WHERE AGE>=40

運行結果8.2所示。

圖8.2 TEACHER表中所有年齡大於40歲的教師的工資總數

當對某列資料進行求和時，如果該列存在NULL值，則SUM函數會忽略該值。
8.2.2 計數函數——COUNT()

COUNT()函數用來計算表中記錄的個數或者列中值的個數，計算內容由SELECT語句指定。使用COUNT函數時，必須指定一個列的名稱或者使用星號，星號表示計算一個表中的所有記錄。兩種使用形式如下。

* COUNT(*)，計算表中行的總數，即使表中行的資料為NULL，也被計入在內。

* COUNT(column)，計算column列包含的行的數目，如果該列中某行資料為NULL，則該行不計入統計總數。
1．使用COUNT(*)函數對錶中的行數計數

COUNT(*)函數將返回滿足SELECT語句的WHERE子句中的搜尋條件的函數。

執行個體3 COUNT(*)函數的使用

查詢TEACHER表中的所有記錄的行數。執行個體代碼：

SELECT COUNT(*) AS TOTALITEM

FROM TEACHER

運行結果8.3所示。

圖8.3 使用COUNT(*)函數對錶中的行數計數

在該例中，SELECT語句中沒有WHERE子句，那麼認為表中的所有行都滿足SELECT語句，所以SELECT語句將返回表中所有行的計數，結果與5.2.1節的表5-1列出的TEACHER表的資料相吻合。

如果DBMS在其系統資料表中儲存了表的行數，COUNT(*)將很快地返回表的行數，因為這時，DBMS不必從頭到尾讀取表，並對物理表中的行計數，而直接從系統資料表中提取行的計數。而如果DBMS沒有在系統資料表儲存表的行數，將具有NOT NULL約束的列作為參數，使用COUNT( )函數，則可能更快地對錶行計數。

注意

COUNT(*)函數將準確地返回表中的總行數，而僅當COUNT()函數的參數列沒有NULL值時，才返回表中正確的行計數，所以僅當受NOT NULL限制的列作為參數時，才可使用COUNT( )函數代替COUNT(*)函數。
2．使用COUNT( )函數對一列中的資料計數

COUNT( )函數可用於對一列中的資料值計數。與忽略了所有列的......餘下全文>>
 
怎簡明的組織一個SQL，包含彙總函式與判斷取值

select b.a,a.b,a.c from
(select B,sum(C) as c from TABLE group by B) a,
(select A,B from TABLE where (substr(A,-1) = 'a') b
where a.b=b.b

---------to LZ----------
你執行a和b兩個查詢大概用多長時間？
如果這兩個子查詢很快的話，建議在b欄位建立索引

對了，還有一個問題，substr當用的時候，會使索引失效，你的(substr(A,-1) = 'a' 可以改成 A like '%a',反正都是一個意思

--------------------------
還有其他where條件的話，你可以考慮有索引的條件放在前邊，如果是因為資料量太大的話造成查詢速度慢，確實好辦法不多，但是可以考慮用到分區，但是好像又不太值得
先用強制索引實驗一下吧
表就是當前子查詢的表，索引就是能用到的索引，譬如第一個子查詢裡用欄位b上的索引，第二個子查詢用欄位a上的索引

select b.a,a.b,a.c from
(select /*+ index(表名,索引名) */ B,sum(C) as c from TABLE group by B) a,
(select /*+ index(表名,索引名) */ A,B from TABLE where A like = '%a') b
where a.b=b.b