如何获取每个用户的最新记录? [英] How to get the latest records of the each user?

问题描述

我有两张表，一张是登录名，一张是公司的详细信息.下面是表的结构.(我试图在线添加 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)

这篇关于如何获取每个用户的最新记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！