从数据库中的每个用户检索最后插入的行

问题描述：

我想用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...

请在此处看到小提琴.

从数据库中的每个用户检索最后插入的行

相关推荐