By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE user ( user_id INT PRIMARY KEY,
user_name VARCHAR(255));
INSERT INTO user VALUES ( 1, 'Иван' ),
( 2, 'Пётр' ),
( 3, 'Игорь' );
CREATE TABLE action ( action_id INT PRIMARY KEY,
user_id INT,
action VARCHAR(255),
action_date DATETIME );
INSERT INTO action VALUES ( 1, 1, 'login', '2022-02-02 11:22:33' ),
( 2, 2, 'login', '2022-02-02 11:22:50' ),
( 3, 1, 'edit', '2022-02-02 11:23:45' ),
( 4, 2, 'read', '2022-02-02 11:24:10' ),
( 5, 2, 'edit', '2022-02-02 11:24:54' ),
( 6, 1, 'logout', '2022-02-02 11:25:00' ),
( 7, 2, 'logout', '2022-02-02 11:25:44' );
SELECT * FROM user ORDER BY 1;
SELECT * FROM action ORDER by 2,4;
| user_id | user_name |
|---|---|
| 1 | Иван |
| 2 | Пётр |
| 3 | Игорь |
| action_id | user_id | action | action_date |
|---|---|---|---|
| 1 | 1 | login | 2022-02-02 11:22:33 |
| 3 | 1 | edit | 2022-02-02 11:23:45 |
| 6 | 1 | logout | 2022-02-02 11:25:00 |
| 2 | 2 | login | 2022-02-02 11:22:50 |
| 4 | 2 | read | 2022-02-02 11:24:10 |
| 5 | 2 | edit | 2022-02-02 11:24:54 |
| 7 | 2 | logout | 2022-02-02 11:25:44 |
-- только те пользователи, у которых есть действия
SELECT *
FROM user
JOIN action USING (user_id)
JOIN ( SELECT user_id, MAX(action_date) AS action_date
FROM action
GROUP BY 1 ) AS get_max_date USING (user_id, action_date)
| user_id | action_date | user_name | action_id | action |
|---|---|---|---|---|
| 1 | 2022-02-02 11:25:00 | Иван | 6 | logout |
| 2 | 2022-02-02 11:25:44 | Пётр | 7 | logout |
-- Все пользователи
SELECT *
FROM user
LEFT JOIN ( action
JOIN ( SELECT user_id, MAX(action_date) AS action_date
FROM action
GROUP BY 1 ) AS get_max_date USING (user_id, action_date) ) USING (user_id)
| user_id | user_name | action_date | action_id | action |
|---|---|---|---|---|
| 1 | Иван | 2022-02-02 11:25:00 | 6 | logout |
| 2 | Пётр | 2022-02-02 11:25:44 | 7 | logout |
| 3 | Игорь | null | null | null |