sql 2005 一道题目....希望有人解答!!!!(效率越高越好!!!!)

0
[已解决问题] 解决于 2009-11-29 18:21

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

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

------------------ 这题目是在网上搜的..希望不要用网上的代码哦!!!

lx0802 | 初学一级 | 园豆：150

0

1楼写的很好！不过有点小粗心哦呵呵！

select productID,productName,clickNum

from

(select parentID,max(clickNum) as maxNum from productinfo group by parentID) as x

left outer join productinfo as y on x.parentID=y.parentID
and y.clickNum=x.maxNum
order by clickNum DESC

chocol@te | 菜鸟二级 |园豆：325 | 2009-11-24 10:32
!! 很细心哦!
lx0802 | 园豆：150 (初学一级) | 2009-11-26 16:45

0

select productID,productName,clickNum

from

(select parentID,max(clickNum) as maxNum from productInfo group by parentID)  x

left outer join productInfo y on x.parentID=y.parentID and x.clickNum=y.maxNum

order by clickNum DESC

0

daodaodao | 园豆：205 (菜鸟二级) | 2009-11-24 09:40

您需要登录以后才能回答，未注册用户请先注册