比如有文章表 Article(Id,Category,InsertDate),現在要用SQL找出每種類型中時間最新的前N個數據組成的集合,一段不錯的代碼,留存備用
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 就是你要取多少條
下面是我用到了一個產品分類表中,superId是大分類,prcid是產品分類。能用SQL完成的功能就要盡量用SQL語句來完成,這既簡潔又高效。
SELECT A1.* FROM prcKx AS A1 INNER 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.superId AND A1.prcid = B1.prcid ORDER BY superId, prcid
需求是這樣的(CSDN上的一個問題):mysql中有個表:article(字段:id,type,date),type有1-10,10種類型。現在要用SQL找出每種類型中時間最新的前N個數據組成的集合。
這個問題應該有很多方法可以實現,下面就來說說在網上看到的一位高手的實現(用一條SQL語句實現的,個人感覺非常好,所以拿來和大家分享):
select a1.* from article a1 inner join (select a.type,a.date from article a left join article b on a.type=b.type and a.date<=b.date group by a.type,a.date having count(b.date)<=2 )b1 on a1.type=b1.type and a1.date=b1.date order by a1.type,a1.date desc
注:上面sql語句中的2代表的就是前面提到的N。
以上所述就是本文的全部內容了,希望大家能夠喜歡。