Блог

Главная - Вопросы по программированию - Курсор SQL для генерации уникальных непоследовательных выводов

Курсор SQL для генерации уникальных непоследовательных выводов

#sql-server #loops #random #cursor #unique

#sql-сервер #циклы #Случайный #курсор #уникальный

Вопрос:

Я пытаюсь сгенерировать непоследовательные уникальные выводы для группы новых сотрудников. Вот как это должно работать:

  1. Курсор должен извлекать всех новых сотрудников из таблицы B
  2. Создавайте новые уникальные контакты для каждого нового сотрудника
  3. Проверьте, существует ли вновь созданный вывод в таблице A
  4. Если да, повторяйте шаг 2, пока мы не получим уникальный PIN-код для всех новых сотрудников
  5. Вставьте новый номер сотрудника и контакты в таблицу A

В таблице A поля PIN, Employee_Number, Date

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

Это выполняется только один раз и останавливается, может кто-нибудь сказать мне, чего мне не хватает

  DECLARE 
    @EmpNo             VARCHAR(50), 
    @Pin               INT, 
    @today             DATETIME,
    @Upper             INT,
    @Lower             INT,
SET @Lower = 100000 ---- The lowest random number allowed
SET @Upper = 999999 ---- The highest random number allowed

DECLARE cur CURSOR FOR 

SELECT   
       Employee_Number, 
       (ROUND(((@Upper - @Lower -1) * RAND()   @Lower), 0))as PIN, 
       GETDATE()
FROM     TableB 
WHERE    Employee_Number NOT IN (SELECT Employee_Number FROM TableA)


OPEN cur
FETCH NEXT FROM cur INTO @EmpNo, @Pin, @Today

WHILE @@FETCH_STATUS=0

BEGIN    

         INSERT INTO TableA (PIN, Employee_Number, Date)
         SELECT @Pin, @EmpNo, @Today


FETCH NEXT FROM cur INTO @Pin, @EmpNo, @Today
END

CLOSE cur
DEALLOCATE cur

Ответ №1:

вам не нужен курсор. Также лучше использовать newid() для генерации случайного числа вместо rand()

 INSERT INTO TableA (PIN, Employee_Number, Date)
SELECT pin = @Lower 
             (   abs(convert(bigint,convert(varbinary(20),newid()))) 
               % (@Upper - @Lower   1) 
             ),
       Employee_Number,
       GETDATE()
FROM   TableB 
WHERE  Employee_Number NOT IN (SELECT Employee_Number FROM TableA)

Редактировать :

для обработки уникального вывода решение без использования курсора состоит в том, чтобы сохранить таблицу всех возможных выводов.

 create table pin
(
    pin         int primary key,
    is_used     bit default 0
)

вы генерируете вывод, используя рекурсивный cte

 ; with num as
(
    -- anchor member
    select  pin = @Lower

    union all

    -- recursive member
    select  pin = pin   1
    from    num
    where   pin < @Upper
)
insert into pin (pin) 
select  pin
from    num

затем, когда вам нужно назначить PIN-код сотруднику

 ; with emp as
(
    select  Employee_Number, rn = row_number() over (order by Employee_Number)
    from    TableA a
    where   not exists
            (
                select  *
                from    TableB b
                where   b.Employee_Number   = a.Employee_Number
            )
),
pin as
(
    -- get unused pin, order it randomly using newid()
    select  pin, rn = row_number() over (order by newid())
    from    pin
    where   is_used = 0
)
insert into #TableB (Employee_Number, pin)
select  e.Employee_Number, p.pin
from    emp e
        inner join pin p    on  e.rn    = p.rn

после этого обновите столбец is_used в таблице выводов

 update  p
set     is_used = 1
from    TableB b
        inner join #pin p   on  b.pin   = p.pin
where   p.is_used   = 0

Комментарии:

1. Спасибо за альтернативу. Этот запрос работает без создания повторяющегося вывода, однако выдает ошибку ограничения УНИКАЛЬНОГО КЛЮЧА, поскольку поле вывода в таблице a становится УНИКАЛЬНЫМ. Будет ли безопасно удалить эту уникальную ключевую функцию из поля вывода?

2. А также при вставке запрос должен также проверять таблицу A на любые повторяющиеся выводы

3. Почему PIN-код должен быть уникальным для всех сотрудников? на самом деле это не имеет смысла для меня

4. уникальный вывод усложнит запрос. Однако для обеспечения уникального вывода вы можете использовать ИДЕНТИФИКАТОР в столбце pin.

5. @AhmedSaeed, разве это не сделает вывод sequential ?

Ответ №2:

Другой подход может быть следующим.

Примечание @EmpTable является демонстративным пустым (удалите @ для вашей среды)

1) cte0 генерирует базовую таблицу подсчета из 10 строк

2) cteR расширяет cte0 до 1000 строк и добавляет случайную строку (NewID())

3) cteI преобразует cteR в INT (не более 6 цифр)

4) Обновление применяется ко всем нулевым ВЫВОДАМ

 Declare @EmpTable table (Employee_Number int,PIN int)
Insert Into @EmpTable values
(1,100288),(2,651701),(3,823700),
(4,NULL),(5,NULL)

Declare @Lower int=100000,@Upper int = 999999

 ;with cte0(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N))
     , cteR(R)   As (Select Distinct R=cast(NewID() as varchar(50)) From cte0 N1, cte0 N2, cte0 N3, cte0 N4) 
     , cteI(R,N) As (Select Distinct R,cast(Left(cast(abs(cast(Hashbytes('MD5',R) as int)) as varchar(25)),6) as int) From cteR R) 
Update @EmpTable Set PIN = B.N
 From  @EmpTable A
 Join  (
         Select RowNr=(Select min(Employee_Number)-1 From @EmpTable Where PIN is NULL) Row_Number() over (Order By R)
               ,N 
          From  cteI 
          Where N Between @Lower and @Upper
            and N not in (Select Distinct PIN From @EmpTable Where PIN is not NULL)
        ) B on A.Employee_Number=B.RowNr and PIN is null


Select * from @EmpTable

Обновлен пустой

 Employee_Number PIN
1               100288   -- Same as Original
2               651701   -- Same as Original
3               823700   -- Same as Original
4               118844   -- New Random PIN
5               677855   -- New Random PIN