MySQL - выбрать строки, в которых значение столбца равно 0, сгруппировать по другому столбцу?

Примерная таблица:

ID, foreign_id, status

ID - это уникальный, foreign_id - нет. Примеры строк:

1, 1111, 0  
2, 2222, 0  
3, 3333, 1
4, 1111, 1
5, 4444, 0

Я хочу выбрать уникальные foreign_id, которые имеют только статус 0. Если он имеет статус 0, но другая строка с тем же внешним идентификатором имеет статус 1, не выбирайте этот foreign_id. Ожидаемый результат:

1, 2222, 0
5, 4444, 0

Я могу выбрать, где находится status = 0, и сгруппировать по foreign_id, но это просто выберет строку со статусом 0, даже если может быть другая строка с тем же foreign_id и статусом 1.

SELECT ID, foreign_id 
FROM table 
WHERE status = 0 
GROUP BY foreign_id

Любая помощь приветствуется.

Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
В последние годы архитектура микросервисов приобрела популярность как способ построения масштабируемых и гибких приложений. Laravel , популярный PHP...
Как построить CRUD-приложение в Laravel
Как построить CRUD-приложение в Laravel
Laravel - это популярный PHP-фреймворк, который позволяет быстро и легко создавать веб-приложения. Одной из наиболее распространенных задач в...
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
В предыдущем посте мы создали функциональность вставки и чтения для нашей динамической СУБД. В этом посте мы собираемся реализовать функции обновления...
Документирование API с помощью Swagger на Springboot
Документирование API с помощью Swagger на Springboot
В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .
Роли и разрешения пользователей без пакета Laravel 9
Роли и разрешения пользователей без пакета Laravel 9
Этот пост изначально был опубликован на techsolutionstuff.com .
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.
2
0
414
5
Перейти к ответу Данный вопрос помечен как решенный

Ответы 5

Ответ принят как подходящий

Используя предложение GROUP_CONCAT и DISTINCT, мы группа все уникальные статусы для foreign_id. Теперь внутри предложения HAVING нам просто нужно отфильтровать только те foreign_id, где уникальные статусы - только '0'.

Попробуйте следующее:

SELECT ID, foreign_id, status, GROUP_CONCAT(DISTINCT status) as statuses 
FROM table 
GROUP BY foreign_id 
HAVING statuses = '0'

Хорошая идея с использованием GROUP_CONCAT - но запрос не вернет соответствующие данные без GROUP BY foreign_id, а затем вы потеряете идентификатор - следовательно, ваш запрос не фильтрует правильные записи foreign_id и не выводит требуемые атрибуты

symcbean 13.09.2018 14:24

@symcbean в моем запросе есть предложение GROUP BY. Пожалуйста, уточните дальше.

Madhur Bhaiya 13.09.2018 14:35

Попробуйте запустить его - посмотрите, что произойдет с идентификатором - либо вы получите сообщение об ошибке, если ваша СУБД настроена на использование строгого синтаксиса, либо он сообщит последнее значение для 'id', которое он видит - OP запросил все идентификаторы.

symcbean 13.09.2018 16:45

Вы можете использовать group by и having:

select foreign_id, max(status) as status
from t
group by foreign_id
having max(status) = 0;

В принципе, вам нужно сделать тест после на group by.

Если вам нужны полные строки, используйте not exists:

select t.*
from t
where not exists (select 1
                  from t t2
                  where t2.foreign_id = t.foreign_id and t2.status <> 0
                 );

использовать агрегатную функцию

select * from t where foreign_id in (
     select foreign_id
        from t
        group by foreign_id
        having min(status) = 0
       ) and status!=1

(Я отредактировал это, чтобы продемонстрировать, что это работает, и прояснить некоторые вещи):

Что вас, вероятно, интересует, так это утверждение HAVING.

Вы можете использовать оператор HAVING после your GROUP BY. Вот довольно хорошее объяснение: https://www.dofactory.com/sql/having

Короче говоря, оператор HAVING позволяет фильтровать данные, возвращаемые в результате выражения GROUP BY.

Попробуй это:

SELECT 
     foreign_id, MAX(status) AS status
FROM
     myTable
GROUP BY 
     foreign_id
HAVING 
     MAX(status) = 0



Вот пример его работы (протестирован в базовом файле MS Access):

Затем я ввел этот код:

И я получил такие результаты:



Дайте знать, если у вас появятся вопросы!

Как говорили другие, вы должны агрегировать данные, но с этим есть много потенциальных проблем. Но прежде чем мы перейдем к этому, вы заявили, что вам нужны исходные записи в выходных данных, а не консолидированные данные - простейшее решение - использовать 2 запроса - один для идентификации foreign_ids только со статусом = 0 и один для получения соответствующего ряды

SELECT r.*
FROM examples r
JOIN ( SELECT s.foreign_id, MAX(s.status)
  FROM examples s
  GROUP BY s.foreign_id
  HAVING MAX(s.status)=0
) t
ON r.foreign_id=t.foreign_id

В качестве альтернативы вы можете получить foreign_ids, где status! = 0, и исключить их во втором запросе, что может быть более эффективным в зависимости от распределения ваших данных.

Это происходит в том случае, если "status" <0. Вывод будет включать эти строки (потенциально то же самое может произойти с SUM ()). Есть несколько способов решить эту проблему, например, SUM (ABS (status)) или SUM (IF (status = 0,0,1)). Как предлагает Мадхур, вы также можете GROUP_CONCAT (status) и проверить, что вывод столбца равен только «0».

Но я подозреваю, что вы собираетесь сказать мне, что статус не может быть отрицательным (или нулевым?), Поскольку это номинальный номер. В этом случае вы используете неправильный тип данных - вам следует использовать ENUM. В этом случае вы не можете применить SUM () или MAX () к ENUM, но вы все равно можете использовать методы SUM (IF (status = ?, 0,1)) или GROUP_CONCAT ().

Следовательно....

SELECT r.*
FROM examples r
JOIN ( SELECT s.foreign_id, SUM(IF(status=0,0,1))
  FROM examples s
  GROUP BY s.foreign_id
  HAVING SUM(IF(status=0,0,1))=0
) t
ON r.foreign_id=t.foreign_id

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