SQL Server T-SQL高级查询实战指南

基本常用查询与条件过滤

T-SQL 的基础查询涉及去重、统计、别名定义以及复杂的条件判断。正确使用 GROUP BY 与 HAVING 是实现数据初步聚合的关键。

第1步：执行基础筛选与统计

目的：根据特定条件抽取数据并进行计数或去重处理。

• 操作入口：SELECT 语句配合 WHERE 子句。
• 关键参数：DISTINCT 用于去除重复值；COUNT() 进行统计；BETWEEN ... AND 定义范围；LIKE 实现模糊匹配。
• 完成状态：返回满足条件的唯一记录集或统计结果。

-- distinct 过滤重复
select distinct sex from student;
-- between 范围查询
select * from student where id between 2 and 5;
-- like 模糊查询支持通配符
select * from student where name like '%[a][o]%';

第2步：分组统计与后处理过滤

目的：对数据进行分类汇总，并对分组后的结果进行二次筛选。

• 操作入口：GROUP BY 指定分组列，HAVING 替代 WHERE 过滤聚合值。
• 关键参数：COUNT(*) 或列名；HAVING 后的聚合函数条件。
• 完成状态：输出按条件分组的统计数据，且符合聚合筛选规则。

-- having 过滤分组后的记录条数
select count(*), age from student group by age having count(age) >= 2;

嵌套子查询与关联逻辑

子查询是解决复杂依赖关系的核心手段，分为标量子查询、表子查询及相关/非相关查询。

第3步：构建 FROM 型表子查询

目的：将子查询的结果集视为临时表供外层查询使用。

• 操作入口：FROM (subquery) alias 结构。
• 注意：子查询必须赋予别名，且通常需包含主键或唯一标识以便外层引用。

select * from (
    select id, name from student where sex = 1
) t where t.id > 2;

第4步：使用 IN、NOT IN 与 EXISTS 关联

目的：基于子查询结果的成员资格判断数据归属。

• IN/NOT IN：适用于列值集合判断，要求子查询仅返回一列。NOT IN 遇到 NULL 值可能产生意外结果。
• EXISTS/NOT EXISTS：适用于相关子查询，检查内部是否有记录返回。通常性能优于 IN，特别是当内部表数据量巨大时。

-- in 子查询示例
select * from student where cid in (
    select id from classes where id > 2 and id < 4
);
-- exists 相关子查询示例
select * from student s where exists (
    select * from classes c where c.id = s.cid and c.id = 3
);

聚合扩展与立方体汇总

除了基础的 SUM 和 COUNT，SQL Server 提供了更强大的多维分析功能。

第5步：利用 CUBE 生成多维报表

目的：一次性生成所有可能的维度组合汇总数据。

• 操作入口：GROUP BY column WITH CUBE。
• 说明：比传统的 COMPUTE BY 更简洁，直接返回完整的结果集，包含总计行和各类小计行。

select count(*), sex from student group by sex with cube;

排序与分析函数（Window Functions）

T-SQL 的排序函数允许在不破坏数据行的情况下计算序号，常用于排名、分页和累计计算。

第6步：选择合适的排名策略

目的：根据业务需求生成连续序号、跳跃排名或密集排名。

• ROW_NUMBER()：无论是否并列，序号严格递增不重复。
• RANK()：并列时占用相同名次，但后续名次跳过（如 1, 2, 2, 4）。
• DENSE_RANK()：并列时占用相同名次，后续名次不跳过（如 1, 2, 2, 3）。

-- row_number 生成不重复递增序号
select s.id, s.name, row_number() over(order by s.id) as number 
from student s;

第7步：结合 PARTITION BY 进行组内排序

目的：在各自组别内独立进行排名或分段。

• 操作入口：在窗口函数中添加 OVER(PARTITION BY column)。
• 适用场景：获取每个班级内成绩最高的学生，而非全校最高。

-- 按班级名称分区，组内按id排序
select s.id, s.name, c.name, 
       dense_rank() over(partition by c.name order by s.id) as rank 
from student s, classes c where s.cid = c.id;

集合运算与公用表表达式（CTE）

处理多表结果合并及提升复杂查询的可读性。

第8步：执行集合并集与交集运算

目的：合并、对比两组查询结果。

• UNION ALL：保留重复项并集；UNION：去重并集。
• INTERSECT：取交集（两边都有的记录）。
• EXCEPT：取差集（左边有右边没有的记录）。

-- 减集运算：列出名字以ja开头但不以jas开头的学生
select * from student where name like 'ja%'
except
select * from student where name like 'jas%';

第9步：定义并使用 CTE

目的：为长查询创建临时命名结果集，支持自引用或多次引用。

• 语法：WITH cte_name AS (subquery) SELECT * FROM cte_name。
• 优势：比子查询更具可读性，便于递归查询和逻辑分层。

with statNum(id, num) as 
(
    select cid, count(*) from student where id > 0 group by cid
)
select id, num from statNum order by id;

内置函数与自定义函数应用

T-SQL 内置了大量针对数据转换、安全和日期的函数，开发者亦可封装逻辑。

第10步：调用日期与字符串处理函数

• 日期：DATEADD（增减时间）、DATEDIFF（计算差值）、GETDATE（当前时间）。
• 字符串：REPLACE 替换内容，STUFF 删除指定长度并插入新字符，CONCAT 或 + 拼接。

-- 添加天数和查找索引
select dateAdd(day, 3, getDate());
select charIndex('o', 'hello world');

第11步：构建用户自定义函数 (UDF)

目的：复用特定业务逻辑（如金额计算、格式化）。

• 标量函数：返回单个值，类似 Excel 公式。
• 表值函数：返回表结构，可像表一样参与 JOIN。
• 权限控制：使用 EXECUTE AS CALLER 明确权限上下文。

if (object_id('fun_add', 'fn') is not null)
    drop function fun_add
go
create function fun_add(@num1 int, @num2 int)
    returns int
as
    begin
        return coalesce(@num1,0) + coalesce(@num2,0);
    end
go
-- 调用
select dbo.fun_add(id, age) from student;

以上就是 SQL Server T-SQL 高级查询的详细内容，更多关于 T-SQL 进阶资料请关注本站其它相关文章！