我有这个表。我需要选择registration=1的一行,然后选择session=1和lastcreated_at的一行。
2行-注册和最后一次会话
我试过row_number,但它在mysql中不起作用。(?(
如何编写联接查询?
在此处输入图像描述
应为在此处输入图像描述
first row: registration = 1 - user_id = 1
second row: session = 1 - user_id = 1 - created_at = max(created_at) where user_id = 1
这是一个带有相关子查询的联合,用于获取最后一个会话例如
drop table if exists u,m,memos,um;
create table u (id int auto_increment primary key,uID int, reg int, sess int,dt date);
insert into u (uid,reg,sess,dt) values
( 1 , 1,0,'2018-01-01') ,
( 1 , 0,1,'2018-01-01'),
( 1 , 0,1,'2018-02-01');
select u.* from u where reg = 1
union
select u.* from u where sess = 1 and id = (select max(id) from u u1 where u1.uid = u.uid)
order by uid,id;
+----+------+------+------+------------+
| id | uID | reg | sess | dt |
+----+------+------+------+------------+
| 1 | 1 | 1 | 0 | 2018-01-01 |
| 3 | 1 | 0 | 1 | 2018-02-01 |
+----+------+------+------+------------+