使用SQL Server的select top返回与select *不同的输出 [英] Select top using SQL Server returns different output than select *

问题描述

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

I tried 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 | 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 * from table):

But when I try to select top n (ex : top 5), I get different output than expected (not like select * from table) :

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 | share
-------+--------
k007   | BBCA
b454   | BBCA
a522   | BBCA
p430   | BBCA
q797   | 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 output like :

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

推荐答案

我试图以不同的角度回答这个问题.

I am trying to answer this in different perspective.

首先应该清楚Optimizer make the best possible plan quickly.

Optimizer select index or do not select index in most cost effective manner.

我正在使用Adventure 2016 database，而Production.Product具有504行.

I am using Adventure 2016 database and Production.Product has 504 rows.

select [Name],ProductNumber from Production.Product
order by [Name]

它按预期对行进行排序.

It sort the rows as expected.

select top 5 [Name],ProductNumber from Production.Product
order by [Name]

它按预期对行进行排序.

It sort the rows as expected.

如果我在Order中使用case语句

If I use case statement in Order

select [Name],ProductNumber from Production.Product
order by case when [name] like '[a]%' then 1 else -1 end

它按预期对记录进行排序.所有504行都是进程.

It sort the record as intended. All 504 rows are process.

如果我在

select Top 5 [Name],ProductNumber from Production.Product
order by case when [name] like '[a]%' then 1 else -1 end



Then it pick first n records and display n record quickly.
Sorting was not as expected.

如果我在顶部使用更多20% of total rows之类的

If I use more 20% of total rows in Top like

select Top (101) [Name],ProductNumber from Production.Product
order by case when [name] like '[a]%' then 1 else -1 end

它将处理所有504 rows并进行相应排序.

It will process all 504 rows and sort accordingly.

排序结果符合预期.

在以上所有情况下，Clustered Index Scan (Product id)均已完成. 在此示例中，[Name]and ProductNumber是两个不同的non clustered index.

In all above case Clustered Index Scan (Product id) is done. In this example [Name]and ProductNumber are two different non clustered index.

但未选中.

您可以这样做

;With CTE as(

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


)

select top 5 * from CTE
order by SortCol

这篇关于使用SQL Server的select top返回与select *不同的输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！