有以下结构的数据表Cate Item Cnt
101 2539288 10
101 2539288 8
101 2539288 5
101 2539288 1
101 2539289 1
103 2819669 5
103 2539890 2
103 2539961 2
103 2539962 2
103 2539925 2
106 2722811 4
106 2722786 1
106 2844326 1
112 1888177 20
112 1888996 14
112 1889005 10
112 1889761 9
112 1889384 8
112 1889596 7现在我想按Cnt的倒叙排序,选取每个Cate对应的Item的前3条记录,即:
101 2539288 10
101 2539288 8
101 2539288 5
103 2819669 5
103 2539890 2
103 2539961 2
106 2722811 4
106 2722786 1
106 2844326 1
112 1888177 20
112 1888996 14
112 1889005 10
请问大家有什么好方法谢谢。
101 2539288 10
101 2539288 8
101 2539288 5
101 2539288 1
101 2539289 1
103 2819669 5
103 2539890 2
103 2539961 2
103 2539962 2
103 2539925 2
106 2722811 4
106 2722786 1
106 2844326 1
112 1888177 20
112 1888996 14
112 1889005 10
112 1889761 9
112 1889384 8
112 1889596 7现在我想按Cnt的倒叙排序,选取每个Cate对应的Item的前3条记录,即:
101 2539288 10
101 2539288 8
101 2539288 5
103 2819669 5
103 2539890 2
103 2539961 2
106 2722811 4
106 2722786 1
106 2844326 1
112 1888177 20
112 1888996 14
112 1889005 10
请问大家有什么好方法谢谢。
select *,rn= row_number()over(partition by item order by getdate()) from tb
) t where rn<4
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([Cate] [int],[Item] [int],[Cnt] [int])
INSERT INTO [tb]
SELECT '101','2539288','10' UNION ALL
SELECT '101','2539288','8' UNION ALL
SELECT '101','2539288','5' UNION ALL
SELECT '101','2539288','1' UNION ALL
SELECT '101','2539289','1' UNION ALL
SELECT '103','2819669','5' UNION ALL
SELECT '103','2539890','2' UNION ALL
SELECT '103','2539961','2' UNION ALL
SELECT '103','2539962','2' UNION ALL
SELECT '103','2539925','2' UNION ALL
SELECT '106','2722811','4' UNION ALL
SELECT '106','2722786','1' UNION ALL
SELECT '106','2844326','1' UNION ALL
SELECT '112','1888177','20' UNION ALL
SELECT '112','1888996','14' UNION ALL
SELECT '112','1889005','10' UNION ALL
SELECT '112','1889761','9' UNION ALL
SELECT '112','1889384','8' UNION ALL
SELECT '112','1889596','7'
-->SQL查询如下:
;WITH T AS
(
SELECT RN=ROW_NUMBER()OVER(PARTITION BY CATE ORDER BY CNT DESC),*
FROM [TB]
)
SELECT ITEM,CNT
FROM T
WHERE RN<=3
/*
ITEM CNT
----------- -----------
2539288 10
2539288 8
2539288 5
2819669 5
2539890 2
2539961 2
2722811 4
2722786 1
2844326 1
1888177 20
1888996 14
1889005 10(12 行受影响)
*/
insert into tb values(101, '2539288', 10)
insert into tb values(101, '2539288', 8)
insert into tb values(101, '2539288', 5)
insert into tb values(101, '2539288', 1)
insert into tb values(101, '2539289', 1)
insert into tb values(103, '2819669', 5)
insert into tb values(103, '2539890', 2)
insert into tb values(103, '2539961', 2)
insert into tb values(103, '2539962', 2)
insert into tb values(103, '2539925', 2)
insert into tb values(106, '2722811', 4)
insert into tb values(106, '2722786', 1)
insert into tb values(106, '2844326', 1)
insert into tb values(112, '1888177', 20)
insert into tb values(112, '1888996', 14)
insert into tb values(112, '1889005', 10)
insert into tb values(112, '1889761', 9)
insert into tb values(112, '1889384', 8)
insert into tb values(112, '1889596', 7)
goselect t.* from tb t where Cnt in (select top 3 Cnt from tb where Cate = t.Cate order by cnt desc) order by t.cate , t.cnt descdrop table tb/*
Cate Item Cnt
----------- ---------- -----------
101 2539288 10
101 2539288 8
101 2539288 5
103 2819669 5
103 2539890 2
103 2539961 2
103 2539962 2
103 2539925 2
106 2722811 4
106 2722786 1
106 2844326 1
112 1888177 20
112 1888996 14
112 1889005 10(所影响的行数为 14 行)*/