tb_searchkey的searchkey和tbl_keywords的id关联表:tb_searchkey id searchkey 25 32 26 15 5 5 12 16tbl_keywords id kw_mains 32 宝马系统 15 iloveyou 5 hi
select A.kw_mains,A.id,[count]=(select sum(id) from tb_searchkey where searchkey=A.id) from tbl_keywords A order by [count] desc
select A.kw_mains,A.id,[count]=(select sum(id) from tb_searchkey where searchkey=A.id) into # from tbl_keywords A order by [count] descselect [kw_mains]=(case when (select count(1) from # where [count]>=A.count)<=3 then 'java'+kw_mains else kw_mains end), id,[count] from # A drop table #
是不是这个意思啊! select top 10 case when count(t.id) in (select top 3 t.id from g.dbo.tb_searchkey t, r2004.dbo.tbl_keywords k where t.searchkey=k.id) then 'java'+Kw_mains else Kw_mains end As Kw_mains, searchkey, count(t.id) as cid from g.dbo.tb_searchkey t,r2004.dbo.tbl_keywords k where t.searchkey=k.id group by Kw_mains,searchkey order by cid desc
1.不妨考虑这样做 在Asp中使用sql=....?这个sql语句不会写--就你原来那样写呀 //因为你有order by ,那么cid的前3笔纪录如果有就是排在前3笔 dim i i = 1 set rs=conn.execute(sql) if not rs.eof then do while not rs.eof if i<= 3 then --用变量定义输出了多少笔纪录,还是前3笔,就输出"java" + rs("kw_mains") Response.Write("java"&rs("kw_mains")) else Response.write(rs("kw_mains")) --否则输出rs("kw_mains") end if i = i + 1 rs.movenext loop end if 2.楼主这样写查询语句,我有点疑问,你这样写,相当于是依照tabel tbl_keywords 的两个field分组,这样会出现cid相等的情况,就是我们都出现了同样的次数,这个时候,你的排名准吗?另外如果tbl_keywords的纪录值没有匹配在tb_searchkey 里面,你的语句不会出现xx,xx,0(cid)这样的纪录, 改为这样或许更好: select top 10 Kw_mains,searchkey,count(t.id) As cid ,NEWID() from r2004.dbo.tbl_keywords k left join g.dbo.tb_searchkey t on t.searchkey=k.id group by Kw_mains,searchkey order by cid desc
b.t.w 为了保险:在asp里面: if not rs.eof then rs.movefirst ..... end if
id searchkey
25 32
26 15
5 5
12 16tbl_keywords
id kw_mains
32 宝马系统
15 iloveyou
5 hi
from tbl_keywords A order by [count] desc
into #
from tbl_keywords A order by [count] descselect [kw_mains]=(case when (select count(1) from # where [count]>=A.count)<=3 then 'java'+kw_mains else kw_mains end),
id,[count]
from # A
drop table #
select top 10 case when count(t.id) in (select top 3 t.id
from g.dbo.tb_searchkey t,
r2004.dbo.tbl_keywords k
where t.searchkey=k.id)
then 'java'+Kw_mains
else Kw_mains
end As Kw_mains,
searchkey,
count(t.id) as cid
from g.dbo.tb_searchkey t,r2004.dbo.tbl_keywords k where t.searchkey=k.id group by Kw_mains,searchkey order by cid desc
在Asp中使用sql=....?这个sql语句不会写--就你原来那样写呀
//因为你有order by ,那么cid的前3笔纪录如果有就是排在前3笔
dim i
i = 1
set rs=conn.execute(sql)
if not rs.eof then
do while not rs.eof
if i<= 3 then --用变量定义输出了多少笔纪录,还是前3笔,就输出"java" + rs("kw_mains")
Response.Write("java"&rs("kw_mains"))
else
Response.write(rs("kw_mains")) --否则输出rs("kw_mains")
end if
i = i + 1
rs.movenext
loop
end if
2.楼主这样写查询语句,我有点疑问,你这样写,相当于是依照tabel tbl_keywords 的两个field分组,这样会出现cid相等的情况,就是我们都出现了同样的次数,这个时候,你的排名准吗?另外如果tbl_keywords的纪录值没有匹配在tb_searchkey 里面,你的语句不会出现xx,xx,0(cid)这样的纪录,
改为这样或许更好:
select top 10 Kw_mains,searchkey,count(t.id) As cid ,NEWID()
from r2004.dbo.tbl_keywords k left join g.dbo.tb_searchkey t
on t.searchkey=k.id
group by Kw_mains,searchkey
order by cid desc
为了保险:在asp里面:
if not rs.eof then
rs.movefirst
.....
end if
不过可能跟你要求的结果不同!!