Как убедиться, что у каждого идентификатора игрока есть хотя бы 1 основное изображение, если у него более 0 изображений (mysql)

ProgramBox

Как убедиться, что у каждого идентификатора игрока есть хотя бы 1 основное изображение, если у него более 0 изображений (mysql)

Post author:admin
Запись опубликована:11 мая, 2023
Post category:Вопросы по программированию

#mysql #sql

Вопрос:

Я работаю с таблицей images. Мне нужно убедиться, что у каждого игрока есть основное изображение, если у него более 1 изображения. В следующем примере dataset это обновило бы либо myimage.jpg или hisimage.jpg основное значение равно 1 и only1image.jpg основное значение равно 1.

 Table:  Images

Playerid - 16
Image  - myimage.jpg
primary - 0

Playerid - 16
Image  - hisimage.jpg
primary - 0

Playerid - 17 
Image - only1image.jpg
primary - 0

Playerid - 18 
Image - jamison.jpg
primary - 1

1. Вы хотите «убедиться», создав правило MySQL, которое запрещает вам изменять 1 на 0? или вам просто нужен способ проверить?

2. Если для игрока существует 1, то он соответствует текущим требованиям, и никакие данные для этого идентификатора игрока не должны быть изменены

Ответ №1:

Я предполагаю, что должна быть таблица игроков? Если это так, вы могли бы использовать следующее…

(Это синтаксис MS SQL Server, но логика должна сохраняться в MySQL.)

 UPDATE
  Images
SET
  Primary = 1
FROM
  Player
INNER JOIN
  Image
    ON Image.ID = (SELECT MIN(lookup.ID) FROM Image AS lookup WHERE lookup.playerID = Player.ID)
WHERE
  NOT EXISTS (SELECT 1 FROM Image AS lookup WHERE lookup.playerID = Player.ID AND lookup.Primary = 1)

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

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

Версия, которая не использует таблицу Player, но, возможно, медленнее (потребуется тестирование)…

 UPDATE
  Image
SET
  Primary = 1
WHERE
  ImageID = (SELECT MIN(lookup.ID) FROM Image AS Lookup WHERE lookup.PlayerID = Image.PlayerID)
  AND NOT EXISTS (SELECT 1 FROM Image AS Lookup WHERE lookup.PlayerID = Image.PlayerID AND lookup.Primary = 1)

1. Вы правильно указали таблицу игроков, но ваш исходный запрос не предоставил мне правильные наборы данных. Получено много значений, у которых уже были первичные изображения из запроса после удаления update и установки с помощью select.

2. NOT EXISTS Коррелированный подзапрос должен был справиться с этим. Я слегка изменил начальный запрос на псевдоним обоих вариантов использования Image в соответствующих подзапросах. Это не должно иметь значения, но попробуйте…

3. Все ли ваши изображения имеют уникальные идентификаторы? Или у каждого игрока 16 и игрока 17 может быть изображение с идентификатором 1? И что вы получаете от этого? SELECT * FROM Image WHERE NOT EXISTS (SELECT 1 FROM Image AS Lookup WHERE lookup.PlayerID = Image.PlayerID AND lookup.Primary = 1)

4. У каждого игрока есть уникальный идентификатор игрока, у каждого изображения есть уникальный идентификатор, единственная ситуация, которую это не возвращает, — это когда у игрока есть только 1 изображение, и его значение основного изображения = 0.

5. С вашим запросом я получаю результаты (930) — и 908 из вашего исходного запроса на обновление

Ответ №2:

Вот дополнительный продукт @Dems, для меня это просто имеет больше смысла

 UPDATE Images SET `primary` = 1 WHERE Player IN (
    SELECT Player FROM (
        SELECT Player, MAX(`primary`) as max_prim FROM Images GROUP BY Player
    ) as MPrim WHERE max_prim = 0
)

1. Это установит основной флаг равным 1 для всех изображений для игрока. В случае, если у игрока есть несколько изображений, только у одного должен быть установлен флаг Primary. (Также, насколько я знаю, MySQL не очень хорошо работает с предложениями IN? использование EXISTS может быть быстрее для этой части вашей логики, я не уверен …)

2. @Dems вы правы. Я не учитывал эту часть критериев OP

Ответ №3:

В качестве альтернативы вы могли бы установить primary равным 1 по умолчанию и создать уникальность с помощью (Player,primary) и добавить ON DUPLICATE KEY UPDATE primary=0

Ответ №4:

Рассмотрите возможность рефакторинга ваших таблиц.

В вашей таблице игроков есть столбец, который указывает на строку изображения, которая является основным изображением, вот так:

 player
---------
player_id
primary_image_id


image
---------
image_id
player_id

Таким образом, у игрока будет только одно применимое основное изображение.