必須知道的SQL編寫技巧，多條件查詢不拼字串的寫法

雲霏霏發表於2014-11-14
SQL字串

　　在做專案中，我們經常遇到複雜的查詢方法，要根據使用者的輸入，判斷某個引數是否合法，合法的話才能當作過濾條件，我們通常的做法是把查詢SQL賦值給一個字串變數，然後根據判斷條件動態的拼接where條件進行查詢。下面來簡單說一下寫SQL中遇到的問題和解決辦法。

 

 一、不確定欄位名，而產生的SQL字串拼接

 　　比如，有個公司要做一個系統，要支援多語言，這個時候我們就要將語音資訊儲存在資料庫中。然後，根據客戶選擇查詢對應的語言欄位，進行顯示。下面我們來模擬這個場景，開啟SQL Server，新建SysLanguage表，新增一些語言欄位，如English，Chinese，French，這裡French不知道是什麼，就都設定為Empty。

 

建表的SQL和插入一些簡單的資料，SQL如下：

CREATE TABLE SysLanguage(
    Id INT PRIMARY KEY,
    English NVARCHAR(100),
    Chinese NVARCHAR(100),
    French NVARCHAR(100)
)

INSERT INTO SysLanguage VALUES (1, 'Hello', '你好', 'Empty')
INSERT INTO SysLanguage VALUES (2, 'world', '世界', 'Empty')
INSERT INTO SysLanguage VALUES (3, 'Book', '書', 'Empty')
INSERT INTO SysLanguage VALUES (4, 'Open', '開啟', 'Empty')
INSERT INTO SysLanguage VALUES (5, 'Save', '儲存', 'Empty')
INSERT INTO SysLanguage VALUES (6, 'New', '新建', 'Empty')

 那麼，考慮到系統效能，我們可能會使用儲存過程，來減少網路通訊量和提高響應速度。那麼，在儲存過程中，我們會傳入一個引數，然後根據引數來查詢指定的語言欄位，那麼SQL該怎麼寫呢？

這個時候，我們會想到在C#程式碼中，我們都是把SQL拼好，然後傳給SQL Server執行，我們可以拼接字串啊，然後有了下面的寫法:

DECLARE @sql NVARCHAR(100);
DECLARE @para NVARCHAR(20);
SET @para = 'Chinese';
SET @sql = 'select ID, ' + @para + ' from SysLanguage'
print @sql
exec(@sql)

當然這樣寫沒有任何問題，但是當SQL很複雜的時候，很多的引號和字元轉義，還有拼接字串帶來的缺少空格的問題，會令人崩潰！下面來說一種不拼接字串的寫法，程式碼如下：

DECLARE @para NVARCHAR(20);
SET @para = 'Chinese';
SELECT ID, 
CASE @para 
WHEN 'English' THEN English
WHEN 'Chinese' THEN Chinese 
ELSE
French
END
AS [Language]
FROM
SysLanguage

這裡使用了CASE WHEN語句來進行判斷，當判斷條件太多時，寫起來也很累，我們可以寫一個Function來進行封裝，當然，具體用那種方法，要看具體情況了。

這裡說道了CASE WHEN，就不得不提另外一種查詢了。先來看下需求：從學生表中查詢出男生的人數，女生的人數和學生總人數。

看到這個，我們首先想到了SUM函式，但是，然後呢？好像哪裡不對？那麼，到底該怎麼寫呢，當然還是用CASE WHEN，來看下程式碼吧

SELECT SUM(CASE StuSex WHEN 1 THEN 1 ELSE 0 END) AS Boys,
SUM(CASE StuSex WHEN 1 THEN 0 ELSE 1 END) AS Girls,  
SUM(StuID) AS Total
FROM Student

看到程式碼，感覺是不是很簡單啊，就是當時沒想到，呵呵！

 

 二、不定查詢條件產生的SQL字串拼接

　　習慣了C#程式碼，突然寫起SQL來，感覺還是有點那麼不順手！下面來看看，當有多個查詢條件，但是又不確定幾個查詢條件時，SQL該怎麼寫？我們經常遇到這種需求，頁面上很多控制元件，要根據使用者的輸入進行查詢，這個時候我們就要判斷傳入的控制元件的值是否符合指定條件，符合條件了才能被作為查詢條件，否則就忽略這個條件。

拼接字串的寫法相信大家都經常用，這裡就不寫了，我們之接來看布拼接SQL怎麼查詢！

比如現在要查詢指定條件的學生，使用者可能查詢所有，或查詢男生，或查詢指定Id的學生，或……等待，可能很多種情況。下面我們來寫布拼接字串的SQL，程式碼如下：

DECLARE @ID INT;
DECLARE @Sex BIT;
DECLARE @Name NVARCHAR(50);

SET @ID = -1;
SET @Sex = NULL;
SET @Name = 'Ja';

SELECT * FROM Student
WHERE (@ID = -1 OR StuID = @ID)
AND (@Sex IS NULL OR StuSex = @Sex)
AND (@Name IS NULL OR StuName LIKE '%' + @Name + '%')

這裡宣告三個變數，當做傳入的引數，傳入的引數都有預設值，或者為NULL，我們在where新增里面用了OR新增判斷，首先判斷@ID = -1，如果條件成立了，OR就不會再判斷OR後面的條件，到這裡就(@ID = -1 OR StuID = @ID)就返回一個TRUE，相當於WHERE TRUE AND ……，所以就會忽略這個查詢條件繼續走下面的過濾判斷。這樣寫，比拼接字串好的多吧！

 

最後來說一下資料庫中N字元的作用，說白了就是將後面的字串內容，轉換成Unicode編碼，來寫段SQL測試一下吧

DECLARE @a NVARCHAR(20)
DECLARE @b NVARCHAR(20)
SET @a = N'中文'
SET @b = '中文'
IF(@a = @b)
PRINT '數值相等'
ELSE
PRINT '不相等'

輸出結果如下：

在中文版的系統中，是相等的，在英文版的系統中，@b會顯示亂碼。大家可以測試一下！

 

 作者：雲霏霏

 部落格地址：http://www.cnblogs.com/yunfeifei/

 宣告：本部落格原創文字只代表本人工作中在某一時間內總結的觀點或結論，與本人所在單位沒有直接利益關係。非商業，未授權，貼子請以現狀保留，轉載時必須保留此段宣告，且在文章頁面明顯位置給出原文連線。

 

相關文章