从数据库中的每个用户检索最后插入的行 [英] retrieve the last inserted row from each user in database

问题描述

我想用php在mysql中进行查询，并从数据库中的每个用户获取最后插入的行.让我给你举个例子.

I want to make a query in mysql with php and get the last inserted row from each user i have in a database. Let me give you an example.

假设我们有一个包含10行的表

Suppose we have a table with 10 rows

ID    Message   User     Date inserted

1      lala1    chris     13/02/2010 12:13
2      lala2    john      14/02/2010 12:14
3      lala3   george    15/03/2009 12:00
4      lala4    jack      01/04/2013 11:09
5      lala5    ina       12/08/2012 15:00
6      lala6    chris     13/03/2010 12:13
7      lala7    john      14/01/2010 12:04
8      lala8   george    15/02/2009 12:00
9      lala9    jack      01/03/2013 11:09
10     lala10    ina       12/05/2012 15:00

我想进行查询以按日期顺序从chris，john和ina获取最后插入的行.所以结果应该是这样:

I want to make a query to get the last inserted rows from chris,john and ina ordered by date. So the result should be this:

5      lala5    ina       12/08/2012 15:00
6      lala6    chris     13/03/2010 12:13
2      lala2    john      14/02/2010 12:14

然后在我从这些用户获得最后插入的行之后，我想再次查询以再次为这些用户获得先前最后插入的行，因此结果应为:

then after i get the last inserted rows from these users i want to make another query to get the previous last inserted rows for these users again so the result should be:

10     lala10    ina       12/05/2012 15:00
1      lala1    chris     13/02/2010 12:13
7      lala7    john      14/01/2010 12:04

以此类推...

任何帮助表示赞赏！

推荐答案

如果需要获取最后一个，然后获取之前的最后一个，依此类推，则需要使用排名功能:

If you need to get the last, and then the previous last, and so on, you need to use a ranking function:

SELECT *
FROM (
  SELECT
    Users.*,
    CASE WHEN @lst=User THEN @row:=@row+1 ELSE @row:=0 END row,
    @lst:=User
  FROM
    Users
  WHERE
    User IN ('ina','chris','john')
  ORDER BY
    User, Date_ins DESC
) s
WHERE
  row=0

row = 0将获取最后一个日期，row = 1将获取最后一个日期，依此类推...

row=0 will get the last date, row=1 the previous last, and so on...

请在此处看到小提琴.

这篇关于从数据库中的每个用户检索最后插入的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！