SQL Server高级查询 #

-- 返回单个值的子查询
SELECT 
    name,
    salary,
    (SELECT AVG(salary) FROM users) AS avg_salary
FROM users;

-- 在WHERE中使用
SELECT * FROM users
WHERE salary > (SELECT AVG(salary) FROM users);

-- 返回单列多行
SELECT * FROM users
WHERE department_id IN (SELECT id FROM departments WHERE location = 'Beijing');

-- 使用ANY/SOME
SELECT * FROM users
WHERE salary > ANY (SELECT salary FROM users WHERE department_id = 1);

-- 使用ALL
SELECT * FROM users
WHERE salary > ALL (SELECT salary FROM users WHERE department_id = 2);

-- 返回多行多列
SELECT u.name, o.order_date
FROM users u
INNER JOIN (
    SELECT user_id, order_date 
    FROM orders 
    WHERE status = 'completed'
) o ON u.id = o.user_id;

-- 使用EXISTS
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id 
    AND o.amount > 1000
);

-- 使用NOT EXISTS
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- 相关子查询（引用外部查询）
SELECT 
    name,
    salary,
    (SELECT AVG(salary) FROM users u2 WHERE u2.department_id = u1.department_id) AS dept_avg
FROM users u1;

-- 使用相关子查询更新
UPDATE users u1
SET salary = (
    SELECT AVG(salary) FROM users u2 
    WHERE u2.department_id = u1.department_id
)
WHERE salary < 3000;

-- 使用派生表
SELECT dept_name, avg_salary
FROM (
    SELECT 
        d.name AS dept_name,
        AVG(u.salary) AS avg_salary
    FROM users u
    INNER JOIN departments d ON u.department_id = d.id
    GROUP BY d.name
) AS dept_stats
WHERE avg_salary > 5000;

-- 简单CTE
WITH active_users AS (
    SELECT * FROM users WHERE status = 1
)
SELECT * FROM active_users;

-- 多个CTE
WITH 
active_users AS (
    SELECT * FROM users WHERE status = 1
),
high_salary AS (
    SELECT * FROM active_users WHERE salary > 5000
)
SELECT * FROM high_salary;

-- 使用CTE进行聚合分析
WITH user_stats AS (
    SELECT 
        department_id,
        COUNT(*) AS employee_count,
        AVG(salary) AS avg_salary,
        MAX(salary) AS max_salary,
        MIN(salary) AS min_salary
    FROM users
    GROUP BY department_id
)
SELECT 
    d.name AS department,
    us.employee_count,
    us.avg_salary,
    us.max_salary,
    us.min_salary
FROM user_stats us
INNER JOIN departments d ON us.department_id = d.id
ORDER BY us.avg_salary DESC;

-- 组织架构递归查询
WITH org_hierarchy AS (
    -- 锚点：顶级员工
    SELECT 
        id,
        name,
        manager_id,
        1 AS level,
        CAST(name AS NVARCHAR(1000)) AS path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归：下级员工
    SELECT 
        e.id,
        e.name,
        e.manager_id,
        h.level + 1,
        CAST(h.path + ' > ' + e.name AS NVARCHAR(1000))
    FROM employees e
    INNER JOIN org_hierarchy h ON e.manager_id = h.id
)
SELECT * FROM org_hierarchy
ORDER BY level, path;

-- 递归生成日期序列
WITH date_series AS (
    SELECT CAST('2024-01-01' AS DATE) AS date_value
    
    UNION ALL
    
    SELECT DATEADD(DAY, 1, date_value)
    FROM date_series
    WHERE date_value < '2024-01-31'
)
SELECT date_value FROM date_series
OPTION (MAXRECURSION 100);  -- 默认100，最大32767

-- 使用CTE删除
WITH duplicate_users AS (
    SELECT 
        id,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS row_num
    FROM users
)
DELETE FROM users
WHERE id IN (SELECT id FROM duplicate_users WHERE row_num > 1);

-- 使用CTE更新
WITH user_ranks AS (
    SELECT 
        id,
        salary,
        RANK() OVER (ORDER BY salary DESC) AS salary_rank
    FROM users
)
UPDATE user_ranks
SET salary = salary * 1.1
WHERE salary_rank <= 5;

-- 行号
SELECT 
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM users;

-- 分组行号
SELECT 
    department_id,
    name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM users;

-- 分页
WITH numbered AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS row_num
    FROM users
)
SELECT * FROM numbered
WHERE row_num BETWEEN 4 AND 6;

-- RANK：并列跳过
SELECT 
    name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS rank_val,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_val
FROM users;

-- 结果示例
-- name    | salary | rank_val | dense_rank_val
-- --------|--------|----------|----------------
-- Charlie | 7000   | 1        | 1
-- Jane    | 6000   | 2        | 2
-- David   | 5500   | 3        | 3
-- John    | 5000   | 4        | 4
-- Eve     | 4800   | 5        | 5
-- Bob     | 4500   | 6        | 6
-- Alice   | 4000   | 7        | 7

-- 累计聚合
SELECT 
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total,
    AVG(amount) OVER (ORDER BY order_date) AS running_avg,
    COUNT(*) OVER (ORDER BY order_date) AS running_count
FROM orders;

-- 分组聚合
SELECT 
    department_id,
    name,
    salary,
    SUM(salary) OVER (PARTITION BY department_id) AS dept_total,
    AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
FROM users;

-- 移动平均
SELECT 
    order_date,
    amount,
    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3
FROM orders;

-- 窗口帧类型
/*
ROWS：基于行数
RANGE：基于值范围

帧边界：
PRECEDING：之前
FOLLOWING：之后
CURRENT ROW：当前行
UNBOUNDED PRECEDING：窗口开始
UNBOUNDED FOLLOWING：窗口结束
*/

-- 常用帧定义
SELECT 
    order_date,
    amount,
    -- 从开始到当前
    SUM(amount) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS cum_sum,
    -- 前3行到后1行
    AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING) AS avg_5,
    -- 当前到结束
    SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS remaining
FROM orders;

-- 访问前后行数据
SELECT 
    order_date,
    amount,
    LAG(amount, 1, 0) OVER (ORDER BY order_date) AS prev_amount,
    LEAD(amount, 1, 0) OVER (ORDER BY order_date) AS next_amount,
    amount - LAG(amount, 1, amount) OVER (ORDER BY order_date) AS diff
FROM orders;

-- 计算环比增长
SELECT 
    order_date,
    amount,
    LAG(amount) OVER (ORDER BY order_date) AS prev_amount,
    CAST((amount - LAG(amount) OVER (ORDER BY order_date)) * 100.0 / 
         LAG(amount) OVER (ORDER BY order_date) AS DECIMAL(10,2)) AS growth_rate
FROM orders;

-- 首尾值
SELECT 
    department_id,
    name,
    salary,
    FIRST_VALUE(name) OVER (PARTITION BY department_id ORDER BY salary DESC) AS highest_paid,
    LAST_VALUE(name) OVER (
        PARTITION BY department_id 
        ORDER BY salary DESC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_paid
FROM users;

-- 分组
SELECT 
    name,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile  -- 分成4组
FROM users;

-- 结果
-- name    | salary | quartile
-- --------|--------|----------
-- Charlie | 7000   | 1
-- Jane    | 6000   | 1
-- David   | 5500   | 2
-- John    | 5000   | 2
-- Eve     | 4800   | 3
-- Bob     | 4500   | 3
-- Alice   | 4000   | 4

-- 简单CASE表达式
SELECT 
    name,
    status,
    CASE status
        WHEN 1 THEN 'Active'
        WHEN 0 THEN 'Inactive'
        ELSE 'Unknown'
    END AS status_text
FROM users;

-- 搜索CASE表达式
SELECT 
    name,
    salary,
    CASE 
        WHEN salary >= 6000 THEN 'High'
        WHEN salary >= 4000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_level
FROM users;

-- 条件聚合
SELECT 
    department_id,
    COUNT(*) AS total,
    SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) AS active_count,
    SUM(CASE WHEN status = 0 THEN 1 ELSE 0 END) AS inactive_count,
    AVG(CASE WHEN status = 1 THEN salary END) AS avg_active_salary
FROM users
GROUP BY department_id;

-- 自定义排序
SELECT * FROM users
ORDER BY 
    CASE status
        WHEN 1 THEN 1
        WHEN 0 THEN 2
        ELSE 3
    END,
    salary DESC;

-- 创建示例数据
CREATE TABLE sales (
    year INT,
    quarter NVARCHAR(10),
    amount DECIMAL(10,2)
);

INSERT INTO sales VALUES
    (2023, 'Q1', 1000),
    (2023, 'Q2', 1500),
    (2023, 'Q3', 2000),
    (2023, 'Q4', 2500),
    (2024, 'Q1', 1200),
    (2024, 'Q2', 1800);

-- PIVOT转换
SELECT * FROM 
(
    SELECT year, quarter, amount
    FROM sales
) AS source
PIVOT
(
    SUM(amount) FOR quarter IN ([Q1], [Q2], [Q3], [Q4])
) AS pvt;

-- 结果
-- year | Q1    | Q2    | Q3    | Q4
-- -----|-------|-------|-------|------
-- 2023 | 1000  | 1500  | 2000  | 2500
-- 2024 | 1200  | 1800  | NULL  | NULL

-- 动态列
DECLARE @columns NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

SELECT @columns = STRING_AGG(QUOTENAME(quarter), ',')
FROM (SELECT DISTINCT quarter FROM sales) t;

SET @sql = '
SELECT * FROM 
(
    SELECT year, quarter, amount
    FROM sales
) AS source
PIVOT
(
    SUM(amount) FOR quarter IN (' + @columns + ')
) AS pvt';

EXEC sp_executesql @sql;

-- 创建宽表
CREATE TABLE sales_wide (
    year INT,
    Q1 DECIMAL(10,2),
    Q2 DECIMAL(10,2),
    Q3 DECIMAL(10,2),
    Q4 DECIMAL(10,2)
);

INSERT INTO sales_wide VALUES
    (2023, 1000, 1500, 2000, 2500),
    (2024, 1200, 1800, NULL, NULL);

-- UNPIVOT转换
SELECT year, quarter, amount
FROM sales_wide
UNPIVOT
(
    amount FOR quarter IN (Q1, Q2, Q3, Q4)
) AS unpvt;

-- 结果
-- year | quarter | amount
-- -----|---------|-------
-- 2023 | Q1      | 1000
-- 2023 | Q2      | 1500
-- 2023 | Q3      | 2000
-- 2023 | Q4      | 2500
-- 2024 | Q1      | 1200
-- 2024 | Q2      | 1800

-- 返回第一个查询有但第二个查询没有的行
SELECT name, email FROM users
EXCEPT
SELECT name, email FROM archived_users;

-- 找出只在users中的用户

-- 返回两个查询共有的行
SELECT name, email FROM users
INTERSECT
SELECT name, email FROM vip_users;

-- 找出既是普通用户又是VIP的用户

-- 类似INNER JOIN，但可以应用表值函数
SELECT 
    u.name,
    t.top_order
FROM users u
CROSS APPLY (
    SELECT TOP 1 order_date AS top_order
    FROM orders o
    WHERE o.user_id = u.id
    ORDER BY amount DESC
) t;

-- 使用表值函数
SELECT 
    u.name,
    f.*
FROM users u
CROSS APPLY dbo.fn_GetUserOrders(u.id) f;

-- 类似LEFT JOIN，保留左表所有行
SELECT 
    u.name,
    t.top_order
FROM users u
OUTER APPLY (
    SELECT TOP 1 order_date AS top_order
    FROM orders o
    WHERE o.user_id = u.id
    ORDER BY amount DESC
) t;

-- 没有订单的用户也会返回，top_order为NULL