資料庫中遊標的經典應用

表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