Как ограничить запрос значением столбца
Следующий запрос ...
SELECT event_id, user_id FROM EventUser WHERE user_id IN (1, 2)
... дает мне следующий результат:
+----------+---------+
| event_id | user_id |
+----------+---------+
| 3 | 1 |
| 2 | 1 |
| 1 | 1 |
| 5 | 1 |
| 4 | 1 |
| 6 | 1 |
| 4 | 2 |
| 2 | 2 |
| 1 | 2 |
| 5 | 2 |
+----------+---------+
Теперь я хочу изменить приведенный выше запрос, чтобы получить, например, только две строки для каждого user_id, например:
+----------+---------+
| event_id | user_id |
+----------+---------+
| 3 | 1 |
| 2 | 1 |
| 4 | 2 |
| 5 | 2 |
+----------+---------+
Я думаю о таком, что конечно не работает:
SELECT event_id, user_id FROM EventUser WHERE user_id IN (1, 2) LIMIT 2 by user_id
В идеале это должно работать и со смещениями, потому что я хочу использовать его для разбивки на страницы.
По соображениям производительности важно использовать часть запроса WHERE user_id IN (1, 2).
Также: это было бы легко в любом современном движке базы данных, либо через функцию окна APPLY / LATERAL JOIN, либо ROW_NUMBER (). К сожалению, MySql уже более десяти лет не является «современной» базой данных и не поддерживает ни того, ни другого.
@JoelCoehoorn Какие двигатели вы бы порекомендовали?
@JoelCoehoorn Первое n, упорядоченное по event_id со смещением m, я думаю
Любой из Sql Server, Oracle, Postgresql сделает это. Но, похоже, уже поздно менять платформы.
Ответы 4
Один из методов - при условии, что у вас есть как минимум две строки для каждого пользователя - будет:
(select min(event_id) as event_id, user_id
from t
where user in (1, 2)
group by user_id
) union all
(select max(event_id) as event_id, user_id
from t
where user in (1, 2)
group by user_id
);
По общему признанию, это не «общее» решение, но это может быть самое простое решение того, что вы хотите.
Если вам нужны два самых больших или самых маленьких, то также подойдет альтернатива:
select t.*
from t
where t.user_id in (1, 2) and
t.event_id >= (select t2.event_id
from t t2
where t2.user_id = t.user_id
order by t2.event_id desc
limit 1, 1
);
Насколько я понимаю, это работает только для двух строк на пользователя, верно? Однако мне нужно более общее решение для n строк.
@ HansMu158. . . Это отвечает на ваш вопрос, который заключается в получении двух строк на пользователя. На мой взгляд, изменение второго тривиально, чтобы получить больше строк, но если вы не понимаете, как это сделать, задайте другой вопрос.
Вот динамический пример таких проблем. Обратите внимание, что этот пример работает в SQL Server, пока не удалось попробовать mysql. Пожалуйста, дайте мне знать, как это работает.
CREATE TABLE mytable
(
number INT,
score INT
)
INSERT INTO mytable VALUES ( 1, 100)
INSERT INTO mytable VALUES ( 2, 100)
INSERT INTO mytable VALUES ( 2, 120)
INSERT INTO mytable VALUES ( 2, 110)
INSERT INTO mytable VALUES ( 3, 120)
INSERT INTO mytable VALUES ( 3, 150)
SELECT *
FROM mytable m
WHERE
(
SELECT COUNT(*)
FROM mytable m2
WHERE m2.number = m.number AND
m2.score >= m.score
) <= 2
Как насчет этого?
SELECT event_id, user_id
FROM (
SELECT event_id, user_id, row_number() OVER (PARTITION BY user_id) AS row_num
FROM EventUser WHERE user_id in (1,2)) WHERE row_num <= n;
А n может быть каким угодно
Вопрос помечен как MySql, а MySql не поддерживает оконные функции, такие как row_number ().
действительно ли выпущен MySql 8? Они все еще находятся в стадии "релиз-кандидата", а не в производстве.
Позже, но справка использует производную таблицу и перекрестное соединение. Для примера в этом посте запрос будет таким:
SELECT
@row_number:=CASE
WHEN @user_no = user_id
THEN
@row_number + 1
ELSE
1
END AS num,
@user_no:=user_id userid, event_id
FROM
EventUser,
(SELECT @user_no:=0,@row_number:=0) as t
group by user_id,event_id
having num < 3;
Больше информации в эта ссылка.
Как узнать, какие два события нужны для каждого user_id?