(递归)SQL 查询而不是循环 [英] (Recursive) SQL query instead of loops

问题描述

我正在寻找获得以下输出的查询:

I'm looking for a query to get the following output:

Id Number
-- ------
 1 241100
 2 241110
 2 241111
 2 241112
 2 241113
 2 241114
 2 241115

表结构:

Id Number From To
-- ------ ---- ----
 1 241100 NULL NULL
 2 241110  111  115

没有从/到范围的行必须返回数字.其他的必须返回数字后跟 SUBSTRING(Number, 1, 3) + <from/to range>

Rows without a from/to range has to return the number. The other ones have to return the number followed by SUBSTRING(Number, 1, 3) + <from/to range>

一种可能的解决方案是使用 while 循环.但这不是我喜欢的方式.而且速度很慢.并且没有办法改变数据结构.我们从第三方供应商处查询数据.

One possible solution would be using while-loops. But thats not the way I'd prefer. And it's quite slow. And there's no way to change the data structure. We query data from a third party supplier.

在应用程序站点上，我有一个(非常小的)数字列表，例如241113"、241000"……并且需要知道该数字分配给哪个 ID.

On application site I've a (very small) list of numbers such as '241113', '241000', ... and need to know to which id this number is assigned to.

我将用来获取结果的查询是:

The query I will use to get a result is:

SELECT Id, Number FROM MyView WHERE Number IN ('241113', '241000')

推荐答案

我将用来获取结果的查询是:

The query I will use to get a result is:

SELECT Id, Number FROM MyView WHERE Number IN ('241113', '241000')

SELECT Id, Number FROM MyView WHERE Number IN ('241113', '241000')

这就是您可以编写该查询的方式.无需生成数字.

This is how you can write that query instead. There is no need to generate the numbers.

declare @T table
(
  Id int,
  Number int,
  [From] int,
  [To] int
)

insert into @T values
(1, 241100, NULL, NULL),
(2, 241110,  111,  115)

select T.Id, V.Number
from @T as T
  inner join (values (241113), 
                     (241100)) as V(Number)
    on V.Number between T.Number and T.Number + isnull(T.[To], 0)

将您要查找的数字放入表格变量的版本.

A version where you put the numbers you look for in a table variable instead.

declare @V table(Number int)
insert into @V values(241100)
insert into @V values(241113)

select T.Id, V.Number
from @T as T
  inner join @V as V
    on V.Number between T.Number and T.Number + isnull(T.[To], 0)

我没有在任何地方使用过 From 因为我不清楚除了 null 和 number+1 之外该列中可能有哪些值.

I have not used From anywhere because it is unclear to me what values is possible in that column other then null and number+1.

还有一个版本，您可以在过滤掉数字之前生成数字.结果是一样的，我相信性能没有那么好.

And a version where you generate the numbers before filtering them out. The result is the same and I believe performance is not as good.

;with C as
(
  select T.Id,
         T.Number
  from @T as T
  union all
  select T.Id,
         C.Number + 1
  from @T as T
    inner join C
      on C.Id = T.Id
  where stuff(C.Number, 1, 3, '') < T.[To]
)
select Id, Number
from C
where Number in ('241113', '241100')

这篇关于(递归)SQL 查询而不是循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！