Блог

У меня есть следующие 3 таблицы:

• пользователи: [идентификатор, имя, администратор …]
• события: [идентификатор, идентификатор пользователя, тип …]
• сообщения: [id, user_id, …]

Я хочу создать запрос, который выполняет следующее:

-> Выберите всех пользователей из таблицы, пользователи, которые не запланировали событие типа «коллекция»

-> И у кого меньше 3 сообщений типа «collection_reminder»

-> И кто не администратор

Мне удалось разобраться с первой частью этого запроса, но все это выглядит немного грушевидно, когда я пытаюсь добавить таблицу 3, выполнить подсчет и т.д.

Вот запрос, который может выполнить работу. Каждое из требований представлено как условие в WHERE предложении, при необходимости используя соответствующие подзапросы:

 SELECT u.*
FROM users u
WHERE 
    NOT EXISTS (
        SELECT 1 
        FROM events e 
        WHERE e.user_id = u.id AND e.type = 'collection'
    )
    AND (
        SELECT COUNT(*) 
        FROM messages m 
        WHERE m.userid = u.id AND m.type = 'collection_reminder'
    ) <= 3
    AND u.admin IS NULL
  

Я попробую это на макушке, поэтому ожидайте некоторых проблем с synthax, но идея заключается в следующем.

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

 select * from users u
left join events e on e.user_id = u.id
where e.user_id is null
  

Теперь я не думаю, что это самый эффективный способ, а простой способ поиска всех, у кого есть 3 или менее сообщений:

 select * from users u
left join events e on e.user_id = u.id
where u.id in (
   select COUNT(*) from messages m where m.user_id = u.id HAVING COUNT(*)>3;
)
and e.user_id is null
  

Тогда фильтровать, кто не является администратором, проще всего: D

 select * from users u
left join events e on e.user_id = u.id
where u.id in (
   select COUNT(*) from messages m where m.user_id = u.id HAVING COUNT(*)>3;
)
and e.user_id is null
and u.admin = false
  

Надеюсь, это поможет.

Это в значительной степени прямой перевод ваших требований в том порядке, в котором вы их перечислили:

 SELECT u.*
FROM users AS u
WHERE u.user_id NOT IN (SELECT user_id FROM events WHERE event_type = 'Collection')
   AND u.user_id IN (
      SELECT user_id 
      FROM messages 
      WHERE msg_type = 'Collection Reminder' 
      GROUP BY user_id 
      HAVING COUNT(*) < 3
   )
   AND u.admin = 0
  

или, альтернативно, это может быть полностью выполнено с помощью объединений:

 SELECT u.*
FROM users AS u
LEFT JOIN events AS e ON u.user_id = e.user_id AND e.event_type = 'Collection'
LEFT JOIN messages AS m ON u.user_id = m.user_id AND m.msg_type = 'Collection Reminder'
WHERE u.admin = 0
   AND e.event_id IS NULL        -- No event of type collection
GROUP BY u.user_id -- Note: you should group on all selected fields, and 
                   -- some configuration of MySQL will require you do so.
HAVING COUNT(DISTINCT m.message_id) < 3   -- Less than 3 collection reminder messages 
             -- distinct is optional, but 
             -- if you were to remove the "no event" condition, 
             -- multiple events could multiply the message count.
;
  

Этот запрос использует объединения для связывания 3 таблиц, фильтрует результат с помощью предложения where и с помощью Group by, ограничивая результат только теми, кто удовлетворяет условию «меньше, чем количество»..

 SELECT    a.id, 
      SUM(CASE WHEN b.type = 'collection' THEN 1 ELSE 0 END),
      SUM(CASE WHEN c.type = 'collection_reminder' THEN 1 ELSE 0 END
FROM      users a 
left join   events b on (b.user_id = a.id)
left join   messages c on (c.user_id = a.id)
WHERE     a.admin = false
GROUP BY  a.id
HAVING  SUM(CASE WHEN b.type = 'collection' THEN 1 ELSE 0 END) = 0 
    AND SUM(CASE WHEN c.type = 'collection_reminder' THEN 1 ELSE 0 END) < 3