您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 實用 SQL 技巧大全：數字輔助表、生日計算、工作日統計、分組去重、分頁優化

編輯：MySQL 資料庫相關知識

整理自實戰專案的 MySQL 進階 SQL 技巧，涵蓋數字輔助表構建、不連續 ID 分組填充、生日下次發生日期計算、工作日統計預存程序、常用 DDL/DML 模板、高級集合運算、外連接語法、分組統計規則等，適合開發與 DBA 參考收藏。

一、數字輔助表構建與應用

1. 快速生成數字表（遞歸 CTE 現代寫法，MySQL 8.0+）

WITH RECURSIVE nums(n) AS (
    SELECT 1 UNION ALL SELECT n+1 FROM nums WHERE n < 10000
)
SELECT n FROM nums;

2. 傳統預存程序生成（MySQL 5.7 相容）

CREATE TABLE `num` (`id` INT UNSIGNED NOT NULL PRIMARY KEY);
DELIMITER //
CREATE PROCEDURE pnum(cnt INT UNSIGNED)
BEGIN
    DECLARE i INT UNSIGNED DEFAULT 1;
    INSERT INTO num VALUES (1);
    WHILE i*2 < cnt DO
        INSERT INTO num SELECT i + id FROM num;
        SET i = i * 2;
    END WHILE;
END //
DELIMITER ;
CALL pnum(10000);

3. 不連續 ID 分組（找出連續區間）

-- 假設表 a(id) 值：1,2,3,100,101,110,111
SET @q = 0;
SELECT id, @q:=@q+1 AS cn FROM a;

SET @a = 0;
SELECT MIN(id) AS start_v, MAX(id) AS end_v
FROM (
    SELECT id, cn, id - cn AS diff
    FROM (SELECT id, @a:=@a+1 AS cn FROM a) t
) d
GROUP BY diff;
-- 結果：(1,3), (100,101), (110,111)

4. 不連續值填充（找出缺口）

SELECT a+1 AS start_v,
       (SELECT MIN(a)-1 FROM pincer w WHERE w.a > q.a) AS end_v
FROM pincer q
WHERE NOT EXISTS (SELECT 1 FROM pincer p WHERE p.a = q.a + 1)
  AND a < (SELECT MAX(a) FROM pincer);

二、生日問題：計算下次生日日期

SELECT name, birthday,
       IF(cur > today, cur, nxt) AS next_birthday
FROM (
    SELECT name, birthday, today,
           DATE_ADD(cur, INTERVAL IF(DAY(birthday)=29 AND DAY(cur)=28, 1, 0) DAY) AS cur,
           DATE_ADD(nxt, INTERVAL IF(DAY(birthday)=29 AND DAY(nxt)=28, 1, 0) DAY) AS nxt
    FROM (
        SELECT name, birthday, today,
               DATE_ADD(birthday, INTERVAL diff YEAR) AS cur,
               DATE_ADD(birthday, INTERVAL diff+1 YEAR) AS nxt
        FROM (
            SELECT CONCAT(last_name, ' ', first_name) AS name,
                   birth_date AS birthday,
                   YEAR(NOW()) - YEAR(birth_date) AS diff,
                   NOW() AS today
            FROM employees
        ) a
    ) b
) c;

處理閏年 2/29 生日在非閏年按 2/28 或 3/1 計算的邊界情況。

三、工作日計算預存程序

CREATE PROCEDURE p_get_workdays(s DATETIME, e DATETIME)
BEGIN
    SELECT FLOOR(days/7)*5 + days%7
           - CASE WHEN 6 BETWEEN wd AND wd+days%7-1 THEN 1 ELSE 0 END
           - CASE WHEN 7 BETWEEN wd AND wd+days%7-1 THEN 1 ELSE 0 END
    FROM (SELECT DATEDIFF(e, s)+1 AS days, WEEKDAY(s)+1 AS wd) a;
END;

輸入起訖日期，輸出工作日天數（排除週六日）。

四、常用 DDL/DML 模板速查

操作	SQL
建庫	`CREATE DATABASE dbname;`
刪庫	`DROP DATABASE dbname;`
建表	`CREATE TABLE t(c1 TYPE1 [NOT NULL] [PRIMARY KEY], c2 TYPE2...);`
複製表結構	`CREATE TABLE t_new LIKE t_old;`
複製表數據	`CREATE TABLE t_new AS SELECT * FROM t_old;`
刪表	`DROP TABLE t;`
加欄位	`ALTER TABLE t ADD COLUMN c TYPE;`
加主鍵	`ALTER TABLE t ADD PRIMARY KEY(c);`
建索引	`CREATE [UNIQUE] INDEX idx ON t(col...);`
刪索引	`DROP INDEX idx ON t;`
建視圖	`CREATE VIEW v AS SELECT ...;`

五、高級集合運算

UNION：合併結果集，去重。
UNION ALL：合併，保留重複。
EXCEPT (MySQL 用 NOT IN/NOT EXISTS 替代)：差集。
INTERSECT (MySQL 用 INNER JOIN/IN 替代)：交集。

六、外連接語法總結

類型	語法	結果特徵
LEFT JOIN	`SELECT * FROM a LEFT JOIN b ON a.id = b.id`	左表全量 + 右表匹配/NULL
RIGHT JOIN	`SELECT * FROM a RIGHT JOIN b ON a.id = b.id`	右表全量 + 左表匹配/NULL
FULL JOIN	MySQL 不支援，用 `LEFT JOIN UNION RIGHT JOIN` 模擬	兩表全量