This article will share with you how to use mysql to obtain the first N records of all categories. This article provides three examples. For more information, see. For example, if you have an Article table Article (Id, Category, InsertDate), you need to use SQL to find a set of the first N data records of the latest time in each type. it has a good piece of code and is reserved.
SELECT A1.* FROM Article AS A1 INNER JOIN (SELECT A.Category,A.InsertDate FROM Article AS A LEFT JOIN Article AS B ON A.Category = B.Category AND A.InsertDate <= B.InsertDate GROUP BY A.Category,A.InsertDate HAVING COUNT(B.InsertDate) <= @N ) AS B1 ON A1.Category = B1.Category AND A1.InsertDate = B1.InsertDate ORDER BY A1.Category,A1.InsertDate DESC
@ N is the number of entries you need
The following is a product classification table. superId is a big classification, and prcid is a product classification. If you can use SQL, you must use SQL statements as much as possible, which is concise and efficient.
SELECT A1.*FROM prcKx AS A1INNER JOIN ( SELECT A.superId, A.prcid FROM prcKx AS A LEFT JOIN prcKx AS B ON A.superId = B.superId AND A.prcid <= B.prcid GROUP BY A.superId, A.prcid HAVING COUNT(B.prcid) <= 7) AS B1 ON A1.superId = B1.superIdAND A1.prcid = B1.prcidORDER BY superId, prcid
The requirement is as follows (a problem on CSDN): There is a table in mysql: article (field: id, type, date), type has 1-10, 10 types. Now we need to use SQL to find a collection of the first N data of the latest time in each type.
There should be many ways to implement this problem. next we will talk about the implementation of a master we can see on the Internet (using an SQL statement, I personally feel very good, so I will share with you ):
select a1.* from article a1inner join(select a.type,a.date from article a left join article bon a.type=b.type and a.date<=b.date group by a.type,a.datehaving count(b.date)<=2)b1on a1.type=b1.type and a1.date=b1.dateorder by a1.type,a1.date desc
Note: 2 in the preceding SQL statement represents N as mentioned above.
The above is all the content of this article. I hope you will like it.