MySQL obtains the first N records of all categories, and mysql obtains all categories.

MySQL obtains the first N records of all categories, and mysql obtains all categories.

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.