sql服务器-表子集中可用的最小整数-sql

Question

我正在使用一个现有的表，该表存储开始日期、结束日期和用于排序的整数。

对于给定的开始&结束日期，我需要能够确定具有重叠日期范围的条目的最小可用整数。

例如，我的表可能存储以下记录：

7月8日->7月9日，订购指数为0。

7月9日->7月10日，订购指数为1。

7月9日->7月11日，订购指数为2。

然后，给定日期范围July 10th->July 11th，我想将排序索引设置为0。

它需要在条目日期范围内没有其他条目的情况下工作(因此它可以默认为0(。日期范围并不总是相隔两个日期，排序索引也没有限制。

以下是我所拥有的，只返回高于最大订单指数的一个：

SELECT ISNULL(MAX(order_index),-1) + 1 FROM table
WHERE start_date <= @end AND end_date >= @start)

我试着用这个答案，但没能得到想要的结果。

Answer 1

这样的东西怎么样？

declare @SampleData table ([BeginDate] date, [EndDate] date, [Order] int);
insert @SampleData values
    ('2015-07-08', '2015-07-09', 0),
    ('2015-07-09', '2015-07-10', 1),
    ('2015-07-09', '2015-07-11', 2);
declare @Start date = '2015-07-10';
declare @End date = '2015-07-11';
with [OrderingCTE] as
(
    select
        [Order],
        [Ideal Order] = row_number() over (order by [Order]) - 1
    from
        @SampleData
    where
        [BeginDate] <= @End and
        [EndDate] >= @Start
)
select coalesce
(
    min(case [Order] when [Ideal Order] then null else [Ideal Order] end),
    max([Order]) + 1
)
from
    [OrderingCTE];

CTE为源表中的每条记录生成两个排序：[Order]是存储在记录中的实际值，[Ideal Order]是如果在给定日期范围内使用了所有可能的排序(从零开始(，则的值将是。

如果在任何时候[Ideal Order]与[Order]不同，则可以推断当前的[Ideal Order]值未被使用，因此是可用的最小值。如果在任何时候都不是这样，那么最小可用值比迄今为止使用的最大值大一；这是脚本底部COALESCE的后半部分。

最后一点：你链接的问题有另一个答案，这引发了人们对可能出现的种族状况的担忧，这取决于你试图如何使用你以这种方式查询的数据。如果你还没有看过，我强烈建议你看一看。

Answer 2

我会尝试这样的东西：

SELECT
  COALESCE(
    MIN(CASE WHEN t2.order_index IS NULL THEN t1.order_index - 1 ELSE NULL END),
    MAX(t1.order_index) + 1,
    0)
FROM TheTable t1
LEFT JOIN TheTable t2
  ON t2.order_index = t1.order_index - 1
    AND t2.start_date <= @end
    AND t2.end_date >= @start
WHERE t1.start_date <= @end
  AND t1.end_date >= @start

Answer 3

我认为可以通过枚举值来实现这一点。如果我假设顺序索引不重复，那么您可以使用row_number()和一些算术来查找"孔"。需要额外的逻辑来处理边缘情况。

with t as (
      select t.*,
             row_number() over (order by order_index) as seqnum,
             min(order_index) over () as minoi,
             max(order_index) over () as maxoi
      from table t
      where start_date <= @end and end_date >= @start
     )
select (case when min(minoi) > 0 then 0
             when min(minoi) is null then min(maxoi + 1)
             else min(minoi + seqnum - 1)
        end)
from t 
where order_index <> minoi + seqnum - 1 or
      order_index = maxoi