如何确定给定标准的连续范围的大小?
我在 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
诀窍在于
GROUP BY
语句中的PositionID - rn
(行号)。这可以将连续的集合组合在一起...并且从那里可以很容易地执行HAVING
将结果限制为具有所需数量的空闲槽的BoxID
。The trick is the
PositionID - rn
(row number) in theGROUP BY
statement. This works to group together continuous sets... and from there it's easy to just do aHAVING
to limit the results to theBoxID
s that have the required amount of free slots.