Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Поиск свободного номера с исключениями  [new]
P1raTKa
Member

Откуда:
Сообщений: 8
Народ, помогите плс! Необходимо написать хранимку, которая будет выдавать свободный номер из одной таблицы, причём такой, чтобы он не встречался в другой!

Например, в первой таблице есть записи с номерами 1 2 4 5 7, во второй таблице есть запись 3. Хранимка должна выдать ответ 6, так как хоть номер 3 и свободен, он находится во второй таблице (таблице исключений)!
31 янв 12, 00:22    [12000165]     Ответить | Цитировать Сообщить модератору
 Re: Поиск свободного номера с исключениями  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
P1raTKa, UNION ALL + ROW_NUMBER() = ... изобретение велосипеда с квадратными колесами. Если вам нужна генерация секвенсов, то так и скажите, по сему поводу есть даже статьи, хотя ИМХО если есть возможность, лучше вообще отказаться от затыкания дырок.
31 янв 12, 00:27    [12000176]     Ответить | Цитировать Сообщить модератору
 Re: Поиск свободного номера с исключениями  [new]
Crimean
Member

Откуда:
Сообщений: 13147
тип данных для "номера"? уникальность?
31 янв 12, 00:31    [12000190]     Ответить | Цитировать Сообщить модератору
 Re: Поиск свободного номера с исключениями  [new]
P1raTKa
Member

Откуда:
Сообщений: 8
Crimean
тип данных для "номера"? уникальность?


int, уникально
31 янв 12, 00:33    [12000196]     Ответить | Цитировать Сообщить модератору
 Re: Поиск свободного номера с исключениями  [new]
P1raTKa
Member

Откуда:
Сообщений: 8
kDnZP
P1raTKa, UNION ALL + ROW_NUMBER() = ... изобретение велосипеда с квадратными колесами. Если вам нужна генерация секвенсов, то так и скажите, по сему поводу есть даже статьи, хотя ИМХО если есть возможность, лучше вообще отказаться от затыкания дырок.


Извините, я в SQL пока слабо соображаю, можете пример запроса написать?
31 янв 12, 00:35    [12000202]     Ответить | Цитировать Сообщить модератору
 Re: Поиск свободного номера с исключениями  [new]
Crimean
Member

Откуда:
Сообщений: 13147
P1raTKa
Crimean
тип данных для "номера"? уникальность?


int, уникально


в обоих таблицах?
31 янв 12, 00:45    [12000257]     Ответить | Цитировать Сообщить модератору
 Re: Поиск свободного номера с исключениями  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
P1raTKa, а начальный номер какой? Прям-таки = -100500?
Вы опишите, зачем оно вам надобно?
31 янв 12, 00:48    [12000269]     Ответить | Цитировать Сообщить модератору
 Re: Поиск свободного номера с исключениями  [new]
P1raTKa
Member

Откуда:
Сообщений: 8
kDnZP
P1raTKa, а начальный номер какой? Прям-таки = -100500?
Вы опишите, зачем оно вам надобно?


Начальный номер = 1.

БД используется для хранения информации о письмах. Используется порядковая нумерация. Изначально я просто добавлял новую запись, а номер записывал как первый свободный. Но теперь появились исключения - зарезервированные номера для писем

Уникален номер письма вместе с его типом (входящее/исходящее). То есть для каждого типа своя нумерация

CREATE TABLE [letter_data](
[letter_id] [int] NOT NULL PRIMARY KEY IDENTITY(1,1),
[letter_number] [int] NOT NULL,
[incoming] [bit] NOT NULL,
[letter_date] [datetime] NOT NULL,
[letter_text] [nvarchar(MAX)] NOT NULL
UNIQUE (letter_number, incoming))

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

CREATE TABLE [letter_numbers_reserves](
[user_id] [int] NOT NULL,
[incoming] [bit] NOT NULL,
[letter_number] [int] NOT NULL,
PRIMARY KEY (user_id, incoming, letter_number),
FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE
)

Сейчас мне необходимо получить первый свободный номер письма в letter_data, причём чтобы он не был в таблице letter_numbers_reserves.

PS когда я говорю про номер письма, имею ввиду соответствие двух уникальных полей (letter_number, incoming)
31 янв 12, 01:02    [12000307]     Ответить | Цитировать Сообщить модератору
 Re: Поиск свободного номера с исключениями  [new]
SandalTree
Member

Откуда: Перехлёсток восьми батог
Сообщений: 28146
BEGIN TRAN

CREATE TABLE #a
(
    f1 int
)
CREATE TABLE #b
(
    f1 int
)

INSERT INTO #a(f1) VALUES(1)
INSERT INTO #a(f1) VALUES(2)
INSERT INTO #a(f1) VALUES(4)
INSERT INTO #a(f1) VALUES(5)
INSERT INTO #a(f1) VALUES(7)
INSERT INTO #b(f1) VALUES(6);



WITH PreSet AS
(
  SELECT f1 FROM #a
  UNION
  SELECT f1 FROM #b
)  
SELECT MIN(RowNumber)
FROM (
    SELECT ROW_NUMBER() OVER( ORDER BY error ASC ) AS 'RowNumber'
    FROM master..sysmessages
) c 
WHERE RowNumber not in ( SELECT f1 FROM PreSet)
  and RowNumber <=  ( SELECT MAX(f1)+1 FROM PreSet)

ROLLBACK
31 янв 12, 01:09    [12000322]     Ответить | Цитировать Сообщить модератору
 Re: Поиск свободного номера с исключениями  [new]
P1raTKa
Member

Откуда:
Сообщений: 8
SandalTree
BEGIN TRAN

CREATE TABLE #a
(
    f1 int
)
CREATE TABLE #b
(
    f1 int
)

INSERT INTO #a(f1) VALUES(1)
INSERT INTO #a(f1) VALUES(2)
INSERT INTO #a(f1) VALUES(4)
INSERT INTO #a(f1) VALUES(5)
INSERT INTO #a(f1) VALUES(7)
INSERT INTO #b(f1) VALUES(6);



WITH PreSet AS
(
  SELECT f1 FROM #a
  UNION
  SELECT f1 FROM #b
)  
SELECT MIN(RowNumber)
FROM (
    SELECT ROW_NUMBER() OVER( ORDER BY error ASC ) AS 'RowNumber'
    FROM master..sysmessages
) c 
WHERE RowNumber not in ( SELECT f1 FROM PreSet)
  and RowNumber <=  ( SELECT MAX(f1)+1 FROM PreSet)

ROLLBACK



Спасибо большое!
31 янв 12, 01:18    [12000339]     Ответить | Цитировать Сообщить модератору
 Re: Поиск свободного номера с исключениями  [new]
kDnZP
Member [заблокирован]

Откуда: ★[msg=16399436]★[msg=20850760]
Сообщений: 11289
P1raTKa, почитайте еще тут, думаю будет полезно.
31 янв 12, 01:32    [12000358]     Ответить | Цитировать Сообщить модератору
 Re: Поиск свободного номера с исключениями  [new]
Gluck_13
Member

Откуда: Санкт-Петербург
Сообщений: 207
P1raTKa,

в предыдущем примере можно обойтись и без Row_Number.

;WITH PreSet AS
(
  SELECT f1 FROM #a
  UNION ALL
  SELECT f1 FROM #b
)
SELECT (MIN(p1.F1)) + 1 FROM (SELECT f1 FROM PreSet UNION ALL SELECT 0) p1
WHERE p1.f1 + 1 NOT IN (SELECT f1 FROM PreSet)


План такого запроса оценивается в SSMS как в 45 раз более дешевый (2% vs 92% с ROW_NUMBER())
31 янв 12, 07:57    [12000528]     Ответить | Цитировать Сообщить модератору
 Re: Поиск свободного номера с исключениями  [new]
TimothyZ
Member

Откуда: Новосибирск
Сообщений: 12
P1raTKa
CREATE TABLE [letter_numbers_reserves](
   [user_id] [int] NOT NULL,
   [incoming] [bit] NOT NULL,  
   [letter_number] [int] NOT NULL,
   PRIMARY KEY (user_id, incoming, letter_number),
   FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE
)


Эээ, только меня смущает, что следуя данному определению первичного ключа, разные пользователи смогут зарезервировать одинаковые номера писем?
31 янв 12, 08:14    [12000559]     Ответить | Цитировать Сообщить модератору
 Re: Поиск свободного номера с исключениями  [new]
P1raTKa
Member

Откуда:
Сообщений: 8
TimothyZ
P1raTKa
CREATE TABLE [letter_numbers_reserves](
   [user_id] [int] NOT NULL,
   [incoming] [bit] NOT NULL,  
   [letter_number] [int] NOT NULL,
   PRIMARY KEY (user_id, incoming, letter_number),
   FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE
)


Эээ, только меня смущает, что следуя данному определению первичного ключа, разные пользователи смогут зарезервировать одинаковые номера писем?


Согласен, исправляю )))
31 янв 12, 12:01    [12002127]     Ответить | Цитировать Сообщить модератору
 Re: Поиск свободного номера с исключениями  [new]
P1raTKa
Member

Откуда:
Сообщений: 8
Gluck_13
P1raTKa,

в предыдущем примере можно обойтись и без Row_Number.

;WITH PreSet AS
(
  SELECT f1 FROM #a
  UNION ALL
  SELECT f1 FROM #b
)
SELECT (MIN(p1.F1)) + 1 FROM (SELECT f1 FROM PreSet UNION ALL SELECT 0) p1
WHERE p1.f1 + 1 NOT IN (SELECT f1 FROM PreSet)


План такого запроса оценивается в SSMS как в 45 раз более дешевый (2% vs 92% с ROW_NUMBER())


Спасибо, да очень удобное решение, и срабатывает когда в таблице нет записей, в отличие от предыдущего!!!
31 янв 12, 12:19    [12002312]     Ответить | Цитировать Сообщить модератору
 Re: Поиск свободного номера с исключениями  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
P1raTKa
Gluck_13
P1raTKa,

в предыдущем примере можно обойтись и без Row_Number.

;WITH PreSet AS
(
  SELECT f1 FROM #a
  UNION ALL
  SELECT f1 FROM #b
)
SELECT (MIN(p1.F1)) + 1 FROM (SELECT f1 FROM PreSet UNION ALL SELECT 0) p1
WHERE p1.f1 + 1 NOT IN (SELECT f1 FROM PreSet)


План такого запроса оценивается в SSMS как в 45 раз более дешевый (2% vs 92% с ROW_NUMBER())


Спасибо, да очень удобное решение, и срабатывает когда в таблице нет записей, в отличие от предыдущего!!!
А если сто клиентов будут одновременно выполнять этот запрос?
Они получат один и тот же результат, а воспользоваться им сможет только один?
31 янв 12, 12:27    [12002400]     Ответить | Цитировать Сообщить модератору
 Re: Поиск свободного номера с исключениями  [new]
P1raTKa
Member

Откуда:
Сообщений: 8
iap
P1raTKa
пропущено...


Спасибо, да очень удобное решение, и срабатывает когда в таблице нет записей, в отличие от предыдущего!!!
А если сто клиентов будут одновременно выполнять этот запрос?
Они получат один и тот же результат, а воспользоваться им сможет только один?


Суть в том, что найденный пользователем свободный номер сразу же будет заноситься в таблицу резервов (по данному примеру #b) в рамках одной транзакции, соответственно другие пользователи не смогут его зарезервировать.
31 янв 12, 12:43    [12002550]     Ответить | Цитировать Сообщить модератору
 Re: Поиск свободного номера с исключениями  [new]
iap
Member

Откуда: Москва
Сообщений: 47145
P1raTKa
iap
пропущено...
А если сто клиентов будут одновременно выполнять этот запрос?
Они получат один и тот же результат, а воспользоваться им сможет только один?


Суть в том, что найденный пользователем свободный номер сразу же будет заноситься в таблицу резервов (по данному примеру #b) в рамках одной транзакции, соответственно другие пользователи не смогут его зарезервировать.
Если имеется в виду какая-то вставка в таблицу, то я и говорю,
первый вставит, остальным - исключение!
При чём тут сама транзакция, не совсем понятно.
Важен уровень изоляции транзакции, если на то пошло.
Вам годится только эксклюзивная блокировка таблицы.
Но это же кошмар!
31 янв 12, 12:55    [12002696]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить