数据库中游标的经典应用

表className中有如下分类:

classID   className
1              衣服
2              裤子
5              帽子
10            鞋子

表productInfo有如下记录:

productID             productName            parentID            clickNum

1                            男士衣服                      1                         90            --衣服类别中这条记录的点击率最高
2                            女士衣服                      1                         80
3                            男士裤子                      2                         70
4                            女士裤子                      2                         90            --裤子类别中这条记录点击率最高
5                            男士帽子                      5                         15
6                            女士帽子                      5                         30            --帽子类别中这条点击率最高
7                            男士鞋子                      10                       65            --鞋子类别中这条点击率最高
8                            女士鞋子                      10                       52
9                            女士鞋子1                    10                       54

现在要求分别把衣服,裤子,帽子,鞋子这些类别中点击率最高的一条记录找出来,然后再降序排列,结果应如下:

productID             productName            clickNum
1                            男士衣服                      90
4                            女士裤子                      90
7                            男士鞋子                      65
6                            女士帽子                      30

实现方法：

declare @temp table
(
 productID int,
 productName nvarchar(30),
 clickNum int
)
declare @classID int
declare cursor_classID cursor
for
select classID from dbo.className

open cursor_classID
fetch next from cursor_classID into @classID

--0 表示 FETCH 语句成功
while @@FETCH_STATUS=0
begin
  insert into @temp
  select top 1 productID,productName,clickNum from dbo.productInfo
  where parentID = @classID
  order by clickNum desc
 
  fetch next from cursor_classID into @classID
end

close cursor_classID

deallocate cursor_classID

select * from @temp order by clickNum desc