Brand:(PKID)
Mobile:(PKID,PKIDBrand)
News:(PKID,PKIDMobile,Caption,Date....)现在我要从每个手机品牌中抽取5条最新的新闻
SELECT * FROM News A JOIN Mobile ON A.PKIDMobile=Mobile.PKID JOIN Brand ON Mobile.PKIDBrand=Brand.PKID
WHERE 6>(SELECT COUNT(*) FROM News WHERE a.PKIDMobile=PKIDMobile and a.Date>=Date)
--这段是从每款手机中取出5条最新的新闻
--每个品牌的最新5条新闻怎么取?
--try:
select * from news a where pkid in
(select top 5 pkid from news where pkidmobile=a.pkidmobile order by date desc)
(select top 5 pkid from news where a.pkidmobile = pkidmobile order by date desc)
(select top 5 pkid from news where pkidmobile=a.pkidmobile order by date desc)
select * from (select a.*,b.pkidbrand from news a,pkidbrand b
where a.pkidmobile=b.pkid)c
where pkid in
(select top 5 pkid from (select a.*,b.pkidbrand from news a,pkidbrand b
where a.pkidmobile=b.pkid)a where pkidbrand =c.pkidbrand order by date desc)
这样来:select brand.* from news join mobile on news.pkidMobile=mobile.pkid join brand on mobile.pkidbrand=brand.pkid
results:
PKID: Name Link:........
32 Google http://detail.zol.com.cn/cell_phone_index/subcate57_1922_list_1.html NULL 2008-05-15 10:50:40.703
32 Google http://detail.zol.com.cn/cell_phone_index/subcate57_1922_list_1.html NULL 2008-05-15 10:50:40.703
32 Google http://detail.zol.com.cn/cell_phone_index/subcate57_1922_list_1.html NULL 2008-05-15 10:50:40.703
31 阿尔卡特 http://detail.zol.com.cn/cell_phone_index/subcate57_531_list_1.html NULL 2008-05-15 10:50:40.703
31 阿尔卡特 http://detail.zol.com.cn/cell_phone_index/subcate57_531_list_1.html NULL 2008-05-15 10:50:40.703
31 阿尔卡特 http://detail.zol.com.cn/cell_phone_index/subcate57_531_list_1.html NULL 2008-05-15 10:50:40.703
我现在希望从每个PKID中选出最新的5条记录
select * from (select a.*,b.pkidbrand from news a,pkidbrand b
where a.pkidmobile=b.pkid)c这个时候的pkid到底是哪个表的??是mobile吗?那么sql是:
select * from (select a.*,b.pkidbrand from news a,mobile b
where a.pkidmobile=b.pkid)c
where pkid in
(select top 5 pkid from (select a.*,b.pkidbrand from news a,mobile b
where a.pkidmobile=b.pkid)a where pkidbrand =c.pkidbrand order by date desc)运行成功,需要4秒,,,我现在正在分析其中的逻辑关系..稍等...
where PKID in
(select top 5 PKID from (select news.pkid,news.pkidMobile,news.Date,mobile.pkidbrand from news ,mobile
where news.pkidmobile=mobile.pkid)temp2 where pkidbrand =temp.pkidbrand order by date desc)有没有更简单的写法......