SQL多表格查询合并至单一声明的常用方式

核心提示： 今天突然想起来很久以前的一个sql面试题,在同事的帮助下终于实现了.在此对vivianhu(MM)表示感谢．题目具体数据记不清了,但是大概意思一样,要求在查询分析器中完成,题目如下:表className中有如下分类:classID className1 衣服2 裤子5 帽子10 鞋子表prod

　　　  今天突然想起来很久以前的一个sql面试题,在同事的帮助下终于实现了.在此对vivianhu(MM)表示感谢．
题目具体数据记不清了,但是大概意思一样,要求在查询分析器中完成,题目如下:

表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

以下为实现过程:

/*
功能: 搜索类别表[className]中的每个类别下点击率最高的那条记录,然后将这些记录降序排列
作者: vivianhu
整理: kgdiwss(我只是添加了注释以及重命名了一些变量)
日期: 2006-4-17
*/

/* 如果存在临时表tTable,则先将它删除 */
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 table tTable
(
productid int,
productname varchar(10),
clicknum int
)

/*  变量申明 */
declare @classID int 

/*  定义游标 */
declare cursor_classID 　
CURSOR FOR
SELECT classID FROM className

/*
打开游标
@@FETCH_STATUS
返回被 FETCH 语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。
0 表示 FETCH 语句成功。
*/
OPEN cursor_classID
FETCH NEXT FROM cursor_classID INTO @classID
WHILE @@FETCH_STATUS = 0

/* 搜索某一类别中点击率最高的一条记录 */
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 cursor_classID

/* 删除游标引用 */
DEALLOCATE cursor_classID

/* 对临时表中的记录根据点击率进行降序排列 */
select * from tTable order by clickNum desc

/* 删除临时表 */
drop table tTable