Блог

У меня есть одна monitoring таблица со столбцами идентификаторов клиентов ID , дата последнего входа в приложение Time . Я написал запрос для отображения таблицы, в какое время клиенты имели доступ к системе, в виде: Время — Количество записей в это время — Идентификаторы клиентов.

Запрос:

 select Time, count (*) as Quantity, group_concat (ClientID) from monitoring group by Time;
 

Как мне написать следующий запрос? Отобразить таблицу в том же виде, только теперь необходимо каждый раз, когда хотя бы 1 клиент имел доступ, отображать id всех клиентов, у которых на тот момент не было доступа.

UPD.

  --------------------- ------------- ---------------- 
| Time                | Quantity    | ClientID       |                                                                                               
 --------------------- ------------- ----------------                                                                                                              
| 2018-06-14 15:51:03 |       3     | 311,240,528    |                                                                                                    
| 2018-06-14 15:51:20 |       3     | 314,312,519    |                                                                                                    
| 2019-01-14 06:00:07 |       1     | 359            |                                                                                                    
| 2019-08-21 14:30:04 |       1     | 269            |                                                                                                    
 --------------------- ------------- ---------------- 
 

Это идентификаторы клиентов, которые в настоящее время имели доступ. И вам нужно отобразить идентификаторы всех клиентов, у которых не было доступа в это конкретное время
То есть в этом случае:

  --------------------- ------------- ----------------------------- 
| Time                | Quantity    | ClientID                    |                                                                                               
 --------------------- ------------- -----------------------------                                                                                                             
| 2018-06-14 15:51:03 |           5 | 269,359,314,312,519         |                                                                                                    
| 2018-06-14 15:51:20 |           5 | 311,240,528,359,269         |                                                                                                    
| 2019-01-14 06:00:07 |           7 | 311,240,528,314,312,519,269 |                                                                                                    
| 2019-08-21 14:30:04 |           7 | 311,240,528,314,312,519,359 |                                                                                                    
 --------------------- ------------- ----------------------------- 
 

Желательно не учитывать день и время, а только год и месяц. Но как только он выйдет. Спасибо.

Вы можете сгенерировать все возможные комбинации клиентов и времени с помощью одного cross join из двух select distinct подзапросов, а затем отфильтровать те, которые существуют в таблице not exists . Последний шаг — агрегирование:

 select t.time, count(*) as quantity, group_concat(c.clientid) as clientids
from (select distinct time from monitoring) t
cross join (select distinct clientid from monitoring) c
where not exists (
    select 1
    from monitoring m
    where m.time = t.time and m.clientid = c.clientid
)
group by t.time
 

Мне непонятно, что вы подразумеваете под последним предложением в вопросе. Приведенный выше запрос сгенерирует результаты, которые вы показали для своих выборочных данных.