求解惑：max函数有关问题

2013-10-15

求解惑：max函数问题本帖最后由 athwind 于 2013-10-13 17:18:10 编辑有一个表tb，主键为ID，自增长int类型，3

求解惑：max函数问题
本帖最后由 athwind 于 2013-10-13 17:18:10 编辑有一个表tb，主键为ID，自增长int类型，3条记录
ID
3
6
7
为了获取前2条记录的最大ID，于是很easy的写出了以下语句：
select max(ID) from (select top 2 ID from tb) t
------结果
7
很困惑，应该为6才对啊，因为select top 2 ID from tb的返回
3
6
测试发现select max(ID) from (select top N ID from tb) t始终返回当前表的最大值
只有在N=1时，即：
select max(ID) from (select top 1 ID from tb) t
或显式的加了order by之后才符合预期，即：
select max(ID) from (select top N ID from tb order by ID) t
求解惑
ps：数据库为Sql Server 2008
[解决办法]

引用:

有一个表tb，主键为ID，自增长int类型，3条记录
ID
3
6
7
为了获取前2条记录的最大ID，于是很easy的写出了以下语句：
select max(ID) from (select top 2 ID from tb) t
------结果
7
很困惑，应该为6才对啊，因为select top 2 ID from tb的返回
3
6
测试发现select max(ID) from (select top N ID from tb) t始终返回当前表的最大值
只有在N=1时，即：
select max(ID) from (select top 1 ID from tb) t
或显式的加了order by之后才符合预期，即：
select max(ID) from (select top N ID from tb order by ID) t
求解惑
ps：数据库为Sql Server 2008

我用的是sql server 2008r2，做了一个实验，就是正常的，没有你说的这种问题：

create table tt(id int not null identity(1,1))


insert into tt
default values

insert into tt
default values

insert into tt
default values

insert into tt
default values

insert into tt
default values

insert into tt
default values

insert into tt
default values




select * from tt

delete from tt
where id not in (3,6,7)

select top 2 ID from tt
/*
ID
3
6
*/

select max(ID) 
from (
select top 2 ID from tt
) t
/*
(无列名)
6
*/

[解决办法]

引用:

Quote: 引用:
Quote: 引用:
Quote: 引用:
select top 2 id
from
(
select *,rn=row_number() over(order by id desc) from tb
)t
where rn<=2
？
这个可以学习了
都有病吗？我是问这种现象的原因的，是问why，不是how

Why？逻辑上如果先Top再Aggregate可以得到期望的结果，不过优化器可能会自作聪明地将Aggregate提前，先Aggregate再Top(如果优化器足够聪明甚至可能会省略Top)，导致非预期结果。

当子查询里面加order by时，优化器始终会先使用Top N Sort（无索引提供顺序）或执行有序索引扫描 + Top，然后应用Aggregate，所以结果是正确的。

观察 Top 1 和 Top N 执行计划的差异就会明白了。不同版本优化器的行为可能不同，yupeigu在R2的测试正确不代表所有情况下都正确（我在2012试了一下也正确），如果优化器认为有必要将Aggregate提前仍然会提前，所以不应该给优化器自作聪明发挥的余地，子查询里面的order by不可少。

另外一种how方法，option(force order)可能有用，没有2008环境，你可以测试一下，如果方便公布一下结果，那就更好了：

select max(ID) from (select top 2 ID from tb) t option(force order)
[解决办法]
因为ID列是主键(默认也是聚集索引),top 2时会按聚集索引排序,
当ID列不是主键时,返回6


-- 测试1, tb1.ID是主键
create table tb1
(ID int identity(1,1) not null
 constraint pk_tb1 primary key(ID)
)

set identity_insert tb1 on

insert into tb1(ID)
 select 3 union all
 select 6 union all
 select 7

set identity_insert tb1 off


select max(ID) 'maxID'
 from (select top 2 ID from tb1) t

/*
maxID
-----------
7

(1 row(s) affected)
*/



-- 测试2, tb2.ID不是主键
create table tb2
(ID int identity(1,1) not null
)

set identity_insert tb2 on

insert into tb2(ID)
 select 3 union all
 select 6 union all
 select 7

set identity_insert tb2 off


select max(ID) 'maxID'
 from (select top 2 ID from tb2) t

/*
maxID
----------- 
 
6

(1 row(s) affected)
*/

[解决办法]
直接执行没问题，目测是你的排序规则问题，你的数据库排序规则是什么？
SELECT collation_name
FROM sys.databases
WHERE name='库名'

----------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-10-14 08:13:51
-- Version:
--      Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64) 
--Jun 10 2013 20:09:10 
--Copyright (c) Microsoft Corporation
--Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([ID] int)
insert [tb]
select 3 union all
select 6 union all
select 7
--------------开始查询--------------------------

select max(ID) from (select top 2 ID from tb) t
----------------结果----------------------------
/* 
-----------
6
*/

热点排行