Примерная таблица:
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
Любая помощь приветствуется.
Используя предложение 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'
@symcbean в моем запросе есть предложение GROUP BY
. Пожалуйста, уточните дальше.
Попробуйте запустить его - посмотрите, что произойдет с идентификатором - либо вы получите сообщение об ошибке, если ваша СУБД настроена на использование строгого синтаксиса, либо он сообщит последнее значение для 'id', которое он видит - OP запросил все идентификаторы.
Вы можете использовать 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
Хорошая идея с использованием GROUP_CONCAT - но запрос не вернет соответствующие данные без
GROUP BY foreign_id
, а затем вы потеряете идентификатор - следовательно, ваш запрос не фильтрует правильные записи foreign_id и не выводит требуемые атрибуты