时间戳

首页 > 解决方案 > 从选择顶部获取数据

sql-server - 从选择顶部获取数据

问题描述

我之前的问题已经回答了一个问题Select top using SQL Server 返回与 select * 不同的输出

我想select top n从基于字母和编号格式的数据库中获取数据。输出必须先按字母排序，然后按数字排序。

当我尝试获取所有数据 ( select *) 时，我得到了正确的输出：

select nocust, share 
from TB_STOCK
where share = ’BBCA’ 
  and concat(share, nocust) < ‘ZZZZZZZZ’
order by 
    case when nocust like ‘[a-z]%’ then 0 else 1 end, nocust


nocust | share
-------+--------
a522   | BBCA
b454   | BBCA
k007   | BBCA
p430   | BBCA
q797   | BBCA
s441   | BBCA
s892   | BBCA
u648   | BBCA
v107   | BBCA
4211   | BBCA
6469   | BBCA
6751   | BBCA

当我尝试select top n（例如：前 5 名）时，我也得到了正确的数据：

select top 5 nocust, share 
from TB_STOCK
where share = ’BBCA’ 
  and concat(share, nocust) < ‘ZZZZZZZZ’
order by 
    case when nocust like ‘[a-z]%’ then 0 else 1 end, nocust

nocust | share
-------+--------
a522   | BBCA
b454   | BBCA
k007   | BBCA
p430   | BBCA
q797   | BBCA

问题是，当我尝试根据最后一次攻击获得下一个前 5 名并分享之前的前 5 名数据 (concat(share, nocust) < 'ZZZZq797')) 时，它返回错误的预期数据：

select top 5 nocust, share 
from TB_STOCK
where share = ’BBCA’ 
and concat(share, nocust) < ‘ZZZZq797’
order by 
case when nocust like ‘[a-z]%’ then 0 else 1 end, nocust

nocust | share
-------+--------
a522   | BBCA
b454   | BBCA
k007   | BBCA
p430   | BBCA
q797   | BBCA

它应该返回：

nocust | share
-------+--------
s441   | BBCA
s892   | BBCA
u648   | BBCA
v107   | BBCA
4211   | BBCA

我希望错误在 concat 和 order by 之间，有人可以告诉我如何获得正确的前 5 名。

标签： sql-serversql-server-2008selectsql-order-by

解决方案

我不确定是否有内置函数来获取行范围，但您始终可以使用ROW_NUMBER： 

select nocust, share
FROM (
  select nocust, share, 
    ROW_NUMBER() OVER(
      ORDER BY case when nocust like ‘[a-z]%’ then 0 else 1 end, nocust
    ) AS RowNum -- Assign rows "row numbers" based on `ORDER BY`
  from TB_STOCK
  where share = ’BBCA’ 
    and concat(share, nocust) < ‘ZZZZZZZZ’
) src
WHERE RowNum BETWEEN <start_row_num> AND <end_row_num> -- Get specified row range
order by 
  case when nocust like ‘[a-z]%’ then 0 else 1 end, nocust -- Not sure if this is needed

这将根据您为每一行分配“行号” ORDER BY，然后仅返回您在WHERE子句中指定的行范围。

推荐阅读