T-SQL“动态”加入

前端之家收集整理的这篇文章主要介绍了T-SQL“动态”加入前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
给定以下带有单个char(1)列的sql Server表: @H_301_2@Value ------ '1' '2' '3'

如何在T-sql中获得以下结果?

@H_301_2@Result ------ '1+2+3' '1+3+2' '2+1+3' '2+3+1' '3+2+1' '3+1+2'

这也需要是动态的,所以如果我的表只保存行’1’和’2′,我期望:

@H_301_2@Result ------ '1+2' '2+1'

看起来我应该能够使用CROSS JOIN来做到这一点,但由于我不知道会提前有多少行,所以我不确定CROSS JOIN会多少次回到自己身上……?

@H_301_2@SELECT a.Value + '+' + b.Value FROM MyTable a CROSS JOIN MyTable b WHERE a.Value <> b.Value

在任何给定时间总是会有少于10行(实际上更像是1-3行).我可以在sql Server中即时执行此操作吗?

编辑:理想情况下,我希望这发生在单个存储过程中,但如果我必须使用另一个proc或一些用户定义的函数解决这个问题,我就可以了.

解决方法

sql将计算排列而不重复: @H_301_2@WITH recurse(Result,Depth) AS ( SELECT CAST(Value AS VarChar(100)),1 FROM MyTable UNION ALL SELECT CAST(r.Result + '+' + a.Value AS VarChar(100)),r.Depth + 1 FROM MyTable a INNER JOIN recurse r ON CHARINDEX(a.Value,r.Result) = 0 ) SELECT Result FROM recurse WHERE Depth = (SELECT COUNT(*) FROM MyTable) ORDER BY Result

如果MyTable包含9行,则计算需要一些时间,但它将返回362,880行.

更新说明:

WITH语句用于定义recursive common table expression.实际上,WITH语句多次循环执行UNION,直到递归完成.

sql的第一部分设置起始记录.假设在MyTable中有3行名为“A”,“B”和“C”,这将生成以下行:

@H_301_2@Result Depth ------ ----- A 1 B 1 C 1

然后下一个sql块执行第一级递归:

@H_301_2@SELECT CAST(r.Result + '+' + a.Value AS VarChar(100)),r.Result) = 0

这将获取到目前为止生成的所有记录(将在recurse表中)并再次将它们连接到MyTable中的所有记录. ON子句过滤MyTable中的记录列表,仅返回此行排列中不存在的记录.这将导致这些行:

@H_301_2@Result Depth ------ ----- A 1 B 1 C 1 A+B 2 A+C 2 B+A 2 B+C 2 C+A 2 C+B 2

然后递归循环再次给出这些行:

@H_301_2@Result Depth ------ ----- A 1 B 1 C 1 A+B 2 A+C 2 B+A 2 B+C 2 C+A 2 C+B 2 A+B+C 3 A+C+B 3 B+A+C 3 B+C+A 3 C+A+B 3 C+B+A 3

此时,递归停止,因为UNION不再创建任何行,因为CHARINDEX将始终为0.

最后一个sql过滤所有生成的行,其中计算的深度列与MyTable中的记录数匹配.抛出除最后一次递归深度生成的行之外的所有行.所以最终的结果将是这些行:

@H_301_2@Result ------ A+B+C A+C+B B+A+C B+C+A C+A+B C+B+A

猜你在找的MsSQL相关文章