如何确定给定标准的连续范围的大小?

发布于 11-23 15:43 字数 1119 浏览 2 评论 0原文

我在 SQL Server 2008R2 中有一个位置表(定义如下)。

系统框中有位置。

我需要找到一个盒子,其中还有 X 个空闲位置。但是,X 位置必须是连续的(从左到右、从上到下,即递增的 PositionID)。

构建一个查找具有 X 个空闲位置的框的查询非常简单。我现在遇到的问题是确定位置是否连续。

关于基于 TSQL 的解决方案有什么建议吗?

表定义

` CREATE TABLE [dbo].[Position](
        [PositionID] [int] IDENTITY(1,1) NOT NULL,
        [BoxID] [int] NOT NULL,
        [pRow] [int] NOT NULL,
        [pColumn] [int] NOT NULL,
        [pRowLetter] [char](1) NOT NULL,
        [pColumnLetter] [char](1) NOT NULL,
        [SampleID] [int] NULL,
        [ChangeReason] [nvarchar](4000) NOT NULL,
        [LastUserID] [int] NOT NULL,
        [TTSID] [bigint] NULL,
     CONSTRAINT [PK_Position] PRIMARY KEY CLUSTERED 
    (
        [PositionID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]`

编辑

http://pastebin.com/V8DLiucN - 包含示例位置的pastebin链接对于 1 个盒子(样本数据中所有位置为空)

编辑 2

“空闲”位置是 SampleID = null 的位置

I have a positions table in SQL Server 2008R2 (definition below).

In the system boxes there are positions.

I have a requirement to find a box, which has X free positions remaining. However, the X positions must be continuous (left to right, top to bottom i.e. ascending PositionID).

It has been simple to construct a query that finds a box with X positions free. I now have the problem of determining if the positions are continuous.

Any suggestions on a TSQL based solution?

Table Definition

` CREATE TABLE [dbo].[Position](
        [PositionID] [int] IDENTITY(1,1) NOT NULL,
        [BoxID] [int] NOT NULL,
        [pRow] [int] NOT NULL,
        [pColumn] [int] NOT NULL,
        [pRowLetter] [char](1) NOT NULL,
        [pColumnLetter] [char](1) NOT NULL,
        [SampleID] [int] NULL,
        [ChangeReason] [nvarchar](4000) NOT NULL,
        [LastUserID] [int] NOT NULL,
        [TTSID] [bigint] NULL,
     CONSTRAINT [PK_Position] PRIMARY KEY CLUSTERED 
    (
        [PositionID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]`

Edit

http://pastebin.com/V8DLiucN - pastebin link with sample positions for 1 box (all positions empty in sample data)

Edit 2

A 'free' position is one with SampleID = null

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

烟酒忠诚2024-11-30 15:43:55
DECLARE @AvailableSlots INT
SET @AvailableSlots = 25

;WITH OrderedSet AS (
SELECT
    BoxID,
    PositionID,
    Row_Number() OVER (PARTITION BY BoxID ORDER BY PositionID) AS rn
FROM
    Position
WHERE 
    SampleID IS NULL
)
SELECT
    BoxID,
    COUNT(*) AS AvailableSlots,
    MIN(PositionID) AS StartingPosition,
    MAX(PositionID) AS EndingPosition
FROM
    OrderedSet
GROUP BY
    PositionID - rn,
    BoxID
HAVING
    COUNT(*) >= @AvailableSlots

诀窍在于 GROUP BY 语句中的 PositionID - rn(行号)。这可以将连续的集合组合在一起...并且从那里可以很容易地执行 HAVING 将结果限制为具有所需数量的空闲槽的 BoxID

DECLARE @AvailableSlots INT
SET @AvailableSlots = 25

;WITH OrderedSet AS (
SELECT
    BoxID,
    PositionID,
    Row_Number() OVER (PARTITION BY BoxID ORDER BY PositionID) AS rn
FROM
    Position
WHERE 
    SampleID IS NULL
)
SELECT
    BoxID,
    COUNT(*) AS AvailableSlots,
    MIN(PositionID) AS StartingPosition,
    MAX(PositionID) AS EndingPosition
FROM
    OrderedSet
GROUP BY
    PositionID - rn,
    BoxID
HAVING
    COUNT(*) >= @AvailableSlots

The trick is the PositionID - rn (row number) in the GROUP BY statement. This works to group together continuous sets... and from there it's easy to just do a HAVING to limit the results to the BoxIDs that have the required amount of free slots.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文