SQL Server按顺序加入 [英] SQL Server Join In Order

问题描述

我在输入中有2个字符串，例如'1,5,6'和'2,89,9'，且元素数相同(3或加号). 我想要的那两个字符串作为纵坐标联接"

I have 2 string in input for example '1,5,6' and '2,89,9' with same number of element (3 or plus). Those 2 string i want made a "ordinate join" as

1   2
5   89
6   9

我想分配一个行号，并在2个结果集之间建立联接

i have think to assign a rownumber and made a join between 2 result set as

SELECT a.item, b.item  FROM 
  (
  SELECT  
  ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS rownumber,
  *  FROM dbo.Split('1,5,6',',')
  ) AS a
  INNER JOIN   
  (
  SELECT  
  ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS rownumber,
  *  FROM dbo.Split('2,89,9',',')
 ) AS b ON a.rownumber = b.rownumber 

这是最佳实践吗?

推荐答案

当dbo.Split()返回数据集时，您不能做的任何事情都可以绝对确定地分配所需的row_number(基于其在字符串中的顺序).如果没有真正与数据相关的ORDER BY，SQL永远不会保证进行排序.

When dbo.Split() returns the data-set, nothing you do can assign the row_number you want (based on their order in the string) with absolute certainty. SQL never guarantees an ordering without an ORDER BY that actually relates to the data.

使用(SELECT 0)进行排序的技巧，可能 经常 得到正确的值.可能经常非常.但这是从不 保证 .偶尔，您 会 得到错误的订单.

With you trick of using (SELECT 0) to order by you may often get the right values. Probably very often. But this is never guaranteed. Once in a while you will get the wrong order.

您最好的选择是重新编码dbo.Split()以在解析字符串时分配一个row_number.只有这样，您才能100％地确定row_number确实与列表中该项目的位置相对应.

Your best option is to recode dbo.Split() to assign a row_number as the string is parsed. Only then can you know with 100% certainty that the row_number really does correspond to the item's position in the list.

然后按照您的建议加入他们，并获得想要的结果.

Then you join them as you suggest, and get the results you want.

除此之外，这个主意对我来说似乎还不错.尽管您可能希望考虑FULL OUTER JOIN，如果一个列表可以比另一个列表长.

Other than that, the idea does seem fine to me. Though you may wish to consider a FULL OUTER JOIN if one list can be longer than the other.

这篇关于SQL Server按顺序加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！