Сравнить две таблицы MYSQL и вернуть пропущенные даты и сгруппировать по столбцам

#mysql #sql #date #join #mysql-workbench

#mysql #sql #Дата #Присоединиться #mysql-workbench

Вопрос:

У меня есть две таблицы. Я хочу сравнить таблицу A с B и получить недостающую дату из с именем пользователя.

ТАБЛИЦА A

 |----|----------|------------|
| 1  | king     | 2020-08-01 |
| 2  | king     | 2020-08-02 |
| 3  | queen    | 2020-08-01 |
| 4  | queen    | 2020-08-02 |
| 5  | rook     | 2020-08-03 |
| 6  | bishop   | 2020-08-01 |
| 7  | bishop   | 2020-08-01 |
| 8  | queen    | 2020-08-03 |

ТАБЛИЦА B

 | id | working_date |
|----|--------------|
| 1  | 2020-08-01   |
| 2  | 2020-08-02   |
| 3  | 2020-08-03   |

ОЖИДАЕМЫЙ РЕЗУЛЬТАТ

 | name   | missing_date |
|--------|--------------|
| king   | 2020-08-03   |
| rook   | 2020-08-01   |
| rook   | 2020-08-02   |
| bishop | 2020-08-02   |
| bishop | 2020-08-03   |

Также, вместе с этим, могу ли я получить количество пропущенных дат для каждого пользователя, если это возможно?

Ответ №1:

Вы должны перекрестно соединить таблицу B с различными именами таблицы A, а затем соединить таблицу A слева, чтобы отфильтровать совпадающие строки:

 select n.name, b.working_date missing_date 
from TableB b
cross join (select distinct name from TableA) n
left join TableA a on a.name = n.name and a.working_date = b.working_date
where a.id is null

Если вы хотите подсчитать недостающие даты, используйте тот же запрос и сгруппируйте по имени:

 select n.name, count(*) missing_dates 
from TableB b
cross join (select distinct name from TableA) n
left join TableA a on a.name = n.name and a.working_date = b.working_date
where a.id is null
group by n.name

Посмотрите демонстрацию.
Результаты:

 > name   | missing_date
> :----- | :-----------
> king   | 2020-08-03  
> rook   | 2020-08-01  
> rook   | 2020-08-02  
> bishop | 2020-08-02  
> bishop | 2020-08-03

и:

 > name   | missing_dates
> :----- | ------------:
> bishop |             2
> king   |             1
> rook   |             2

1. Спасибо 🙂 , это было очень полезно. Но могу ли я получить оба запроса в виде одной таблицы?

2. Какая у вас версия MySQL?

3. Тогда вы не сможете использовать оконные функции, которые были введены в версии 8.0, поэтому вы можете получить то, что хотите, только если вы объедините эти 2 запроса: dbfiddle.uk /…

4. Для MySQL 8.0 решение простое: dbfiddle.uk /…

Ответ №2:

Получая разные имена пользователей из таблицы A и объединяя ее с таблицей B, вы можете создать все варианты рабочих дат пользователей. Затем с помощью not exists вы можете перечислить отсутствующие даты:

 SELECT A1.name, B.working_date
FROM B JOIN
  (SELECT DISTINCT name
   FROM A) A1
WHERE NOT EXISTS (
  SELECT *
  FROM A
  WHERE A.name = A1.name
    and A.working_date = B.working_date
)

Для вашего последнего вопроса о поиске недостающих показателей вы можете использовать приведенный ниже запрос:

 SELECT S.name, COUNT(1) AS 'MissingDatesCount'
FROM (
    SELECT A1.name, B.working_date
    FROM B JOIN
      (SELECT DISTINCT name
       FROM A) A1
    WHERE NOT EXISTS (
      SELECT *
      FROM A
      WHERE A.name = A1.name
        and A.working_date = B.working_date
    )
) S
GROUP BY S.name

Перейдите к SQLFiddle