Как суммировать результаты двух разных запросов по разным столбцам?

Вот моя customers таблица:

идентификатор имя 1 абв 2 двоично-цифровой код 3 договориться 4 защита

и таблица shifts:

идентификатор идентификатор1 идентификатор2 1 1 2 2 1 3 1 3

мой ожидаемый результат:

имя число абв 3 двоично-цифровой код 1 договориться 1 защита 0

Поэтому я просто хочу посчитать, сколько раз 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 второго запроса.

Как суммировать результаты двух разных запросов по разным столбцам?

🤔 А знаете ли вы, что...
SQL может использоваться для создания индексов, что ускоряет выполнение запросов.


50
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