从选择顶部获取数据 [英] Get data from Select top

问题描述

我已经对上一个问题回答了一个问题，使用SQL Server选择顶部将返回与选择*

I already had a question answered on my previous problem Select top using SQL Server returns different output than select *

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

I want to get select top n data from a database based on alphabetical & numbering format. The output must order by alphabet first and number after that.

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

When I try to get all data (select *), I get the correct output:

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名)时，我也得到了正确的数据:

when I try to select top n (ex : top 5), I got the right data too :

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

问题是，当我尝试根据上一个nocust&分享前5个数据 (concat(share, nocust) < 'ZZZZq797')) 它返回错误的预期数据:

The problem is when i try to get next top 5 based on last nocust & share on previous top 5 data (concat(share, nocust) < 'ZZZZq797')) it return wrong expected data :

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名.

I expect the mistake is somewhere between the concat and order by, can someone tell me how to get the right top 5.

推荐答案

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

I'm not sure if there's a built-in function to get row ranges, but you can always use 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子句中指定的行范围.

This will assign "row numbers" to each row based on your ORDER BY and then return only the range of rows you specify in the WHERE clause.

这篇关于从选择顶部获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！