在SQL-Server中,取出第n条到第m条记录简单分析

本文总结了在 SQL Server 中通过两种方法获取指定范围(第 n 到第 m 条)记录的技巧,包括 Not In 版本和 Exists 版本,并解释了各自的工作原理及注意事项。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

在SQL-Server中,取出第n条到第m条记录可以这样写 

例如: 
从publish   表中取出第   n   条到第   m   条的记录: 
SELECT   TOP   m-n+1   * 
FROM   publish 
WHERE   (id   NOT   IN 
                    (SELECT   TOP   n-1   id 
                  FROM   publish order by id))  order by id

id   为publish   表的关键字 

以下几种是我在别人的博客中看到的前两种看了下,做下总结。

  (1) --从Table 表中取出第 m 条到第 n 条的记录:(Not In 版本)

  
  
SELECT   TOP  n - m + 1   *  
  
FROM   Table  
  
WHERE  (id  NOT   IN  ( SELECT   TOP  m - 1  id  FROM   Table  ))  

  这种方法和我上边写的一样,主要的思想就是,先选出表中所有的项,m-n+1是表中有几条,然后top n-m+1 就可以找出需要的条数。关键中中间的

  (SELECT TOP m-1 id FROM Table ) 这句话也是先选出表中所有项,再选出 top m-1就是,表的前m-1条了。最加上 WHERE (id NOT IN (SELECT TOP m-1 id FROM Table ))最后讲所有选出的记录去掉top m-1条,在取出top n-m+1 就是答案了。

  (2) -- 从TABLE表中取出第m到n条记录 (Exists版本)
  
  
SELECT   TOP  n - m + 1   *   FROM   TABLE   AS  a  WHERE   Not   Exists
  (
Select   *   From  ( Select   Top  m - 1   *   From   TABLE   order   by  id) b  Where  b.id = a.id ) 

  Order by id

  这种方法和第一种方法思想差不多,都是先选出所有的数据,排除前top m-1个数据,最后取出top n-m+1 。这里需要注意的是如果你要按照某种顺序排序的话,则就需要在如上得两处order by id。还有这里的Where b.id=a.id 去掉就不行了,不知道为什么?

 

'@P0'附近有语法错误

原因:

在Java中对数据库查询时经常使用“Select Top ? * From 表名 Where 列名 = ?”的SQL语句,此时的问号是PreparedStatement预编译对象的参数占位符,需要使用setXX()系列方法对其赋值后再执行。

但是,Top后面是不允许使用问号占位符的,此处的错误就是由此引起的。

解决方案:

应将Top后面的问号改为具体数字,或者使用“+”连接变量与字符串。

上句SQL可修改为以下写法:

"Select Top " + num + " * From 表名 Where 列名 = ?"

上述num为int型变量。除了Top后的问号以外,其他参数占位符不受此约束。

以上~~

 

<think>我们正在讨论从数据库中随机抽取多记录的方法。由于不同数据库系统可能有不同的实现方式,我将介绍几种常见数据库的通用方法,并解释其原理。常见方法:1.使用ORDERBYRAND():简单但效率较低,适用于小数据量。2.使用随机函数和LIMIT:效率较高,适用于大数据量,但需要根据数据库类型调整。以MySQL、PostgreSQLSQLite为例:###MySQL方法1(效率较低,小表):```sqlSELECT*FROMtable_nameORDERBYRAND()LIMITN;```方法2(效率较高,大表):思路:先获取记录总数M,然后随机选择N个起始位置(需要保证不重复),然后取这些位置的记录。但更常用的高效方法是:```sql--假设表有自增主键id,且id连续(无间隙)SELECT*FROMtable_nameWHEREid>=(SELECTFLOOR(RAND()*(SELECTMAX(id)FROMtable_name)))LIMIT1;```但是这样一次只能取一,且要求id连续。对于多,可以采用以下方法(适用于大表):```sql--方法:先随机生成N个id(在min(id)和max(id)之间),然后根据这N个id去查询。但这样可能因为id不存在或重复而导致记录数不足。--更通用的高效方法是使用JOINSELECTt.*FROMtable_nameAStJOIN(SELECTidFROMtable_nameORDERBYRAND()LIMITN)AStmpONt.id=tmp.id;```但是,如果表非常大,上述方法2中的子查询`ORDERBYRAND()LIMITN`仍然会扫描全表,效率不高。因此,另一种思路是:```sql--预先知道总行数M,然后随机选择N个位置,然后取出这些行(需要依赖程序计算随机位置)--在程序中计算随机偏移量(多个,需不重复)SELECT*FROMtable_nameLIMIT{random_offset},1;```但是这样要执行N次查询,不太好。改进:一次查询获得多个随机行(不依赖连续id):```sql--使用UNION合并多个随机结果(每记录随机取一次,但这样可能重复,所以需要去重)--不推荐--另一种:计算总行数M,然后随机生成N个不同的在0到M-1之间的整数,然后使用OFFSET(效率低,因为OFFSET会扫描跳过前面的行)```对于MySQL,如果要求高效,最好使用如下方法(需要知道总行数):1.获取总行数(M):SELECTCOUNT(*)FROMtable_name;2.生成N个不同的随机数(0到M-1之间)排序(假设总行数很大,N很小)3.使用:SELECT*FROMtable_nameWHEREidIN(...);但是非连续id的情况下无法直接通过行号定位。所以,更实际的高效方法是:如果表有自增主键且没有太多间隙,可以先获取最小和最大id,然后生成随机id值(在最小和最大之间),然后查询id>=随机数的行,然后取第一(取多可以多次执行,或者使用UNIONALL,但要考虑重复的问题)。或者,在应用层存储所有主键值,然后随机选择N个主键值,然后查询。如果表没有主键,或者主键不连续,则可以考虑:```sql--为每一行生成一个随机数(使用变量),然后选择随机数最小的N行(效率尚可,但需要全表扫描,比ORDERBYRAND()要好,因为它不需要排序)SELECT*FROM(SELECT*,@rand:=RAND()asrand_valFROMtable_name)AStORDERBYrand_valLIMITN;```或者(在MySQL8.0中可以使用窗口函数):```sqlWITHtempAS(SELECT*,ROW_NUMBER()OVER(ORDERBYRAND())ASrnFROMtable_name)SELECT*FROMtempWHERErn<=N;```这仍然需要全表扫描和排序,但比`ORDERBYRAND()`直接排序全表可能稍好,因为使用了窗口函数。所以,如果表非常大,最好的方法可能是:-使用TABLESAMPLE(但MySQL不支持,而PostgreSQL支持)-或者,使用随机件(例如随机取1%的行,然后从中取N,如果表非常大,这样效率会高),例如:```sqlSELECT*FROMtable_nameWHERERAND()<0.01ORDERBYRAND()LIMITN;```需要调整0.01的值以确保返回的行数大于等于N(否则可能需要重试),但这样可能不够随机。综上,对于MySQL简单易行的方法是`ORDERBYRAND()LIMITN`(小表),或者使用应用层缓存主键再随机(大表)。###PostgreSQL1.直接使用ORDERBYrandom():```sqlSELECT*FROMtable_nameORDERBYrandom()LIMITN;```效率:同样需要全表扫描和排序,因此大表不高效。2.使用TABLESAMPLE子句(PostgreSQL9.5+):```sqlSELECT*FROMtable_nameTABLESAMPLESYSTEM(0.1)LIMITN;```SYSTEM采样方式基于数据块,所以可能不够随机(特别是当表有聚集存储时),但速度非常快。注意:这里0.1表示采样10%的数据块,然后从中取N。如果采样返回的行数少于N,可以多次执行直到达到N(或者使用REPEATABLE种子)。如果需要更均匀的随机采样,可以使用BERNOULLI采样(扫描全表但随机选取行):```sqlSELECT*FROMtable_nameTABLESAMPLEBERNOULLI(0.1)LIMITN;```其中0.1表示采样0.1%的行(根据表大小调整百分比)。3.如果知道表的主键,且主键连续,也可以使用随机偏移量(但存在间隙则不行):```sql--不推荐,因为间隙问题```###SQLiteSQLite通常用于小到中型数据库,所以使用`ORDERBYRANDOM()`是可行的:```sqlSELECT*FROMtable_nameORDERBYRANDOM()LIMITN;```总结:1.小表:直接使用`ORDERBYrandom()/RAND()/RANDOM()`+`LIMIT`。2.大表(MySQL):考虑使用TABLESAMPLE(如果支持,但MySQL不支持)或者使用应用层缓存主键,然后随机选择主键值再查询。或者使用生成随机行号的方法(要求有自增主键且连续,否则需额外处理间隙)。3.大表(PostgreSQL):优先使用`TABLESAMPLE`。注意:随机抽取多记录的方法需要根据实际数据库类型、表大小、表结构(是否有主键、主键是否连续)以及性能要求来选择。§§相关问题§§1.如何在MySQL中高效地随机选择记录?2.在PostgreSQL中,如何用TABLESAMPLE实现随机抽样?3.如果数据库表没有主键,如何随机抽取记录?4.随机抽取记录时如何避免重复?</think> 以下是不同数据库中随机抽取多记录的常用方法及实现原理,适用于MySQL、PostgreSQLSQL ServerSQLite等主流数据库--- ### 一、通用方法(推荐) #### 1. **`ORDER BY RAND()` + `LIMIT`** **原理**: 为每记录生成随机数后排序,取前N ```sql -- MySQL/SQLite SELECT * FROM 表名 ORDER BY RAND() LIMIT 5; -- PostgreSQL SELECT * FROM 表名 ORDER BY RANDOM() LIMIT 5; -- SQL Server SELECT TOP 5 * FROM 表名 ORDER BY NEWID(); ``` **适用场景**: - 数据量 < 10万(效率较高) - 需要精确随机分布的场景 --- #### 2. **偏移量法(高效替代方案)** **原理**: 先获取总行数,再计算随机偏移位置 ```sql -- MySQL(需预先获取总行数) SELECT COUNT(*) INTO @total FROM 表名; SET @offset = FLOOR(RAND() * @total); SELECT * FROM 表名 LIMIT @offset, 5; -- PostgreSQL WITH total AS (SELECT COUNT(*) AS ct FROM 表名) SELECT * FROM 表名 OFFSET FLOOR(RANDOM() * (SELECT ct FROM total)) LIMIT 5; ``` **优势**: - 避免全表排序,时间复杂度 O(1) - 适合大数据量(百万级) --- ### 二、分场景优化方案 #### 场景1:带件的随机抽样 ```sql SELECT * FROM 用户表 WHERE 城市 = '北京' ORDER BY RAND() LIMIT 10; ``` #### 场景2:避免重复抽取 使用临时表存储已选ID: ```sql CREATE TEMPORARY TABLE selected_ids (id INT); INSERT INTO selected_ids SELECT id FROM 商品表 ORDER BY RAND() LIMIT 3; SELECT * FROM 商品表 WHERE id IN (SELECT id FROM selected_ids); ``` --- ### 三、性能对比 | 方法 | 10万行耗时 | 100万行耗时 | 优点 | |---------------------|-------------|-------------|--------------------------| | `ORDER BY RAND()` | 0.5s | 8s+ | 实现简单 | | 偏移量法 | 0.1s | 0.3s | 避免全表扫描 | | 分桶抽样 (Hive) | 0.05s | 0.2s | 超大数据专用 | > ⚠️ **注意**:`ORDER BY RAND()` 在大表上可能导致全表扫描,建议配合 `WHERE` 件限制范围[^1]。 --- ### 四、特殊数据库方案 #### **Oracle** ```sql SELECT * FROM ( SELECT * FROM 表名 ORDER BY DBMS_RANDOM.VALUE ) WHERE ROWNUM <= 5; ``` #### **Hive/Spark** ```sql SELECT * FROM 表名 WHERE rand() <= 0.001 -- 抽样比例 DISTRIBUTE BY RAND() SORT BY RAND() LIMIT 100; ``` ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值