A long time ago, I had an SQL interview question and answer.

Today, I suddenly remembered a SQL interview question a long time ago. With the help of my colleagues, I finally realized it. I would like to express my gratitude to vivianhu (mm.
The specific data of the question cannot be remembered, but the meaning is the same. It must be completed in the query analyzer. The question is as follows:

The following types are available in the table classname:

Classid classname
1 clothes
2 pants
5 hats
10 shoes

The table productinfo has the following records:

Productid productname parentid clicknum

1 men's clothes 1 90 -- the highest click rate of this record in the clothing category
2 ladies clothes 1 80
3 men's trousers 2 70
4. Ladies pants 2 90 -- the highest click rate of this record in the trousers category
5 men's hats 5 15
6 ladies hats 5 30 -- the highest click rate in the hat category
7 men's shoes 10 65-the highest click rate in the shoes category
8 women's shoes 10 52
9 ladies shoes 1 10 54

Now we need to find the records with the highest click rate among clothes, trousers, hats, and shoes respectively, and sort them in descending order. The results should be as follows:

Productid productname clicknum
1 men's clothes 90
4 ladies pants 90
7 men's shoes 65
6 ladies hats 30

The implementation process is as follows:

/*
Function: Search for the records with the highest click rate under each category in the category table [classname], and sort these records in descending order.
Author: vivianhu
Sorting: kgdiwss (I just added comments and renamed some variables)
Date: 2006-4-17
*/

/* If a temporary table tTable exists, delete it first */
If exists (
Select * From DBO. sysobjects
Where id = object_id (n' [DBO]. [tTable] ') and objectproperty (ID, n' isusertable') = 1
)
Drop table [DBO]. [tTable]

Go

/* Create a temporary table */
Create Table tTable
(
Productid int,
Productname varchar (10 ),
Clicknum int
)

/* Variable Declaration */
Declare @ classid int

/* Define the cursor */
Declare cursor_classid
Cursor
Select classid from classname

/*
Open cursor
@ Fetch_status
Returns the status of the last cursor executed by the fetch statement, rather than the status of any cursor currently opened.
0 indicates that the fetch statement is successful.
*/
Open cursor_classid
Fetch next from cursor_classid into @ classid
While @ fetch_status = 0

/* Search for a record with the highest click rate in a category */
Begin

Insert into tTable
Select top 1 productid, productname, clicknum from productinfo
Where parentid = @ classid
Order by clicknum DESC

Fetch next from cursor_classid
Into @ classid

End

/* Close the cursor */
Close cursor_classid

/* Delete cursor reference */
Deallocate cursor_classid

/* Sort the records in the temporary table in descending order based on the click rate */
Select * From tTable order by clicknum DESC

/* Delete a temporary table */
Drop table tTable