SQL Server 2005 預存程序實作站內搜尋：關鍵字權重排序、ALL/ANY 模式與分頁

搜尋模式定義

1. 精確搜尋： 整串關鍵字當整體匹配。
2. ALL-Word (類 Google)： 空格分詞，**所有**詞皆須出現 (順序不拘)。
3. ANY-Word： **任一**詞出現即匹配。

核心難點：結果相關性排序

LIKE '%kw%' 結果隨機。需**關鍵字出現頻率加權排序** (類 TF-IDF 簡化版)。

UDF：計算關鍵字在文本中出現次數

CREATE FUNCTION dbo.WordCount (@Word VARCHAR(15), @Phrase VARCHAR(1000))
RETURNS SMALLINT AS BEGIN
    IF @Word IS NULL OR @Phrase IS NULL RETURN 0;
    DECLARE @BiggerWord VARCHAR(21) = @Word + 'x';          -- 比關鍵字長 1 字元
    DECLARE @BiggerPhrase VARCHAR(2000) = REPLACE(@Phrase, @Word, @BiggerWord);
    RETURN LEN(@BiggerPhrase) - LEN(@Phrase);               -- 長度差 = 出現次數
END

**高效原理：** 單次 REPLACE + 長度差運算，**無迴圈、無游標**，集合式運算極快。

參數化：固定 5 個關鍵字參數 (@Word1~@Word5)，超過忽略，不足視為 NULL (Google 限 10 詞)。

搜尋邏輯實作 (搜尋 Product 表 Name + Description)

ANY-Word：加權加總

SELECT Name,
    3 * dbo.WordCount(@Word1, Name) + dbo.WordCount(@Word1, Description)
  + 3 * dbo.WordCount(@Word2, Name) + dbo.WordCount(@Word2, Description)
  + ... AS Rank
FROM Product
ORDER BY Rank DESC;

Name 權重 3，Description 權重 1 (可依業務調整)。

ALL-Word：加權相乘 (任一詞為 0 → Rank=0 → 排除)

SELECT Name,
    (3*WC(@W1,Name)+WC(@W1,Desc)) *
    CASE WHEN @W2 IS NULL THEN 1 ELSE 3*WC(@W2,Name)+WC(@W2,Desc) END *
    CASE WHEN @W3 IS NULL THEN 1 ELSE ... END
    ... AS Rank
FROM Product
ORDER BY Rank DESC;

替代寫法：未出現詞給 -1000 權重 → Rank 為負 → 過濾。

分頁：臨時表 + ROW_NUMBER / IDENTITY 排序

DECLARE @Products TABLE (
    RowNumber SMALLINT IDENTITY(1,1),
    ID INT, Name VARCHAR(50), Description VARCHAR(1000), Rank INT
);

-- 插入排序後結果
INSERT INTO @Products ... ORDER BY Rank DESC;

-- 取總數
SELECT @HowManyResults = COUNT(*) FROM @Products WHERE Rank > 0;

-- 分頁回傳
SELECT * FROM @Products
WHERE Rank > 0
  AND RowNumber BETWEEN (@PageNumber-1)*@ProductsPerPage+1 AND @PageNumber*@ProductsPerPage
ORDER BY Rank DESC;

完整預存程序碼結構

CREATE PROCEDURE SearchCatalog
    @PageNumber TINYINT,
    @ProductsPerPage TINYINT,
    @HowManyResults SMALLINT OUTPUT,
    @AllWords BIT,           -- 1=ALL, 0=ANY
    @Word1 VARCHAR(15)=NULL, @Word2 VARCHAR(15)=NULL, @Word3 VARCHAR(15)=NULL,
    @Word4 VARCHAR(15)=NULL, @Word5 VARCHAR(15)=NULL
AS
BEGIN
    -- 1. 依 @AllWords 分支 INSERT 到 @Products (含 Rank 計算)
    -- 2. 統計 @HowManyResults
    -- 3. 分頁 SELECT 回傳
END

**核心亮點：** UDF 高效計頻、權重靈活配置、ALL/ANY 邏輯清晰分離、資料層分頁避免海量資料傳輸。可直接擴展為生產級搜尋服務。