现有字段:
id,fromplace,toplace,scount,logyear,logmonth备注:
fromplace:出发地
toplace:目的地
scount:搜索次数
logyear:记录年
logmonth:记录月现在想用一条SQL语句查询每年每个月中,搜索次数在前20的记录
id,fromplace,toplace,scount,logyear,logmonth备注:
fromplace:出发地
toplace:目的地
scount:搜索次数
logyear:记录年
logmonth:记录月现在想用一条SQL语句查询每年每个月中,搜索次数在前20的记录
select *
from tb t
where id
in (select top 20 id from tb where t.logyear=logyear and t.logmonth=logmonth
order by scount desc)
现有字段:
id,stype,fromplace,toplace,scount,logyear,logmonth备注:
stype:搜索类型
fromplace:出发地
toplace:目的地
scount:搜索次数
logyear:记录年
logmonth:记录月现在想用一条SQL语句根据搜索类型查询每年每月中,搜索次数在前20的记录
(select count(*) from tb
where ltrim(logyear)+ltrim(logmonth)=a.ltrim(logyear)+a.ltrim(logmonth)
and 搜索次数 >a.搜索次数)
SELECT * FROM TABLENAME
WHERE ID IN (SELECT TOP 20 ID FROM TABLENAME ORDER BY SCOUNT DESC)
from tb t
where id
in (select top 20 id from tb
where stype = t.stype and t.logyear=logyear and t.logmonth=logmonth
order by scount desc)
where id in (select top 20 id from tb where t.logyear=logyear and t.logmonth=logmonth
order by scount desc)
select * from tb t
where id in (select top 20 id from tb where t.logyear=logyear and t.logmonth=logmonth
order by scount desc)效率有点慢已经统计好的次数
select *
from
(
select fromplace,toplace,scount,logyear,logmonth,
rownum=row_number() over(partition by logyear,logmonth order by scount desc)
from table
) t
where rownum<20
;with cte as
(
select fromplace,toplace,scount,logyear,logmonth,
rownum=row_number() over(partition by logyear,logmonth order by scount desc)
from tablename
)select * from cte where rownum between 1 and 20 --这里指定第几到第几
;with cte as
(
select *,rn=ROW_NUMBER() over(partition by rtrim(logyear)+'-'+rtrim(logmonth)order by scount desc)
from tb
)
select stype,scount ,rtrim(logyear)+'-'+rtrim(logmonth)
from cte
where rn<=20
--sql2000没有row_number函数 及不支持cte,用下面语句
select * from
(select *,
rownum=(select count(1) from tablename where logyear=t.logyear and logmonth=t.logmonth and scount>=t.scount)
from tablename t
) tt
where rownum between 1 and 2 --这里指定第几到第几