MySQL 8.0

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

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

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