没有比重复更困难的了 如果你莋得不好，那真是笨拙 当它完好完成时，就像一个小波像波，诗本本身

• 在SQL语句中循环时的
• 注意特别注意无限循环的问题 ！

只要在这裏参与快速问答论坛，就会看到一些常见的主题这些主题会在问题时间和时间上显示出一些常见主题。

在SQL主题中一个主题特别常见，咜通常像"如何在SQL中编写循环以获取"一样，后面的任何结果都试图实现

本文试图根据你不需要循环的原则，为这种类型的问题提供一些潛在的解决方案

所以我主要针对 SQL"新手"，显示了 SQL Server的许多特性是如何很少需要的 但是，其中包含一些技巧中间用户可能会发现有用的。 峩使用工作示例来解释每个备选方案

它看起来很明显，但正式的定义永远不会受到影响：

在计算机程序设计中循环是连续重复的指令序列，直到达到某一条件为止 一般来说，如获取数据项和更改数据然后检查计数器是否达到规定的数目。 如果没有序列中的下一个指令是返回到序列中第一个指令的指令，并重复序列 如果已经达到条件，则下一条指令"通过"将指向循环外的下一条指令或者分支 循环昰编写程序时常用的基本编程思想。[

我将在"gotcha"部分中偶尔提到的其他内容是"无限循环" 下面是一个定义：

无限循环是缺少正常退出例程的一個。 结果是循环不断重复直到操作系统感知它并用错误终止程序或者在某些其他事件发生之前终止该程序。/

为什么我们认为需要一个循環

许多使用关系数据库的新手都来自程序编程背景。 例如许多学生在"高校"上学习的学生都不能自然死亡；那些公司只是不能提升他们嘚技能。

使用这种背景循环往往是对常见问题的一个自然答案，这些问题通常是以下面的术语：

• 对于表格中的每一行"
• 两个日期之间的烸一个日期。"

老实说VB6和VBA几乎强制使用ADO记录集对象的循环，因为"在任何时候记录集对象都只引用集合中的单个记录"[

典型的vb6/vba代码可能看起來像

如果你将生成的序列与交叉 Join 相结合，你可以生成类似于员工 [

设想我们想在接下来的两个星期里生成一个员工名册

我们可以使用一个rCTE苼成日期序列，从今天 14天开始 如果我们把 Join 那个小序列与员工表交叉，我们将从for获得每个员工的每一行 就像这个：

一个完整的日期顺序昰确定从数据集中丢失哪些日期。 上面的例子类似我们将使用一个rCTE生成一个开始日期和结束日期之间所有日期的序列。 对它的第二个查詢将 LEFT OUTER Join 发送到订单表 结果将是所有未放置订单的日期。

 

 
 

 在创建rtc之前你可以能有它的他SQL语句，或者在测试之后你可以能希望将rtc的代码插叺更大的过程。 然后你可能会遇到这里错误消息：
 
 

 关键字'带'附近的语法不正确。 如果这里语句是公用表表达式xmlnamespaces子句或者更改跟踪上下攵子句，则必须用分号终止前一语句
 
 

 这是一个明显的错误消息，解决方法非常简单 只在CTE声明上方的行的末尾放一个分号。 但是如果在CTEの前插入更多代码你必须记住这样做。 MSSQL开发人员的一个常见习惯是将分号放在前面的例子中如上面的例子所示。
 
 

使用SQL窗口函数解决问題

 

 前一节使用的技术将与大多数版本的SQL Server 一起使用但是在发行版中不断改进的一个方面是"窗口功能"。
 
 

 这些通常被称为"通过函数"因为上面使用的关键字。
 
 

 窗口函数是应用于窗口描述符定义的一组行并为基础查询中的每行返回一个值的函数。 窗口描述符的作用是定义函数应應用到的行集[ 
 
 

 这是一个很好的方法，仍然能够获得单个行的详细信息同时也能获得集合信息。
 
 

 我在上面的例子中使用了 ROW_NUMBER() 窗口函数 通過包括分区子句，我可以将行编号应用到每个客户( 不使用程序循环)的每一组数据 "窗口描述符"是 CustomerID。 如果在表的所有行都接收到了行号那麼我们注意到表中的所有行都是行号，而不带窗口描述符的行就是整个表
 
 

 分区通过划分查询结果集划分为分区。 窗口函数将分别应用于烸个分区并为每个分区重新启动计算。[ 
 
 

 这种查询效果良好得到了我们想要的数据，但是如果有一个更简洁的方法来生成额外的数据洏不是 Having，那么就可以创建一个 CTE然后再创建一个独立的连接。 对于 SQL Server 2012 ( 不是速成版) 和更高版本( 包括 2014表示)可以使用额外的SQL窗口函数来实现。
 
 

 这裏查询得到的结果与前面的结果完全相同
 
 

 
 

 注意over子句是如何与以前的ROW_NUMBER() 完全相同的 这是有意义的，因为我们仍然感兴趣的数据我们仍然希朢订单顺序订购。 行号已经消失但是现在我们不使用任何自联接，所以表别名 prev 也消失了
 
 

 功能是为我做的。 查询的那一部分现在说
 
 

这个新的查询很简洁，看起来更容噫看到 滞后( 前向查找等价) 是版本中提供的解析函数之一。

另一个例子- 记住从右边的一个"要求"可以提示你考虑使用循环。

"计算每个产品嘚所有值的总数和平均值"

让我们尝试获得所有产品的细节每产品的总量，每产品的平均数量和每个产品的订单数

看起来很困难，但实際上很简单；我可以使用一些SQL聚合函数和 GROUP BY

但我们也必须显示每单位的数量。库存单位订单单位和结果的重排级别。

当我们将这些字段添加到选择列表中时我们会得到错误

一个解决方案是扩展 GROUP BY 子句以包含我们希望显示的所有其他字段。

或者我们要做一些子查询然后是 Join

咜开始看起来非常混乱 ！

然而，我们可以使用那些聚合函数替代那些聚合函数而把数据划分成我们需要除去的GROUP BY 子句。

我个人认为这看起来更整洁，而且更容易找出正在发生的事情 不要忘记- 我仍然没有必要求助于任何程序循环来获取我需要的数据。 我从一个查询中得到所有需要的信息

你可以在这里阅读更多关于SQL窗口函数的信息- [

初学者遇到的另一个概念是需要列出一组结果。

假设我们被要求按年份获取國家/地区的订单总数 掌握这些信息很简单

但每年的结果是每年一行，每个国家"。"/p>

这不是我们所要做的，更像这样的事情

我可以循环遍历我们的结果以正确的格式构建一个新表。 但你知道我不需要这么做 我可以做一个简单的pivot 查询 [

注意，我调用函数 source的查询非常类似于峩们的原始查询提取数据但是我们丢失了 GROUP BY。

pivot 部分将根据每年的订单来计算我的订单

i.ein子句中的"列"列表。 注意这些年份不是字符串它们玳表最终结果集的列。 pivot 根据该列表旋转数据

换句话说，SQL查找OrderYear中的值即在列列表中输入名称，然后计算 MATCH 上的OrderIDs的数量以确定该值的值。 根据 source的SELECT语句中的其他项对结果进行分区 为了演示这个问题，如果我们从选择中取出它将在 source的整个结果集中进行计数。

伟大- 我们现在可鉯轻松地从我们的数据

总之，对于 pivot 数据你需要一个源查询。 这应包含要在它的上旋转数据的字段以及要将数据分割到的任何字段。 嘫后你需要关键字 pivot 后跟关于如何旋转数据的指令 节必须使用聚合函数，如计数最大 等等，MIN子句将在结果表中定义列 只有这些列，如果没有感兴趣的信息但是重要的是不包括所有可以能的值。

因为列 NAME 不能是所有数字所以我们必须用方括号围绕"列"名称- [] 使它成为有效的列 NAME。 因此

如果你试图打开的值是SQL中的保留字则 true 是相同的，例如

也是 true如果你试图在包含空间上进行 pivot的值，例如

当你从动态查询生成SQL时這变得非常重要。

动态SQL查询和逗号分隔列表

pivot的问题是随着时间的推移未来的年将有订单日期。 这些数据将不会包含在我们的查询中因為我们硬编码了我们想要看到的列。 19961997和 1998.

我们真的不希望每年都更新查询。 如果我们能从数据本身生成这个列表那就很好了。

我需要把那个年份列表用逗号分隔的列表完成的方括号周围的方括号。 但是我不想使用过程循环来构建该列表 ！ 幸运的是有几种方法可以绕过。

我们可以在一个相当简单的查询中使用合并 [

这就给了我们这样的列列表

注意我必须手动添加第一个方括号和最后一个方括号，并且不初始化 @listStr的起始括号( 否则你将得到一个空列 NAME [ ] - 尝试它并查看)。

你还可以使用( SQL 2005自) 和XML来生成逗号分隔列表但是在尝试包括方括号时有点难。 在

现在我的问题是如何将列列表放入我的查询中。 为此我将使用一些动态 SQL。

动态SQL是指在程序执行前由程序以编程方式生成的SQL代码 因此，它是一个非常 flexable [sic] 和强大的工具 可以使用动态sql来完成任务，例如在窗体上添加with子句或者在表单中创建不同的名称 [

我可以使用上面派生的@listStr 變量将SQL查询放置到字符串变量中，以列出所需的列 我把它broken一点，让它更清晰一点

如果你打印出 @sql的内容，你可以看到我们获得了相同的查询( 已经添加了一些空间)

然后，我们可以用这个语句运行查询

如果增加了数据查询仍然会工作，如果为 1999 @listStr 添加数据将自动提取新的列囷 [1998]，[1996][1997] 将被插入到 @sql 变量中。

提示- 注意我说了"如果你把 @sql的内容打印出来你可以看到"。 在包括执行之前最好先打印并检查你正在生成的SQL。 鈳以从消息窗格复制 SQL并直接运行它以查看是否有效。 用这种方式调试它比直接运行它要容易得多 ！

Gotcha - 如何限制所显示列的数目 ！

一段时间の后上面的查询结果会变得有些笨拙。 用户更愿意看到最近 5年的数据( 例如)的声明

还没有必要用循环来做这个，我只是限制了导入 pivot 列列表的数据

它看起来好像这个查询将足够限制列的最近 5年

但如果仔细查看上面为动态SQL生成的查询，列列表如下所示：

他们根本没有按顺序絀来 其实

当in与 ORDER BY 子句一起使用时，结果集仅限于前N 个排序行否则返回未定义的[

所以你必须记住添加一个 ORDER BY 子句。

在视图内联函数。派生表子查询和公用表表达式中，ORDER BY 子句无效除非还指定了 TOP。OFFSET或者 XML

但是，这些方法中的任何一种都将工作：

想象一下你需要用逗号分隔┅组数据的值列表- - 换句话说，你想用一个列来表示一个列

作为示例让我们得到每个订单的产品列表。 产品ID存储在 [Order Details] 表中我们可以使用 FOR XML 和 STUFF 通过订单ID生成这些ID的列表。

如果我们把这些信息放到一个ipqos中我们可以直接从 [Orders] 表中获得它的余的详细信息：

你可能会遇到一种情况，它的Φ有一些非常复杂的SQL这些SQL产生了一些数据。 在这些情况下通常值得考虑使用临时表。表变量或者编写一个函数来返回一个表来简化最終查询

在这里，我不会再讨论这些方法因为它们在其他地方已经被很好地覆盖了。 我只包括这个部分来提醒你可以使用表避免循环 請阅读以下文章以了解更多信息：

我绝对需要一个循环 ！

可能有一段时间你只需要使用循环。 我几乎没有什么可以想到的事情： 操作表架構或者拆分字符串

如果你发现自己在这种情况下，不要直接跳转到使用SQL游标 还有其他的选择。

T-SQL不是用循环性能来完成的[

为什么它是基于设置的技术，所以循环可以能比一种有效的处理数据的方法更加有效

许多编程工具，如. NET 系列语言报表生成器 等等，可以非常高效哋完成这项工作 因此，考虑将数据集传递回应用程序并允许该应用程序执行循环处理。 这尤其是任何视觉增强的true ( 比如 向一个值添加湔零)，该值应留到应用程序的表示层

SQL中的，循环 [ 如果你希望在遍历一组记录时避免锁定表则这里方法非常有用。

 

 实际上我在这里作欺騙当前在T-SQL中没有循环，但是你可以在 [ 
 
 

 下面是使用"循环"来根据分隔符拆分字符串的示例 注- 这不是我的工作- 该示例直接从 [ 
 
 

 你甚至可以在循環中使用像in和in这样的内容来添加功能。
 
 

 

 当然在SQL中还有游标循环。 游标的缺点已经被讨论过数百次我只是在这里重复这些参数是无意义嘚。 这里有一些关于进一步阅读的文章
 
 

 

 希望你已经猜到了，我不喜欢游标 ！
 
 

注意特别小心无限循环 ！

 

 无论使用什么方法无论你是模拟循环还是控制游标的使用，都要特别小心地将循环变量或者条件包含在所有路径上 要将上面的示例转换为无限循环，我只需要将一行移箌错误的位置我们有问题：
 
 

 

 一旦该循环击中了需要跳过的行，因这里变量 @curr 不再增加因为该代码行在内部is语句中。
 
 

 

 我希望我已经演示了 99超过 100在处理SQL查询时不需要使用实际循环。
 
 

 我已经将我的所有示例都限制在 Management Studio中使用SQL查询 我甚至没有接触到数据仓库。商业智能或者多维數据集的使用 如果你对阅读感兴趣，那么你可能希望从以下介绍开始：
 
 

• SQL商务智能分析和数据可视化解决方案的指南

 

 下面是关于进一步阅讀性能考虑的一些建议
 
 

 

 这种类型的文章没有很多原始思想，只有一些经验和大量研究 我试图把我的所有资源放在文章的正文里，但如果你认为我错过了一个或者更好的例子