SQL Server 高级查询语句与函数全面解析

基本常用查询与条件过滤

掌握基础的 SELECT 语句及其修饰符是进行数据库检索的前提。通过 DISTINCT 关键字可以去除结果集中的重复行，而 COUNT、MAX、MIN 等聚合函数则用于统计与数值计算。在使用 TOP 限制返回行数时，需注意其针对的是前 N 条记录而非百分比。

1. 列与别名处理：使用 AS 关键字或直接在列名后指定别名，可提升结果集的可读性。支持对单列、多列及整个表进行重命名。
2. 列运算与拼接：支持在 SELECT 列表中直接进行算术运算或使用 + 号拼接字符串（需注意 NULL 值的影响）。
3. WHERE 条件过滤：利用比较运算符（=, >, <, >=, <=, <>）筛选数据。逻辑运算符 AND/OR 用于组合多个条件。BETWEEN 适用于区间查找，LIKE 结合通配符（%、_、[ ]、[^]）实现模糊匹配。
4. 空值处理：必须使用 IS NULL 或 IS NOT NULL 判断字段是否为空，不能使用 = NULL。

SELECT id, name '名称', sex 性别 FROM student;

SELECT * FROM student WHERE age IS NULL;

嵌套子查询与集合运算

子查询允许将一个查询的结果作为另一个查询的条件或数据源。根据位置不同，主要分为 FROM 子句子查询、WHERE 子句相关/非相关子查询以及 EXISTS 检查。

• FROM 子句子查询：将内部查询结果视为临时表，通常需分配别名。适用于需要基于聚合结果进行再次过滤的场景。
• IN / NOT IN：当子查询返回一列值时，使用 IN 判断外部字段是否存在于该列表中。注意处理 NULL 值可能导致的逻辑差异。
• EXISTS / NOT EXISTS：用于检查子查询是否返回任何行。相比 IN，EXISTS 通常在关联子查询中性能更优，因为它在找到第一匹配行后即可停止扫描。
• SOME / ANY / ALL：用于与标量子查询或列表进行比较。ALL 表示大于所有值（即最大值），ANY/SOME 表示大于任意一个值（即最小值）。

-- 使用 EXISTS 查询有对应班级的学生
SELECT * FROM student s
WHERE EXISTS (
    SELECT 1 FROM classes c WHERE c.id = s.cid
);

聚合汇总与 Cube 扩展

GROUP BY 用于将数据按指定列分组，配合 HAVING 子句对分组后的 aggregate 结果进行过滤。相比于 WHERE（过滤行），HAVING 过滤的是组。

对于复杂的多维汇总需求，SQL Server 提供了 COMPUTE BY 和 WITH CUBE 选项。虽然 COMPUTE BY 会在主结果集之外生成额外的汇总行，但在现代开发中，推荐优先使用 WITH CUBE 或 WITH ROLLUP，因为它们直接返回结构化的结果集，便于应用程序处理。

SELECT count(*), sex FROM student GROUP BY sex WITH CUBE;

窗口函数与排序技巧

窗口函数（Window Functions）允许在不减少结果集行数的情况下，对数据进行排名、聚合或移动平均计算。其核心在于 OVER 子句，可包含 PARTITION BY（分组）和 ORDER BY（排序）。

• ROW_NUMBER()：为每一行分配唯一的连续整数序号，不考虑并列情况。
• RANK()：产生跳跃式排名。若两行值相同，则排名相同，下一排名会跳过相应数量。
• DENSE_RANK()：产生连续排名。即使值相同，后续排名也不跳号，保持紧密连续。
• NTILE(n)：将有序分区内的行划分为大致相等的 n 个桶，并分配桶编号。

-- 按班级分组，每组内按ID排名
SELECT s.id, s.name, c.name,
       ROW_NUMBER() OVER (PARTITION BY c.name ORDER BY s.id) as rn
FROM student s JOIN classes c ON s.cid = c.id;

多种连接查询的应用

连接查询（Join）用于关联多个表以获取综合信息。正确理解不同 Join 类型的行为至关重要：

• INNER JOIN：仅返回两个表中联结字段相等的行（交集）。
• LEFT JOIN：返回左表的所有行，即使右表中没有匹配。右表未匹配的列显示为 NULL。
• RIGHT JOIN：与 LEFT JOIN 相反，返回右表所有行。
• CROSS JOIN：产生笛卡尔积，即左表每一行与右表每一行组合。通常需配合 WHERE 使用以避免结果爆炸。
• 自连接：同一张表自我连接，常用于层级数据（如员工与上级）或对比同类数据。

-- 左连接示例
SELECT s.id, s.name, c.id, c.name 
FROM student s 
LEFT JOIN classes c ON s.cid = c.id;

公用表表达式 (CTE)

公用表表达式 (CTE) 是一种命名的临时结果集，仅在单个语句执行期间存在。它的主要优势在于提高 SQL 代码的可读性和模块化程度，尤其适合递归查询或多层嵌套子查询的重构。

CTE 的定义以 WITH 关键字开始，后跟 CTE 名称、可选的列名列表以及 AS 括号内的查询定义。定义后可像普通表一样在后续的 SELECT、INSERT、UPDATE 或 DELETE 语句中引用。

WITH statNum(id, num) AS (
    SELECT cid, COUNT(*)
    FROM student
    WHERE id > 0
    GROUP BY cid
)
SELECT MAX(id), AVG(num) FROM statNum;

常用内置函数详解

SQL Server 提供了丰富的内置函数库，覆盖数学、日期、字符串、元数据及安全等多个维度。

日期与时间函数

使用 DATEADD、DATEDIFF 进行日期加减与差值计算。DATENAME 返回日期的文本部分（如月份名称），DATEPART 返回整数值。GETDATE 获取当前系统日期，GETUTCDATE 获取 UTC 时间。

字符串与数学函数

STUFF 用于插入、删除或替换指定长度的字符序列，SUBSTRING 用于截取。REPLICATE 重复字符串。ROUND 进行四舍五入或截断。RAND 生成随机数。PATINDEX 返回首次出现模式的起始位置。

系统与安全函数

USER_NAME、SUSER_SNAME 分别返回当前用户和登录名的安全描述。@@IDENTITY 返回最后插入的标识值。@@ERROR 返回最近执行的 Transact-SQL 语句的错误号。ISNULL 或 COALESCE 可用于处理 NULL 值的替代方案。

1. 查看元数据：通过 OBJECT_ID、COL_NAME、TYPE_NAME 等函数动态获取表结构和类型信息。
2. 配置与环境：利用 @@SERVERNAME、@@SERVICENAME、@@LANGUAGE 等了解服务器环境配置。

自定义函数创建与管理

用户自定义函数 (UDF) 允许封装可复用的逻辑。SQL Server 支持三种类型：标量函数（返回单一值）、内联表值函数（返回表变量，无 BEGIN...END 块）和多语句表值函数。

创建函数时需遵循严格语法：CREATE FUNCTION function_name (@param datatype) RETURNS return_type AS BEGIN ... END。标量函数可在 SELECT 列表中调用；表值函数可从 FROM 子句中引用，如同表或视图一般。维护时可先 DROP 再 CREATE，或使用 ALTER 修改现有定义。

IF (OBJECT_ID('fun_add', 'FN') IS NOT NULL)
    DROP FUNCTION fun_add
GO

CREATE FUNCTION dbo.fun_add(@num1 INT, @num2 INT)
RETURNS INT
AS
BEGIN
    DECLARE @result INT;
    SET @result = ISNULL(@num1, 0) + ISNULL(@num2, 0);
    RETURN @result;
END
GO

-- 调用示例
SELECT dbo.fun_add(id, age) FROM student;

以上就是 SQL Server 高级查询语句与函数的详细内容，更多关于 SQL Server 数据库开发与优化的资料请关注本站其它相关文章！