每个SubSection拿一个record

V4ndrake

sql =

1. 
   
2. SELECT loccon.ID AS ID, locpro.ID as locproID, locsec.ID as locsecID, locsec.sectionName AS locsecName, loccon.content AS content, loccon.rating AS rating, user.ID AS userID, user.username AS username, type.ID AS typeID, type.typeName AS typeName
   
3. FROM live_locationprofile locpro, live_locationsubsection locsec, live_locationcontent loccon, live_user user, live_usertype type
   
4. WHERE locpro.ID = loccon.locproID AND locsec.ID = loccon.subsectionID AND user.ID = loccon.userID AND user.typeID = type.ID AND locpro.ID = 1
   
5. GROUP BY locsec.ID, loccon.rating
   
6. ORDER BY loccon.rating DESC

复制代码

如果我要从live_locationcontent里面拿出每个subsection内最高分（rating）的record，我应该怎么写？

我试过这个，但是只是拿到里面最高分的entry，而不是每个subsection一个

1. SELECT loccon.ID AS ID, locpro.ID as locproID, locsec.ID as locsecID, locsec.sectionName AS locsecName, loccon.content AS content, loccon.rating AS rating, user.ID AS userID, user.username AS username, type.ID AS typeID, type.typeName AS typeName
   
2. FROM live_locationprofile locpro, live_locationsubsection locsec, live_locationcontent loccon, live_user user, live_usertype type
   
3. WHERE locpro.ID = loccon.locproID AND locsec.ID = loccon.subsectionID AND user.ID = loccon.userID AND user.typeID = type.ID AND locpro.ID = 1 GROUP BY locsec.ID
   
4. HAVING MAX(loccon.rating)

复制代码

我的table definition基本上就在sql里面

[ 本帖最后由 V4ndrake 于 2-11-2007 03:54 AM 编辑 ]

kristy

很亂，下次簡化點，看到很累

你的意思是live_locationcontent 拿每個 loccon.ID最高的rating 出來是嗎？

如果是的話，用subquery可以解決你的問題
簡化你的table下，回復你

select loccon.ID as ID, lopro.ID as locrpoID, loccon.content as content,
(select max(b.rating) from live_locationcontent b where loccon.ID=b.ID) as Rating
from live_locationcontent ,live_locationprofile locpro
where locpro.ID = loccon.locproI

不曉得這個是不是你問的問題

V4ndrake

首先要Group我的loccon table里面的entry，以loccon.sectionID来group

然后在排列好的result entries里面，每个sectionID的组中，挑出最高rating的

好像有点晕～

看来真的非得要做subquery不可了

[ 本帖最后由 V4ndrake 于 2-11-2007 03:54 AM 编辑 ]

kristy

sectionID != loccon.ID ?
方法類似我給你的例子，你可以參考

V4ndrake

不晓得，我还没时间试

要简化的话，就只是query 一个table（我笨到忘记了简化，真抱歉）

1. 
   
2. SELECT ID, content, rating, ranking, subsectionID, locproID
   
3. FROM live_locationcontent
   
4. WHERE locproID = 1
   
5. GROUP BY subsectionID
   
6. HAVING MAX(rating)
   

复制代码

可是只是有最高rating的record
我要的是每个subsection里面抽一个最高rating的
所以应该是要nested query了对不对？

kristy

照你的寫法，是要拿出最高的rating罷了

你到底是要每個SubSectionID 中,拿出最高rating 還是什麽？？

SubSectionID	Rating
1	95
1	50
2	45
2	90

得出

SubSectionID	Rating
1	95
2	90

如果是的話，你照#2 寫法subquery 的方式

V4ndrake

原帖由 kristy 于 2-11-2007 03:18 PM 发表
照你的寫法，是要拿出最高的rating罷了

你到底是要每個SubSectionID 中,拿出最高rating 還是什麽？？
SubSectionIDRating
1
95
1
50
2
45
2
90


得出
SubSectionIDRating
1
95
2
...

就是这样，谢谢你～