Как суммировать результаты двух разных запросов по разным столбцам?
Вот моя customers таблица:
и таблица shifts:
мой ожидаемый результат:
Поэтому я просто хочу посчитать, сколько раз customers.id находится в таблице shifts, неважно, в id1 или id2. И то, и другое может быть null.
Я могу показать таблицу для одного столбца, например:
SELECT t1.name, COUNT(t2.id1) AS num
FROM customers t1
INNER JOIN shifts t2
ON t2.id1=t1.id
WHERE t2.id1=t1.id
GROUP BY t2.id1
и, конечно, я могу сделать то же самое для id2.
Но как я могу суммировать оба результата?
Я знаю, что могу использовать оператор +, например:
COUNT(t2.id1)+COUNT(t2.id2)
но фактический расчет совершенно другой, поскольку он также должен изменить положения JOIN и GROUP.
Поэтому я попытался суммировать результаты всех запросов, как предложено здесь:
SELECT q1.name, q1.num+q2.num
FROM
(SELECT t1.name, COUNT(t2.id1) AS num
FROM customers t1
INNER JOIN shifts t2
ON t2.id1=t1.id
WHERE t2.id1=t1.id
GROUP BY t2.id1) AS q1,
(SELECT t1.name, COUNT(t2.id2) AS num
FROM customers t1
INNER JOIN shifts t2
ON t2.id2=t1.id
WHERE t2.id2=t1.id
GROUP BY t2.id2) AS q2
WHERE q1.name=q2.name
но он ничего не возвращает, поскольку предложение WHERE требует, чтобы оба запроса имели один и тот же name, а это невозможно. Если я удалю предложение WHERE, результаты будут неверными, поскольку я отбрасываю name второго запроса.
Как суммировать результаты двух разных запросов по разным столбцам?
@nbk FULL OUTER JOIN не нужен, см. мой ответ.
Может ли один и тот же идентификатор быть в id1 и id2? Их нужно считать отдельно?
если у двух клиентов одинаковые имена, вы хотите, чтобы в результате были получены две строки или только одна?
Почему отрицательный голос? Я показал примеры данных, ожидаемый результат, поиск, который я сделал, и мои попытки.
@ysth Было бы довольно странно объединять результаты для двух разных клиентов только потому, что у них по совпадению одно и то же имя. Хотя было бы неплохо включить в результат идентификатор, чтобы различать их.
Любопытно, что мой вопрос и оба хороших ответа были отклонены.
Ответы 2
Используйте оба id1 и id2 в своем ON состоянии.
SELECT t1.name, COUNT(t2.id) AS num
FROM customers t1
LEFT JOIN shifts t2
ON t1.id IN (t2.id1, t2.id2)
GROUP BY t1.name
Используйте LEFT JOIN и COUNT(t2.shift), чтобы получить нулевое количество клиентов, которые не работают ни в одной смене.
Это решение предполагает, что у вас нет id1 = id2, а если есть, каждый из них не должен участвовать в подсчете.
Вам также следует рассмотреть возможность нормализации таблицы shifts примерно так:
- идентификатор
- номер_шифта
- идентификатор_клиента
Есть какие-нибудь комментарии о том, почему этот ответ был отклонен? Если не считать мелких опечаток в коде, похоже, все работает.
Если shifts.id уникален, вы можете выполнить два левых соединения и использовать счетчик различных, как предлагает @ysth.
SELECT
t1.name,
COUNT(DISTINCT t2.id) + COUNT(DISTINCT t3.id) AS num
FROM customers t1
LEFT JOIN shifts t2 ON t2.id1 = t1.id
LEFT JOIN shifts t3 ON t3.id2 = t1.id
GROUP BY t1.name
Альтернативно вы можете использовать два подзапроса:
WITH shift1 AS (
SELECT
t1.name,
COUNT(t2.id1) AS num
FROM customers t1
LEFT JOIN shifts t2 ON t2.id1 = t1.id
GROUP BY t1.name
),
shift2 AS (
SELECT
t1.name,
COUNT(t3.id1) AS num
FROM customers t1
LEFT JOIN shifts t3 ON t3.id2 = t1.id
GROUP BY t1.name
),
shift_both AS (
SELECT
name,
num
FROM shift1
UNION ALL
SELECT
name,
num
FROM shift2
)
SELECT
name,
SUM(num) AS num
FROM shift_both
GROUP BY name
вам нужно будет сделать count(distinct t2.id) + count(distinct t3.id), чтобы избежать умножения результатов
Да, вы совершенно правы. Я отредактировал ответ.
Мне ваши два левых соединения понравились гораздо больше :)
Да, я тоже :) Сначала я не заметил, что shifts.id, скорее всего, должен быть уникальным. Я сохранил два левых соединения в ответе с вашим обновлением. Спасибо!
Да, shifts.id уникален. Решение LEFT JOIN работает отлично и очень элегантно.
Да, я имел в виду t2.id и t3.id... отличный улов!
@Марк, это была опечатка с моей стороны. Вы использовали последнюю версию с COUNT(DISTINCT t2.id)? Изначально я написал COUNT(DISTINCT t2.id1), что неверно.
Да, моя вина. Я не видел обновления. Я удалил комментарий, потому что в конце концов увидел его и снова протестировал. Все работает нормально, не беспокойтесь!
для этого вам нужен полный внешний joij, которого нет в MySQL, но посмотрите дублирующую ссылку