`
041621219
  • 浏览: 17912 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

mysql 取指定条数的记录

MySQLSQLDB2Blog 
阅读更多
Db2 例子
Sql代码
create table mynumber(id int,name varchar(10))  
insert into mynumber values(1,'no1')  
insert into mynumber values(2,'no2')  
insert into mynumber values(3,'no3')  
insert into mynumber values(4,'no4')  
insert into mynumber values(5,'no5')  
insert into mynumber values(5,'no6')  
insert into mynumber values(6,'no7')  
insert into mynumber values(7,'no8')  
insert into mynumber values(8,'no9')  
insert into mynumber values(9,'no10')  
insert into mynumber values(9,'no11')  
insert into mynumber values(9,'no12')  
insert into mynumber values(10,'no13')  
insert into mynumber values(10,'no14')  
insert into mynumber values(10,'no15')  
insert into mynumber values(11,'no16')  
insert into mynumber values(12,'no17')  
insert into mynumber values(13,'no18')  
 
 
select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber) as tt where case1<=10 

create table mynumber(id int,name varchar(10))
insert into mynumber values(1,'no1')
insert into mynumber values(2,'no2')
insert into mynumber values(3,'no3')
insert into mynumber values(4,'no4')
insert into mynumber values(5,'no5')
insert into mynumber values(5,'no6')
insert into mynumber values(6,'no7')
insert into mynumber values(7,'no8')
insert into mynumber values(8,'no9')
insert into mynumber values(9,'no10')
insert into mynumber values(9,'no11')
insert into mynumber values(9,'no12')
insert into mynumber values(10,'no13')
insert into mynumber values(10,'no14')
insert into mynumber values(10,'no15')
insert into mynumber values(11,'no16')
insert into mynumber values(12,'no17')
insert into mynumber values(13,'no18')


select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber) as tt where case1<=10


case 1: 取前10条不同id记录，如果最后1条记录的ID 还有相同的，都要取出来。
Sql代码
select * from mynumber where id in(select distinct id from mynumber fetch first 10 rows only)  
 
select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber) as tt where case1<=10  
select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber) as tt where case1 between 5 and 10 

select * from mynumber where id in(select distinct id from mynumber fetch first 10 rows only)

select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber) as tt where case1<=10
select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber) as tt where case1 between 5 and 10

case 2:取前10条记录，如果第10条记录的ID 还有相同的，都要取出来。

Sql代码
select * from mynumber where id in(select id from mynumber fetch first 10 rows only)  
 
select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber) as tt where case2<=10  
select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber) as tt where case2 between 5 and 10 

select * from mynumber where id in(select id from mynumber fetch first 10 rows only)

select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber) as tt where case2<=10
select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber) as tt where case2 between 5 and 10
case 3：取前10条记录

Sql代码
select id from mynumber fetch first 10 rows only 
 
select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber) as tt where case3<=10  
select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber) as tt where case3 between 5 and 10 

select id from mynumber fetch first 10 rows only

select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber) as tt where case3<=10
select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber) as tt where case3 between 5 and 10
oralce 例子

1. 最好还是利用分析函数
Sql代码
row_number() over ( partition by col1 order by col2 ) 

row_number() over ( partition by col1 order by col2 )比如想取出100-150条记录,按照tname排序

Sql代码
select tname,tabtype from (  
select tname,tabtype,row_number() over ( order by tname ) rn from tab  
)  
where rn between 100 and 150; 

select tname,tabtype from (
select tname,tabtype,row_number() over ( order by tname ) rn from tab
)
where rn between 100 and 150;

2. 直接使用rownum 虚列
Sql代码
select tname,tabtype from (  
select tname,tabtype,rownum rn from tab where rownum <= 150  
)  
where rn >= 100; 

select tname,tabtype from (
select tname,tabtype,rownum rn from tab where rownum <= 150
)
where rn >= 100;使用序列不能基于整个记录集合进行排序,如果指定了order by子句,排序的的是选出来的记录集的排序.

Sql代码
create table mynumber(id int,name varchar(10));  
insert into mynumber values(1,'no1');  
insert into mynumber values(2,'no2');  
insert into mynumber values(3,'no3');  
insert into mynumber values(4,'no4');  
insert into mynumber values(5,'no5');  
insert into mynumber values(5,'no6');  
insert into mynumber values(6,'no7');  
insert into mynumber values(7,'no8');  
insert into mynumber values(8,'no9');  
insert into mynumber values(9,'no10');  
insert into mynumber values(9,'no11');  
insert into mynumber values(9,'no12');  
insert into mynumber values(10,'no13');  
insert into mynumber values(10,'no14');  
insert into mynumber values(10,'no15');  
insert into mynumber values(11,'no16');  
insert into mynumber values(12,'no17');  
insert into mynumber values(13,'no18');  
 
 
select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber; 

create table mynumber(id int,name varchar(10));
insert into mynumber values(1,'no1');
insert into mynumber values(2,'no2');
insert into mynumber values(3,'no3');
insert into mynumber values(4,'no4');
insert into mynumber values(5,'no5');
insert into mynumber values(5,'no6');
insert into mynumber values(6,'no7');
insert into mynumber values(7,'no8');
insert into mynumber values(8,'no9');
insert into mynumber values(9,'no10');
insert into mynumber values(9,'no11');
insert into mynumber values(9,'no12');
insert into mynumber values(10,'no13');
insert into mynumber values(10,'no14');
insert into mynumber values(10,'no15');
insert into mynumber values(11,'no16');
insert into mynumber values(12,'no17');
insert into mynumber values(13,'no18');


select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber;

case1:  取前10条不同id记录，如果最后1条记录的ID 还有相同的，都要取出来。

Sql代码
select id,name from mynumber where id in (select id from (select distinct id from mynumber) tt where rownum<=10);  
 
select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber) tt where case1<=10;  
select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber) tt where case1 between 5 and 10; 

select id,name from mynumber where id in (select id from (select distinct id from mynumber) tt where rownum<=10);

select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber) tt where case1<=10;
select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber) tt where case1 between 5 and 10;

case 2:取前10条记录，如果第10条记录的ID 还有相同的，都要取出来。

Sql代码
select * from mynumber where id in(select id from mynumber where rownum <=10);  
 
select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber) tt where case2<=10;  
select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber) tt where case2 between 5 and 10; 

select * from mynumber where id in(select id from mynumber where rownum <=10);

select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber) tt where case2<=10;
select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber) tt where case2 between 5 and 10;


case 3：取前10条记录

Sql代码
select id,name from mynumber where rownum <=10;  
select id,name from (select id,name,rownum rn from mynumber where rownum <= 10 ) where rn >= 5;  
 
select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber) tt where case3<=10;  
select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber) tt where case3 between 5 and 10; 

select id,name from mynumber where rownum <=10;
select id,name from (select id,name,rownum rn from mynumber where rownum <= 10 ) where rn >= 5;

select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber) tt where case3<=10;
select * from (select id,name,RANK() over ( order by id ) case1,DENSE_RANK() over ( order by id ) case2,row_number() over ( order by id ) case3 from mynumber) tt where case3 between 5 and 10;

Mysql 例子

Sql代码
select id from mytable order by update_date desc limit 0,10 

select id from mytable order by update_date desc limit 0,10
limit用法：

              select * from tablename limit [offset],(rows)
              注释：offset为偏移量
                        rows为要取出的记录条数
内容来自：http://nanshannan0106.iteye.com/blog/213252
分享到：
| mysql 客户端 乱码
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    mysql分组取每组前几条记录(排名) 附group by与order by的研究

    –按某一字段分组取最大(小)值所在行的数据 代码如下: /* 数据如下： nameval memo a 2 a2(a的第二个值) a 1 a1–a的第一个值 a 3 a3:a的第三个值 b 1 b1–b的第一个值 b 3 b3:b的第三个值 b 2 b2b2b2b2 b 4 b4b4 b ...

    MySql实现分组取n条最大记录.txt

    本篇文章详细介绍了如何使用MySQL实现分组取n条最大记录的功能。通过精心设计的SQL查询语句，我们可以有效地解决这类问题。这种方法不仅适用于上述具体场景，还可以扩展应用于其他类似的需求，例如按不同标准分组并...

    Mysql取分组后的每组第一条数据

    在MySQL中，当我们需要从分组后的数据中获取每组的第一条或特定顺序的数据时，标准的`GROUP BY`和`ORDER BY`语句并不能直接实现这个需求。这是因为`GROUP BY`语句会先对数据进行分组，然后在每个组内应用`ORDER BY`...

    易语言MYSQL取表名

    对于“MYSQL取表名”这个需求，我们需要执行特定的SQL语句来获取数据库中的表名。 常用的SQL语句是`SHOW TABLES;`，这条命令会返回指定数据库中的所有表名。在易语言中，我们可以这样做： 1. 导入数据库支持库：...

    查询前几条记录

    这里的`N`就是我们想要的记录数。如果你还需要指定从哪一条开始取，可以配合`OFFSET`关键字： ```sql SELECT * FROM 表名 LIMIT M, N; ``` 这里的`M`是起始位置（从0开始），`N`是取的数量。例如，如果你想从第11...

    mysql数据库原理演示记录

    在数据的增删改查操作方面，添加数据可以通过INSERT命令实现，为表的所有字段或指定字段添加数据，也可以一次性添加多条记录。更新数据使用UPDATE命令，删除数据则使用DELETE命令。 单表查询部分涉及了简单查询、...

    从数据库中读取指定行数记录

    这里 `OFFSET 3` 表示跳过前三条记录，`LIMIT 5` 表示从第四条记录开始取五条记录。 **优点**：简洁明了，易于理解和维护。 **缺点**：部分数据库系统不支持 `OFFSET`。 ##### 4. 使用自连接方法 这种方法通过自...

    mysql获取group by总记录行数的方法

    这样，第一个查询将返回分组后的数据，第二个查询则给出所有数据的总数，包括分组前的原始记录数。 在实际应用中，如果你的查询涉及到多表连接和`GROUP BY`操作，可以像下面这样使用： ```sql SELECT SQL_CALC_...

    三种数据库不同的取前10条记录

    本文将详细介绍如何在Oracle、MySQL以及通用SQL中获取表中的前10条记录。 ### 一、Oracle数据库中获取前10条记录 #### 1. 使用ROWNUM 在Oracle数据库中，可以通过`ROWNUM`伪列来获取特定数量的记录。`ROWNUM`伪列...

    mysql的插入问题 怎么获得自动增长的ID

    在插入17条记录后，删除了ID为15、16和17的记录，然后重启MySQL服务，再插入一条新的记录。 - **MyISAM**：由于MyISAM使用独立的文件存储自动增长的ID值，即使在删除了一些记录并重启MySQL服务后，新的记录ID也会...

    MYSQL获取行号row_no

    综上所述，MySQL中获取行号的方式有多种，具体选择哪种取决于实际需求和数据特性。对于小规模数据集，推荐使用第一种方法；对于需要按特定顺序排列的大型数据集，则建议采用第二种方法；而对于只需要筛选部分记录的...

    php随机取mysql记录方法小结

    // 当记录数不足时，从后向前取 } $query_random = "SELECT * FROM recommends LIMIT $se_pos, $length"; $result_random = mysql_query($query_random); ``` 这种方法的优点是可以避免在大量数据时使用`ORDER BY...

    sql基本语句30条

    **解释**：这些语句分别用于在Access、SQL Server和MySQL中随机选取前n条记录。 ### 8. 查询超过五分钟未完成的任务 **语法示例**： ```sql SELECT * FROM &lt;table_name&gt; WHERE DATEDIFF(MINUTE, start_time, ...

    MySQL查询倒数第二条记录实现方法

    当我们要获取某特定条件下的倒数第二条记录时，可以先按照指定的字段（如`id`）进行降序排序，然后通过`LIMIT`跳过第一条记录，只取下一条。例如，在给定的代码片段中： ```sql SELECT * FROM holder_change_...

    易语言MYSQL分页查询

    这通常涉及“取得记录数”、“取记录字段”等命令，用于获取每条记录的字段值。 显示指定范围的记录，则需要根据用户的页码和每页记录数动态调整`LIMIT`和`OFFSET`的值。例如，如果用户当前在第n页，那么OFFSET应...

    mysql数据库my.cnf配置文件

    适用于在一次性插入100-1000+条记录时， 提高效率。默认值是8M。可以针对数据量的大小，翻倍增加。 myisam_sort_buffer_size = 1024M # MyISAM设置恢复表之时使用的缓冲区的尺寸，当在REPAIR TABLE或用CREATE INDEX...

    mysql分页实例

    `LIMIT`用于指定每页显示的记录数，而`OFFSET`则用于跳过前面的记录，达到定位到某一页的目的。例如，如果我们想获取第2页，每页10条记录的数据，可以编写如下SQL： ```sql SELECT * FROM table_name LIMIT 10 ...

    易语言操作MYSQL源码

    取记录数() .循环 开始 .记录数 .学生姓名 = .记录集.读字段(0) 输出("学生姓名：" + .学生姓名) .循环 结束 ``` 3. **删除数据**：使用“执行SQL语句”命令删除指定记录。例如，删除ID为1的学生： ```易语言 ....

    MySQLpager

    `LIMIT`用于指定每页显示的记录数量，而`OFFSET`则用于跳过前N条记录，N通常是页码乘以每页的记录数。 例如，如果我们每页显示10条记录，要获取第2页的数据，SQL查询可能会写成这样： ```sql SELECT * FROM table_...

    易语言MYSQL取表名源码-易语言

    在处理记录集时，易语言提供了一些方便的命令，如“取记录数”（获取记录总数）、“取字段数”（获取字段数量）、“取字段名”（获取指定位置的字段名）以及“取字段值”（获取指定记录和字段的值）。你可以结合这些...

Magicbox
Global site tag (gtag.js) - Google Analytics