SQL - генерирует число циклического перебора при вставке

Question

Я развертываю существующую сделанную на заказ службу Windows (C#) несколько экземпляров, которые будут читать из одной таблицы очередей.

Очередь основана на приведенной ниже простой таблице SQL.

Record-Id (int auto id)

Added-Date (Date)

Added-By (Text)

Data-To-be-Processed (Text)

**Pool-Number (int)**

Как бы я создал порядковый номер циклического перебора для каждой вставки с учетом максимального размера пула? здесь я использую пул размером 3 (это может быть жестко закодировано). например

1 | 31/10/2014 | DATA | Pool 1

2 | 31/10/2014 | DATA | Pool 2

3 | 31/10/2014 | DATA | Pool 3

4 | 31/10/2014 | DATA | Pool 1

5 | 31/10/2014 | DATA | Pool 2

6 | 31/10/2014 | DATA | Pool 3

7 | 31/10/2014 | DATA | Pool 1

Я думал об использовании таблицы Sequence и увеличивал ее на каждой вставке и сбрасывал до 1, когда он достигает максимального размера пула, например

TbSeq

  dbSeq (int) (Will contain 1-3 depending last insert)

Есть лучший способ сделать это?

Answer 1

Если вы можете использовать RecordId в качестве помощи, то вы могли бы использовать по модулю (% )

select *, 1+((RecordId-1)%3) as Pool
from t

демонстрационный ролик: http://rextester.com/WNEIQM50851

возвращает:

+----------+------------+-------------------+------+
| RecordId | AddedDate  | DataToBeProcessed | Pool |
+----------+------------+-------------------+------+
|        1 | 2014-10-31 | DATA              |    1 |
|        2 | 2014-10-31 | DATA              |    2 |
|        3 | 2014-10-31 | DATA              |    3 |
|        4 | 2014-10-31 | DATA              |    1 |
|        5 | 2014-10-31 | DATA              |    2 |
|        6 | 2014-10-31 | DATA              |    3 |
|        7 | 2014-10-31 | DATA              |    1 |
+----------+------------+-------------------+------+

---

Вы можете добавить это как вычисляемый столбец (сохраняется необязательно, но рекомендуется)

alter table t
  add Pool as (1+((RecordId-1)%3)) persisted;

Answer 2

Я бы не зависел от RecordId как таковой Это может иметь пробелы. Самый простой способ сделать настоящий круговой прием - это использовать row_number() и по модулю арифметики:

select 1 + (row_number() over (order by id) - 1) % 3 as poolnum
from t;

Если вы знаете, что RecordId не имеет пробелов, то вы можете использовать это вместо этого. С помощью RecordId более эффективно, потому что вы можете выполнить полный расчет в пределах одной строки и даже добавить вычисляемый столбец:

alter table t add poolnum as (1 + (row_number() over (order by id) - 1) % 3)

Answer 3

Ты можешь использовать SECQUENCE как:

CREATE TABLE MyTable (
    Record_Id INT IDENTITY(1,1), Added_Date DATE,
    Added_By VARCHAR(50), Data_To_be_Processed VARCHAR(50), Pool_Number INT);
GO    
CREATE SEQUENCE Pool
AS INT
START WITH 1
MINVALUE 1
MAXVALUE 3
CYCLE
GO

INSERT INTO MyTable VALUES
('2014-10-31', 'DATA', 'Pool', NEXT VALUE FOR Pool),
('2014-10-31', 'DATA', 'Pool', NEXT VALUE FOR Pool),
('2014-10-31', 'DATA', 'Pool', NEXT VALUE FOR Pool),
('2014-10-31', 'DATA', 'Pool', NEXT VALUE FOR Pool),
('2014-10-31', 'DATA', 'Pool', NEXT VALUE FOR Pool),
('2014-10-31', 'DATA', 'Pool', NEXT VALUE FOR Pool),
('2014-10-31', 'DATA', 'Pool', NEXT VALUE FOR Pool),
('2014-10-31', 'DATA', 'Pool', NEXT VALUE FOR Pool),
('2014-10-31', 'DATA', 'Pool', NEXT VALUE FOR Pool);

SELECT *
FROM MyTable;

Результат:

+-----------+---------------------+----------+----------------------+-------------+
| Record_Id |     Added_Date      | Added_By | Data_To_be_Processed | Pool_Number |
+-----------+---------------------+----------+----------------------+-------------+
|         1 | 31.10.2014 00:00:00 | DATA     | Pool                 |           1 |
|         2 | 31.10.2014 00:00:00 | DATA     | Pool                 |           2 |
|         3 | 31.10.2014 00:00:00 | DATA     | Pool                 |           3 |
|         4 | 31.10.2014 00:00:00 | DATA     | Pool                 |           1 |
|         5 | 31.10.2014 00:00:00 | DATA     | Pool                 |           2 |
|         6 | 31.10.2014 00:00:00 | DATA     | Pool                 |           3 |
|         7 | 31.10.2014 00:00:00 | DATA     | Pool                 |           1 |
|         8 | 31.10.2014 00:00:00 | DATA     | Pool                 |           2 |
|         9 | 31.10.2014 00:00:00 | DATA     | Pool                 |           3 |
+-----------+---------------------+----------+----------------------+-------------+