Вопрос:
У меня есть пользователи со многими сообщениями. Я хочу построить SQL-запрос, который будет делать следующее в 1 запросе (без подзапроса), и, надеюсь, нет союзов, если это возможно. Я знаю, что могу сделать это с помощью союза, но хочу узнать, можно ли это сделать, используя только соединения.
Я хочу получить список отдельных активных пользователей, которые:
- нет сообщений
- не имеют утвержденных должностей
Вот что у меня есть до сих пор:
SELECT DISTINCT u.* FROM users u LEFT JOIN posts p ON p.user_id = u.id LEFT JOIN posts p2 ON p2.user_id = u.id WHERE u.status = ‘active’ AND (p.status IS NULL OR p2.status != ‘approved’);
Проблема в том, что пользователь имеет несколько сообщений, а один – активен. Это все равно вернет пользователя, которого я не хочу. Если у пользователя активная должность, он должен быть удален из результирующего набора. Есть идеи?
Вот как выглядят данные:
mysql> select * from users; +—-+———+ | id | status | +—-+———+ | 1 | active | | 2 | pending | | 3 | pending | | 4 | active | | 5 | active | +—-+———+ 5 rows in set (0.00 sec) mysql> select * from posts; +—-+———+———-+ | id | user_id | status | +—-+———+———-+ | 1 | 1 | approved | | 2 | 1 | pending | | 3 | 4 | pending | +—-+———+———-+ 3 rows in set (0.00 sec)
Ответ здесь должен быть только у пользователей 4 и 5. 4 не имеет утвержденной должности, а у 5 нет должности. Он не должен включать 1, который имеет утвержденную должность.
Лучший ответ:
Принимая ваши требования и переведя их буквально в SQL, я получаю следующее:
SELECT users.id, COUNT(posts.id) as posts_count, COUNT(approved_posts.id) as approved_posts_count FROM users LEFT JOIN posts ON posts.user_id = users.id LEFT JOIN posts approved_posts ON approved_posts.status = ‘approved’ AND approved_posts.user_id = users.id WHERE users.status = «active» GROUP BY users.id HAVING (posts_count = 0 OR approved_posts_count = 0);
Для ваших тестовых данных выше, это возвращает:
4|1|0 5|0|0
т.е. пользователи с идентификаторами 4 и 5, первая из которых имеет 1 должность, но не утвержденные должности, а вторая из которых не имеет должностей.
Однако мне кажется, что это может быть упрощено, поскольку любой пользователь, у которого нет утвержденных должностей, также не имеет сообщений, поэтому объединение условий не является необходимым.
В этом случае SQL просто:
SELECT users.id, COUNT(approved_posts.id) as approved_posts_count FROM users LEFT JOIN posts approved_posts ON approved_posts.status = ‘approved’ AND approved_posts.user_id = users.id WHERE users.status = «active» GROUP BY users.id HAVING approved_posts_count = 0;
Это также возвращает те же два пользователя. Я что-то упускаю?
Ответ №1
Не существует:
SELECT u.* FROM users u WHERE NOT EXISTS ( SELECT 1 FROM posts p WHERE p.user_id = u.id AND p.status = ‘approved’);
Или эквивалент LEFT JOIN
SELECT u.* FROM users u LEFT JOIN posts p ON p.user_id = u.id AND p.status = ‘approved’ WHERE p.user_id IS NULL; Ответ №2
Пожалуйста, объясните, почему вы не хотите JOINs или UNION. Если это из-за производительности, рассмотрите следующее:
CREATE TABLE t ( PRIMARY KEY(user_id) ) SELECT user_id, MIN(status) AS z FROM Posts GROUP BY user_id; SELECT u.id AS user, IFNULL(z, ‘no_posts’) AS status FROM users u WHERE u.status = ‘active’ LEFT JOIN t ON t.user_id = u.id HAVING status != ‘approved’;
Он будет выполнять только один проход по каждой таблице, тем самым будучи достаточно эффективным (учитывая сложность запроса).
Ответ №3
Это может помочь:
SELECT DISTINCT u.* FROM users u LEFT JOIN posts p ON 1=1 — matches only if user has any post AND p.user_id = u.id — matches only if user has any active post AND p.status = ‘approved’ WHERE 1=1 — matches only active users AND u.status = ‘active’ — matches only users with no matches on the LEFT JOIN AND p.status IS NULL ; Ответ №4
Я думаю, это должно быть легко.
SELECT u.’id’, u.’status’ FROM ‘users’ u LEFT OUTER JOIN ‘post’ p ON p.’user_id’ = u.’id’ AND p.’status’ = ‘approved’ WHERE u.’status’ = ‘active’ AND p.’id’ IS NULL
Дает результат 4 и 5.
[Edit] Просто хотел добавить, почему это работает:
и. status= ‘active’
Это приводит к исключению всех неактивных пользователей.
п. status= ‘approved’
Это исключает все утвержденные должности.
Следовательно, используя эти две строки, мы исключили всех пользователей, которые квалифицируются как одобренные для ваших критериев.
[Редактировать 2]
Если вам также нужно знать, сколько ожидающих и сколько одобрено, вот обновленная версия:
SELECT u.’id’, u.’status’, SUM(IF(p.’status’ = ‘approved’, 1, 0)) AS ‘Approved_Posts’, SUM(IF(p.’status’ = ‘pending’, 1, 0)) AS ‘Pending_Posts’ FROM ‘test_users’ u LEFT OUTER JOIN ‘test_post’ p ON p.’user_id’ = u.’id’ WHERE u.’status’ = ‘active’ GROUP BY u.’id’ HAVING SUM(IF(p.’id’ IS NOT NULL, 1, 0)) Ответ №5
Вы можете попробовать следующий запрос:
SELECT DISTINCT u.* FROM users u LEFT JOIN posts p ON p.user_id = u.id WHERE u.status = ‘active’ AND ( p.user_id IS NULL OR p.status != ‘approved’);
РЕДАКТИРОВАТЬ
В соответствии с обновленным вопросом вышеупомянутый запрос будет включать пользователя 1. Если мы хотим предотвратить это и не хотим использовать внутренний запрос, мы можем использовать функцию group_concat MySQL для получения всех (отдельных) статусов и посмотреть, содержит “активный” статус, ниже запрос должен давать желаемый результат:
SELECT u.id, group_concat(distinct p.status) as statuses FROM users u LEFT JOIN posts p ON u.id = p.user_id WHERE u.status = ‘active’ group by u.id having (statuses is null or statuses not like ‘%approved%’); Ответ №6
Попробуй это
SELECT DISTINCT u.* FROM users u LEFT JOIN posts p ON p.user_id = u.id WHERE p.status IS NULL OR p.status != ‘approved’;
