如何获取每个用户的最新记录?
问题描述:
我有两张表,一张是登录名,一张是公司的详细信息.下面是表的结构.(我试图在线添加 SQL 查询和表,但出现错误 Help: near "ENGINE": syntax error
.所以我在下面添加了它)
I have two tables, one is the login and the second is the details of the company.
The below are the structure of the table. (I was trying online to add the SQL query and table but I am getting the error Help: near "ENGINE": syntax error
. So I added it below)
登录
login_id | username | password
1 | abc | ****
2 | xyz | ****
3 | oiu | ****
公司
company_id | company_name | content | size | date_of_added | login_id
1 | oiuyt | some content | 12 | 2021-05-05 | 1
2 | lddjl | some content | 13 | 2021-05-05 | 2
3 | kjhgf | some content | 15 | 2021-05-05 | 1
4 | qwedrr | some content | 16 | 2021-05-05 | 1
5 | qwesd | some content | 20 | 2021-05-05 | 3
6 | uwewe | some content | 09 | 2021-05-05 | 2
我的问题是,我必须显示每个登录用户的最新记录.我的预期输出是,
My issue is, I have to show the latest records of each login user. My expected output is,
company_id | company_name | content | size | date_of_added | login_id | username
4 | qwedrr | some content | 16 | 2021-05-05 | 1 | abc
6 | uwewe | some content | 09 | 2021-05-05 | 2 | xyz
5 | qwesd | some content | 20 | 2021-05-05 | 3 | oiu
and so on
我尝试了下面的查询,但我得到了所有的记录
I tried the below query but I am getting all the records
select l.*, c.*
from
login l
left join company c
on l.login_id = c.login_id
and c.date_of_created = (
select max(date_of_created)
from company c1
where c1.company_id = c.company_id
)
答
假设最新"是具有最大 id
":
Assuming that "latest" is "with maximal id
":
SELECT c1.*, login.username
FROM company c1
JOIN ( SELECT MAX(c2.company_id) company_id, c2.login_id
FROM company c2
GROUP BY c2.login_id ) c3 USING (company_id, login_id)
JOIN login USING (login_id)