sql for循環(huán)的實際案例分享

在SQL中，F(xiàn)OR循環(huán)并不是一個內置的功能

1. 使用WITH RECURSIVE計算階乘：

WITH RECURSIVE factorial_cte (n, result) AS (
    SELECT 0, 1
    UNION ALL
    SELECT n + 1, result * (n + 1) FROM factorial_cte WHERE n < 5
)
SELECT result FROM factorial_cte WHERE n = 5;

2. 生成指定范圍內的數(shù)字序列：

WITH RECURSIVE numbers_cte (number) AS (
    SELECT 1
    UNION ALL
    SELECT number + 1 FROM numbers_cte WHERE number < 10
)
SELECT number FROM numbers_cte;

3. 計算斐波那契數(shù)列：

WITH RECURSIVE fibonacci_cte (n, value) AS (
    SELECT 0, 0
    UNION ALL
    SELECT 1, 1
    UNION ALL
    SELECT n + 1, value + LAG(value) OVER (ORDER BY n) FROM fibonacci_cte WHERE n < 10
)
SELECT value FROM fibonacci_cte ORDER BY n;

4. 遍歷表中的層次結構數(shù)據(jù)（例如，組織結構）：

WITH RECURSIVE org_hierarchy_cte (employee_id, manager_id, employee_name, level) AS (
    SELECT employee_id, manager_id, employee_name, 1
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.employee_name, oh.level + 1
    FROM employees e
    JOIN org_hierarchy_cte oh ON e.manager_id = oh.employee_id
)
SELECT employee_name, level FROM org_hierarchy_cte ORDER BY level, employee_name;

這些示例展示了如何使用遞歸公共表表達式（CTE）來模擬FOR循環(huán)的行為。請注意，這些查詢可能需要根據(jù)您的數(shù)據(jù)庫系統(tǒng)進行調整。