Выберите идентификатор пользователя для определенного адреса электронной почты или max(id)+1, если адрес электронной почты не существует

Я хочу получить идентификатор пользователя для данного электронного письма, или, если электронное письмо не существует, я хочу, чтобы он возвращал MAX (id) +1.

Итак, для пример ниже я хочу:

  • Для электронной почты «[email protected]» я хочу, чтобы она возвращала 1
  • Для электронной почты «[email protected]» я хочу, чтобы она возвращала 9

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

DECLARE @USERS TABLE (id int, name varchar(50), email varchar(50));

INSERT INTO @USERS(id, name, email) VALUES
(1, 'john', '[email protected]'),
(2, 'nick', '[email protected]'),
(3, 'alex', '[email protected]'),
(4, 'liam', '[email protected]'),
(5, 'noah', '[email protected]'),
(6, 'oliver', '[email protected]'),
(7, 'james', '[email protected]'),
(8, 'rob', '[email protected]');

SELECT CASE WHEN (ISNULL(id, 0) = 0) THEN id ELSE (MAX(id) + 1)
FROM @USERS
WHERE email = '[email protected]';

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

GSerg 29.03.2022 22:08

Использование max может вызвать проблемы, если вы запустите это одновременно...

Dale K 29.03.2022 22:09

Рассматривали ли вы использование оператора MERGE для обновления или вставки? Гораздо более безопасный подход, чем то, что вы предлагаете. После этого вы можете выбрать идентификатор, который был обновлен / вставлен, если это необходимо.

Dijkgraaf 29.03.2022 22:16

@Dijkgraaf Пожалуйста, не рекомендуйте MERGE, если вы также не упомянули все предостережения.

Aaron Bertrand 29.03.2022 22:27

Фэй что ты тогда собираешься в делать сMAX(id)+1? Вы собираетесь ждать и вставить его позже? Это серьезная ошибка параллелизма, ожидающая своего появления. См. Пожалуйста, прекратите использовать этот антишаблон UPSERT.

Aaron Bertrand 29.03.2022 22:37

@DaleK Мне говорили об этом раньше - в другой теме (stackoverflow.com/questions/71632815/…) - но, к сожалению, поле id просто объявлено как int, без первичного ключа, без какой-либо другой техники для обеспечения уникальности ключа ... Так что я не знаю, как чтобы не возникало проблем из-за этого...

Faye D. 29.03.2022 22:37

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

Dale K 29.03.2022 22:38

@ФайД. Если вы не можете изменить таблицу, чтобы создать первичный ключ/идентификацию, вместо этого добавьте SEQUENCE. Это совершенно отдельный объект в базе данных, поэтому вам не нужно вносить никаких изменений в таблицу. Затем используйте последовательность NEXT VALUE FOR, чтобы получить новые значения идентификатора. Значения последовательности никогда не используются повторно, поэтому, если вы хотите получить новый идентификатор заранее и вставить его позже, все по-прежнему безопасно.

Joel Coehoorn 29.03.2022 22:40

@JoelCoehoorn да, к сожалению, я не могу контролировать дизайн этой таблицы.

Faye D. 29.03.2022 22:40

Как я прокомментировал в другой теме, которую я упомянул в своем предыдущем комментарии: «Это будет использоваться в веб-перехватчике WooCommerce, который будет синхронизировать новые заказы интернет-магазина с настольным приложением продавца, которое отвечает за выдачу ваучеров, отслеживание инвентаря. и т. д. Я не могу представить, чтобы два человека делали заказ в один и тот же момент, плюс WooCommerce (я думаю) ставит задачи веб-перехватчика в очередь». Итак, на практике я сомневаюсь, что возникнут какие-либо проблемы, но в идеале я хотел бы сделать что-то, чтобы убедиться, что я тоже...

Faye D. 29.03.2022 22:42

Два человека никогда не заказывают вещи в один и тот же момент, пока не сделают. Зачем рисковать, если можно предотвратить это?

Aaron Bertrand 29.03.2022 22:43

^^^ то, что он сказал... предполагать, что что-то никогда не случится, напрашивается на неприятности.

Dale K 29.03.2022 22:44

Итак, какие меры лучше предпринять для этого? Блокировка перед вставкой и разблокировка после нее?

Faye D. 29.03.2022 22:53

Это все еще ПОСЛЕДОВАТЕЛЬНОСТЬ. Опять же: для создания этого не требуется никаких изменений в таблице. Все остальное, что вы делаете БУДЕМ, в конечном итоге приведет к конфликту идентификаторов. Это обещание.

Joel Coehoorn 29.03.2022 22:55

@JoelCoehoorn, хотя это не по теме, не могли бы вы помочь мне понять это SEQUENCE использование в моем случае? К сожалению, SQL Server мне не по душе, я едва знаком с его основами.

Faye D. 29.03.2022 23:00

@ФайД. 1) прочитать документы 2) задать новый вопрос :)

Dale K 29.03.2022 23:02

@DaleK вот он, сэр! stackoverflow.com/questions/71669547/…, если вы хотите помочь мне и в этом! :)

Faye D. 30.03.2022 00:41
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
17
43
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Следующее должно удовлетворять требованиям синтаксиса:

select coalesce(max(id), (select coalesce(max(id), 0) + 1 from @users))
from @users
where email = '[email protected]'

При этом максимальный идентификатор может меняться между его выбором и использованием в другом запросе.

@дейл я использовал max(id) трюк. Агрегатные функции всегда возвращают одну строку. Я все равно исправил, но вот рабочий пример, показывающая, что это сработало dbfiddle.uk/…

Salman A 29.03.2022 22:18

Я все время забываю об этом :)

Dale K 29.03.2022 22:20

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

Salman A 29.03.2022 22:21
Ответ принят как подходящий

Два подзапроса внутри coalesce должны выполнить эту работу.

DECLARE @TestEmail VARCHAR(50) = '[email protected]';

SELECT 
    COALESCE((SELECT TOP 1 id FROM @USERS WHERE email = @TestEmail), (SELECT MAX(id) + 1 FROM @USERS), 1);

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

Я отмечаю это как правильный ответ только потому, что он был опубликован первым. Потому что кроме этого, другой ответ такой же!

Faye D. 29.03.2022 22:44

То, что я вижу, противоположно: ваше сообщение было опубликовано 37 минут назад, а другое — 39 минут назад, но если вы так говорите, я вам доверяю! Спасибо за справедливость!

Faye D. 29.03.2022 22:54

ОМГ, а я тупой! :П

Faye D. 29.03.2022 23:01

Я закончил тем, что использовал ваш, так как он был понятнее и проще с единственным COALESCE.

Faye D. 29.03.2022 23:19

Я этого не предвидел и не упомянул об этом в своем первоначальном вопросе, но когда адрес электронной почты клиента тот же, новую строку все равно нужно будет вставить, но с тем же идентификатором другой строки с этим адресом электронной почты. Итак, в случае третьей строки SQL жаловался, что было возвращено несколько строк (обе/все с одним и тем же идентификатором, но все равно возвращало ошибку), поэтому мне пришлось использовать DISTINCT для первого подзапроса! Очень подлый!

Faye D. 30.03.2022 00:59

Это было решено другим решением. Но TOP лучше, чем DISTINCT.

Dale K 30.03.2022 01:02

Другие вопросы по теме