按时间顺序显示前六条
SELECT TOP 6 * FROM dailylog_user ORDER BY birthday asc
结果:
userid name deptid birthday memo age
4 4 11 2006-12-04 00:00:00.000 12121 11
6 6 11 2006-12-04 00:00:00.000 1212121 22
7 7 11 2006-12-05 00:00:00.000 1212121 22
2 2 11 2006-12-05 00:00:00.000 2 22
12 12 11 2006-12-05 00:00:00.000 111 111
10 10 11 2006-12-06 00:00:00.000 11 11现在想取按时间顺序显示前六条中的后三条
select top 3 * from
(SELECT TOP 6 * FROM dailylog_user ORDER BY birthday asc) b
order by birthday desc
结果
userid name deptid birthday memo age
10 10 11 2006-12-06 00:00:00.000 11 11
2 2 11 2006-12-05 00:00:00.000 2 22
7 7 11 2006-12-05 00:00:00.000 1212121 22那个userid=12的数据就被userid=2代替了????????
SELECT TOP 6 * FROM dailylog_user ORDER BY birthday asc
结果:
userid name deptid birthday memo age
4 4 11 2006-12-04 00:00:00.000 12121 11
6 6 11 2006-12-04 00:00:00.000 1212121 22
7 7 11 2006-12-05 00:00:00.000 1212121 22
2 2 11 2006-12-05 00:00:00.000 2 22
12 12 11 2006-12-05 00:00:00.000 111 111
10 10 11 2006-12-06 00:00:00.000 11 11现在想取按时间顺序显示前六条中的后三条
select top 3 * from
(SELECT TOP 6 * FROM dailylog_user ORDER BY birthday asc) b
order by birthday desc
结果
userid name deptid birthday memo age
10 10 11 2006-12-06 00:00:00.000 11 11
2 2 11 2006-12-05 00:00:00.000 2 22
7 7 11 2006-12-05 00:00:00.000 1212121 22那个userid=12的数据就被userid=2代替了????????
现在网上说到翻页(sqlserver)都是 "select top n * from (select top m * from talbe order by id desc) a order by id asc"吗?象我这种问题怎么弄啊????
具体可看:
http://sqlzoo.net/howto/source/s.pl.htm?file=tip857849&e=sqlserver2.数据量大的情况下,你这个查询会很慢。建议用游标。
比如你要取100,120条。直接select * from user order by birthday asc然后
if(rs.absolute(100))
{
// 获取第100条
rs.get("userid);
int i = 1;
while(rs.next() && i++<20)
{
rs.get("userid");
}
}大概类似这样,具体代码可以自己看着办。
我上面的写法可以兼容mysql,oracle,db2,sqlserver等等