sqlserver选择行号、随机查询

我不是女神ヾ 2021-07-04 14:12 486阅读 0赞

1、选择行号

select row_number() over (order by colName) as rowNum,* from tableName

2、随机排序

select *,newid() randomId from tableName order by randomId

3、随机选取一行

1）方式一，以 选择行号 为基础，过滤条件是 rowNum 等于一个随机的整数。

select * from
    (
    select row_number() over (order by colName) as rowNum,* from tableName
    ) t where t.rowNum = celling(rand()*num)   -- num是该表的记录个数

2）方式二，为每条记录生成一个随机的`id`，根据随机id排序，最后选择第一条记录`top 1`。

select top 1 * from
    (
    select *,newid() randomId from tableName order by randomId 
    ) t

--------------------

## sql:除非另外还指定了 `TOP` 或 `FOR XML`，否则，`ORDER BY` 子句在视图、内联函数、派生表、子查询 ##

执行sql语句:

select * from (
    
    select * from tab where ID>20 order by userID desc
    
    ) as a order by date desc

逻辑上看着挺对 但是报错:

> **除非另外还指定了 TOP 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。**

只要我们在嵌套子查询视图里面加入: `top 100 percent` 即可

select * from (
    
    select top 100 percent * from tab where ID>20 order by userID desc
    
    ) as a order by date desc

默认情况下，如果在子查询，函数，视图中尝试去使用`ORDER BY`，

CREATE VIEW dbo.VSortedOrders
    AS
    
    SELECT orderid, customerid
    FROM dbo.Orders
    ORDER BY orderid
    GO

那么可能会遇到下面的错误

> 消息 1033，级别 15，状态 1，第 4 行
> 
> 除非另外还指定了 TOP 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。

**原因就是针对一个表的`SELECT`其实并不是返回一个表，而是一个游标。**

如果一定要用怎么办呢？答案就是配合`TOP 100 PERCENT`

SELECT     TOP (100) PERCENT orderid, customerid
    FROM         dbo.Orders
    ORDER BY orderid, customerid DESC

## 示例 ##

select top 1   * from
    (
    select top 100 PERCENT *,newid() randomId from Products order by randomId 
    ) t
    
    select  top 1 *,newid() randomId from Products order by randomId 
    select  top 1 *  from Products where status=1 ORDER BY NEWID()
    
    select * from
    (
    select row_number() over (order by id) as rowNum,* from Products
    ) t where t.rowNum = ceiling(rand()*10)   -- num是该表的记录个数
    
    
    select ceiling( rand()*10) 
    
    select rand()*1000