T-SQL 语言基础：进阶单表查询技巧

目录

1. 介绍
2. 单表查询
   • 按客户 ID 和年份分组的订单计数
   • 去除重复行
   • 获取最近订单
   • 使用窗口函数计算总值和排名
3. 最佳实践
4. 总结
5. 引用
6. 数据库下载

介绍

这篇博客中，我们将探讨常见的 T-SQL 查询技巧。涵盖了从基础到进阶的内容，包括按客户 ID 和年份分组的订单计数、去除重复行、获取最近订单、使用窗口函数计算总值和排名等操作。希望这篇博客为您提供有价值的参考。

单表查询

按客户 ID 和年份分组的订单计数

示例代码

USE TSQLFundamentals2008;

SELECT empid,							-- STEP 5
       YEAR(orderdate) AS OrderYear,
       COUNT(*) AS NumOrders
FROM Sales.Orders						-- STEP 1
WHERE custid = 71						-- STEP 2
GROUP BY empid, YEAR(orderdate)			-- STEP 3
HAVING COUNT(*) > 1						-- STEP 4
ORDER BY empid, OrderYear;				-- STEP 6

解释

• STEP 1: 从 Sales.Orders 表中查询数据。
• STEP 2: 过滤客户 ID 为 71 的订单。
• STEP 3: 按员工 ID 和订单年份分组。
• STEP 4: 仅保留订单数大于 1 的组。
• STEP 5: 选择员工 ID 和订单年份。
• STEP 6: 按员工 ID 和订单年份排序。

去除重复行

示例代码

SELECT DISTINCT empid, YEAR(orderdate) AS OrderYear
FROM Sales.Orders
WHERE custid = 71;

解释

使用 DISTINCT 关键字从结果集中去除重复行，只保留唯一的员工 ID 和订单年份组合。

获取最近订单

示例代码

SELECT TOP 5 orderid, orderdate, custid, empid
FROM Sales.Orders
ORDER BY orderdate DESC, orderid DESC;

解释

使用 TOP 关键字获取最近的 5 个订单，并按订单日期和订单 ID 降序排序。

使用 TOP WITH TIES

SELECT TOP 5 WITH TIES orderid, empid, orderdate, custid
FROM Sales.Orders
ORDER BY orderdate DESC;

解释

WITH TIES 关键字确保如果第 5 行与其他行在排序列上有相同的值，这些行也会被包含在结果集中。

使用窗口函数计算总值和排名

示例代码

SELECT orderid, custid, val,
       SUM(val) OVER() AS totalvalue
FROM Sales.OrderValues;

解释

使用 SUM 窗口函数计算所有订单的总值。

示例代码

SELECT orderid, custid, val,
       SUM(val) OVER() AS totalvalue,
       SUM(val) OVER(PARTITION BY custid) AS custTotalValue
FROM Sales.OrderValues;

解释

使用 PARTITION BY 子句按客户 ID 计算每个客户的订单总值。

使用比例计算

SELECT orderid, custid, val,
       100.0 * val / SUM(val) OVER() AS totalvalue,
       100.0 * val / SUM(val) OVER(PARTITION BY custid) AS custTotalValue
FROM Sales.OrderValues;

解释

计算每个订单值在总值和客户总值中的比例。

使用排名函数

SELECT orderid, custid, val,
       ROW_NUMBER() OVER(ORDER BY val) AS rownum,
       RANK() OVER(ORDER BY val) AS [rank],
       DENSE_RANK() OVER(ORDER BY val) AS [dense_rank],
       NTILE(10) OVER(ORDER BY val) AS [NTILE]
FROM Sales.OrderValues
ORDER BY val;

解释

使用 ROW_NUMBER、RANK、DENSE_RANK 和 NTILE 窗口函数生成行号、排名、密集排名和分组。

最佳实践

避免同时使用 DISTINCT 和 ROW_NUMBER，因为 ROW_NUMBER 函数是在 DISTINCT 子句之前处理的。在这种情况下，DISTINCT 不起任何作用。

示例代码

SELECT DISTINCT val,
       ROW_NUMBER() OVER(ORDER BY val) AS rownum
FROM Sales.OrderValues;

解释

可以使用 GROUP BY 来替代 DISTINCT，因为 GROUP BY 在 SELECT 阶段之前处理。

示例代码

SELECT DISTINCT val,
       ROW_NUMBER() OVER(ORDER BY val) AS rownum
FROM Sales.OrderValues
GROUP BY val;

总结

在这篇博客中，我们探讨了几种常见的 T-SQL 查询技巧，包括按客户 ID 和年份分组的订单计数、去除重复行、获取最近订单、使用窗口函数计算总值和排名等操作。

引用

• Microsoft SQL Server 官方文档
• T-SQL 基础教程

数据库脚本下载

链接：
TSQLFundamentals2008

**喜欢的话,请收藏 | 关注(✪ω✪)** ……**万一有趣的事还在后头呢，Fight!!(ｏ^-^)~''☆ミ☆ミ**……