MySQL Group + sort + Limit

Now there is an interesting situation, when the user login success, to display the user's last login information, time and IP, and so on. However, in the User login information table, the user's logon process is not recorded, only the account password of the basic information, the user's actions are all recorded in the log table.

Also, when a user logs on successfully, they will automatically add a record to the log table, which has been resolved in the Triggers section of the previous two days. So, we need to do three things:

1: Find all login records for the user in the log table

2: Sort by Time descending

3: Find the second row of records and return.

Why is the second row, because the first line is the user's current login information, the second line is the last login information.

Create a simple log table first, time or timestamp type, do not need to add it yourself. Content storage operation types, such as login logout

Create Table Log (    intnotnullprimarykey  auto_increment,     timestamp,    varchar(+),    　　user_id  int  )

Inserting records, I did not insert them all at once, and there was a time separation between the actions.

Insert  into Log(Content,user_id)Values('Login',2);Insert  into Log(Content,user_id)Values('Login',4);Insert  into Log(Content,user_id)Values('Logout',4);Insert  into Log(Content,user_id)Values('Logout',2);Insert  into Log(Content,user_id)Values('Login',2);Insert  into Log(Content,user_id)Values('Logout',2);Insert  into Log(Content,user_id)Values('Login',2);

　　

By the above table we can see that the user 2 login log out login log out and then log in. Assuming that the last login was performed by User 2, we have to get the contents of ID 5 to display the last login information. Seeing this we found that it is possible to sort the time and sort the ID.

1: GROUP BY

　　

We can see that it is divided into two groups through user_id, each group shows the first record after grouping, obviously this is not enough to meet our needs. We need to get all the content of this user_id=2 group.

2:where sentence

In fact, there is no grouping, we can get the concrete content through the WHERE clause.

　　

where and We can get the contents of all the login operations of user 2.

3: Sort ORDER BY

　　

DESC is descending ASC is ascending. The syntax is simple, too.

Now that we have the above content, we need to get the second line of records.

4: Limit Limit

　　

The error, said table to have an alias. Let's take a look at table with a name.

　　

Finally get the second line of the record. And look at what we've entered.

　　

Select *  from(Select *  from Log  asLwhereL.user_id=2  andL.content='Login' Order  byIddesc) asLo Limit1,1;

(SELECT * from log as L where l.user_id=2 and l.content= ' login ' ORDER BY id DESC) is given a table and then we query it on the basis of this table.

Limit N,m is said to find records between N and M starting from 0. We only need the second record, so it's limit 1, 1.

MySQL Group + sort + Limit