查询各分类的前几条记录

CREATE TABLE #Temp1
(
    tid int IDENTITY(1,1) PRIMARY KEY NOT NULL,
    TypeID int DEFAULT 0 NOT NULL,
    clickNum int DEFAULT 0 NOT NULL,
    txt varchar(200) DEFAULT '' NOT NULL
)

INSERT INTO #Temp1(TypeID,clickNum, txt)
SELECT 1, CONVERT(int,(rand()*(1000-0)+0)) ,'info'+ left(isnull(max(tid)+1,1), 6) FROM #Temp1
INSERT INTO #Temp1(TypeID,clickNum, txt)
SELECT 2, CONVERT(int,(rand()*(1000-0)+0)) ,'info'+ left(isnull(max(tid)+1,1), 6) FROM #Temp1
INSERT INTO #Temp1(TypeID,clickNum, txt)
SELECT 3, CONVERT(int,(rand()*(1000-0)+0)) ,'info'+ left(isnull(max(tid)+1,1), 6) FROM #Temp1
--多执行几次插入新数据
SELECT * FROM #Temp1

 

--查询各分类的前5条记录

SELECT t1.* FROM #Temp1 t1 WHERE tid IN
(SELECT TOP 5 tid FROM #Temp1 WHERE TypeID = t1.TypeID ORDER BY clickNum DESC) ORDER BY TypeID, clickNum DESC