【急】求sql语句,要求见内容。
有这样一个表
对字段‘statetime’进行分组查询,如图
得到如下结果
但是我最终想得到的结果是在分组后每组取出前两条数据,并且实现翻页,用sql语句。数据库是sql2000的。
ps:只有80分了,你懂的,大神...
[解决办法]
这个改一下:
select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
newspage,userid,StateTime,
rownum
from
(
select t1.*,
(
select count(*)
from
(
select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
newspage,userid,StateTime,
convert(varchar(7),StateTime,120) as mon
from tb_News
where (NewsType = '时光树') and (userid = 1)
group by StateTime,NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,
NewsAudi,newstc,Newssource,updateuser,Newsimg,Statetype,psort,
newspartinfo,newspage,userid
) t2
where t2.mon = t1.mon
)-
(
select
count(*)
from
(
select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
newspage,userid,StateTime,
convert(varchar(7),StateTime,120) as mon
from tb_News
where (NewsType = '时光树') and (userid = 1)
group by StateTime,NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,
NewsAudi,newstc,Newssource,updateuser,Newsimg,Statetype,psort,
newspartinfo,newspage,userid
) t2
where t2.mon = t1.mon
and t2.NewsId <= t1.NewsId
)+1 as rownum
from
(
select NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,NewsAudi,
newstc,Newssource,updateuser,Newsimg,Statetype,psort,newspartinfo,
newspage,userid,StateTime,convert(varchar(7),StateTime,120) as mon
from tb_News
where (NewsType = '时光树') and (userid = 1)
group by StateTime,NewsId,NewsTitle,NewsContent,NewsTime,NewsClick,NewsType,
NewsAudi,newstc,Newssource,updateuser,Newsimg,Statetype,psort,
newspartinfo,newspage,userid
)t1
)t
where rownum <= 2
order by mon desc,StateTime desc,rownum