SQL 經典語句大全：從基礎查詢到進階技巧

前言

在資料庫開發與維護過程中，掌握一套高效、常用的 SQL 語句能大幅提升工作效率。本文彙整了針對 SQL Server 的經典語句，涵蓋表結構查詢、交叉表、分頁、隨機排序、日期維度表、字串搜尋、資料庫比較、行列轉換、動態 SQL、加密、備份還原等實用場景，供開發人員參考與收藏。

一、查詢表結構與欄位詳細資訊

以下語句可列出資料庫中所有使用者資料表的欄位名稱、資料類型、長度、是否為主鍵、身分識別欄、允許空值、預設值及說明等資訊：

SELECT 
    表名 = CASE WHEN a.colorder = 1 THEN d.name ELSE '' END,
    表說明 = CASE WHEN a.colorder = 1 THEN ISNULL(f.value, '') ELSE '' END,
    字段序號 = a.colorder,
    字段名 = a.name,
    標識 = CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END,
    主鍵 = CASE WHEN EXISTS(
        SELECT 1 FROM sysobjects 
        WHERE xtype = 'PK' AND name IN (
            SELECT name FROM sysindexes 
            WHERE indid IN (
                SELECT indid FROM sysindexkeys 
                WHERE id = a.id AND colid = a.colid
            )
        )
    ) THEN '√' ELSE '' END,
    類型 = b.name,
    占用字節數 = a.length,
    長度 = COLUMNPROPERTY(a.id, a.name, 'PRECISION'),
    小數位數 = ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0),
    允許空 = CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END,
    預設值 = ISNULL(e.text, ''),
    字段說明 = ISNULL(g.[value], '')
FROM syscolumns a
LEFT JOIN systypes b ON a.xtype = b.xusertype
INNER JOIN sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties'
LEFT JOIN syscomments e ON a.cdefault = e.id
LEFT JOIN sysproperties g ON a.id = g.id AND a.colid = g.smallid
LEFT JOIN sysproperties f ON d.id = f.id AND f.smallid = 0
ORDER BY a.id, a.colorder;

若需查詢特定表，可在 WHERE 子句加上 d.name = '表名'。

二、交叉表（行列轉換）實作

1. 固定列數的交叉表

適用於已知類別（如科目）的情況：

SELECT 
    name,
    SUM(CASE subject WHEN '數學' THEN source ELSE 0 END) AS '數學',
    SUM(CASE subject WHEN '英語' THEN source ELSE 0 END) AS '英語',
    SUM(CASE subject WHEN '語文' THEN source ELSE 0 END) AS '語文'
FROM test
GROUP BY name;

2. 動態列數的交叉表

當類別不固定時，可使用動態 SQL 生成列：

DECLARE @sql VARCHAR(8000);
SET @sql = 'SELECT name,';
SELECT @sql = @sql + 'SUM(CASE subject WHEN ''' + subject + ''' THEN source ELSE 0 END) AS ''' + subject + ''','
FROM (SELECT DISTINCT subject FROM test) AS a;
SET @sql = LEFT(@sql, LEN(@sql) - 1) + ' FROM test GROUP BY name';
EXEC(@sql);

三、SQL Server 分頁方案效能比較

針對大量資料分頁，常見三種方案效能由高到低：

1. 方案二：ID 大於子查詢最大值 + TOP（最高效，需動態拼接 SQL）
2. 方案一：NOT IN + TOP（次之，亦需動態 SQL）
3. 方案三：游標存儲過程（最通用但效能最差）

建議在實際專案中依據資料量、主鍵類型、是否需動態 SQL 等因素選擇。

方案二範例：

SELECT TOP 10 *
FROM TestTable
WHERE ID > (
    SELECT MAX(ID) FROM (
        SELECT TOP 20 ID FROM TestTable ORDER BY ID
    ) AS T
)
ORDER BY ID;

四、隨機排序與日期維度表

隨機排序

SELECT * FROM Northwind..Orders ORDER BY NEWID();
SELECT TOP 10 * FROM Northwind..Orders ORDER BY NEWID();

生成日期維度表

可用於報表、數據倉庫的時間維度：

CREATE TABLE [dbo].[time_dimension] (
    [time_id] [int] IDENTITY(1,1) NOT NULL,
    [the_date] [datetime] NULL,
    [the_day] [nvarchar](15) NULL,
    [the_month] [nvarchar](15) NULL,
    [the_year] [smallint] NULL,
    [day_of_month] [smallint] NULL,
    [week_of_year] [smallint] NULL,
    [month_of_year] [smallint] NULL,
    [quarter] [nvarchar](2) NULL,
    [fiscal_period] [nvarchar](20) NULL
);

DECLARE @dDate SMALLDATETIME = '2002-01-01';
WHILE @dDate < '2004-12-31'
BEGIN
    INSERT INTO time_dimension (the_date, the_day, the_month, the_year, day_of_month, week_of_year, month_of_year, quarter)
    VALUES (
        @dDate,
        DATENAME(dw, @dDate),
        DATENAME(mm, @dDate),
        DATENAME(yy, @dDate),
        DATENAME(dd, @dDate),
        DATENAME(week, @dDate),
        DATEPART(month, @dDate),
        'Q' + CAST(DATENAME(quarter, @dDate) AS VARCHAR(1))
    );
    SET @dDate = DATEADD(day, 1, @dDate);
END;

五、跨資料庫搜尋字串、比較表結構

搜尋某字串存在於哪個表的哪個欄位

DECLARE @str VARCHAR(100) = 'White';
DECLARE @s VARCHAR(8000);
DECLARE tb CURSOR LOCAL FOR
SELECT 'IF EXISTS(SELECT 1 FROM [' + b.name + '] WHERE [' + a.name + '] LIKE ''%' + @str + '%'') PRINT ''所在的表及欄位: [' + b.name + '].[' + a.name + ']'''
FROM syscolumns a JOIN sysobjects b ON a.id = b.id
WHERE b.xtype = 'U' AND a.status >= 0 AND a.xusertype IN (175, 239, 231, 167);
OPEN tb;
FETCH NEXT FROM tb INTO @s;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@s);
    FETCH NEXT FROM tb INTO @s;
END;
CLOSE tb; DEALLOCATE tb;

比較兩個資料庫的表結構差異

使用暫存表儲存兩邊結構，再以 FULL JOIN 比對差異，可檢測缺少的表、欄位、類型、長度、主鍵、預設值等差異。

六、行列轉換進階：列轉行、行轉列、多值合併

• 列轉行：使用 CASE WHEN 配合動態 SQL，將科目欄位轉為多列。
• 行轉列：利用 UNION ALL 將多欄位合併為單欄位多行。
• 多值合併：自訂函數 fmerg 將同一 ID 的多行 PID 以逗號串聯。

七、動態 SQL 執行與參數化

當表名、欄位名為變數時，必須使用動態 SQL：

DECLARE @fname VARCHAR(20) = '[name]';
EXEC('SELECT ' + @fname + ' FROM sysobjects');
-- 或使用 sp_executesql 取得輸出參數
DECLARE @num INT, @sqls NVARCHAR(4000);
SET @sqls = 'SELECT @a = COUNT(*) FROM ' + @servername + '.a.dbo.b';
EXEC sp_executesql @sqls, N'@a INT OUTPUT', @num OUTPUT;
SELECT @num;

八、各資料庫取前 N 筆語法差異

九、生成資料表建立腳本與 Insert 語句

系統預存程序 SP_GET_TABLE_INFO 可依表名產生 CREATE TABLE 腳本；spGenInsertSQL 與 proc_insert 可根據現有資料生成 INSERT 語句，方便資料遷移與備份。

十、金額小寫轉大寫函數

函數 dbo.L2U 接受數值與轉換類型參數，可將數字金額轉為中文大寫（如「壹佰貳拾叁元整」），支援簡繁體。

十一、常用實用片段彙整

• 複製表結構：SELECT * INTO b FROM a WHERE 1<>1
• 複製資料：INSERT INTO b(a,b,c) SELECT d,e,f FROM a
• 外部連接刪除：DELETE FROM info WHERE NOT EXISTS (SELECT * FROM infobz WHERE info.infid = infobz.infid)
• 查詢第 10-20 筆：SELECT TOP 10 * FROM (SELECT TOP 20 * FROM employees ORDER BY employeeid) a ORDER BY employeeid DESC
• 遞迴查詢樹狀結構：使用 table-valued function 搭配 WHILE @@ROWCOUNT > 0 迭代子節點或父節點。
• 編碼規則轉換函數 dbo.f_ChangeCodeRule：支援編碼長度擴充、縮減、填充字元、位置指定。

十二、漢字筆劃與拼音首字母計算

利用 SQL Server 的中文排序規則 Chinese_PRC_Stroke_CS_AS_KS_WS 與 Chinese_PRC_CS_AS_KS_WS，配合 Unicode 編碼範圍，可建立對應表並撰寫函數 fun_getbh（筆劃）與 fun_getPY（拼音首字母），實現純 SQL 計算。

十三、單一欄位加密/解密

建立視圖 v_rand 產生隨機字元，函數 f_jmstr 使用 XOR 運算對字串逐字元加密/解密，最後一字元存放金鑰，實現簡易對稱加密。