求解惑：max函数问题

Question

本帖最后由 athwind 于 2013-10-13 17:18:10 编辑

Answer 1

select top 2 id
from
(
select *,rn=row_number() over(order by id desc) from tb
)t
where rn<=2

Answer 2

我用的是sql server 2008r2，做了一个实验，就是正常的，没有你说的这种问题：
create table tt(id int not null identity(1,1))
insert into tt
default valuesinsert into tt
default valuesinsert into tt
default valuesinsert into tt
default valuesinsert into tt
default valuesinsert into tt
default valuesinsert into tt
default values
select * from ttdelete 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
*/

Answer 3

？这个可以 学习了 
都有病吗？我是问这种现象的原因的，是问why，不是how

Answer 4

我用的是sql server 2008r2，做了一个实验，就是正常的，没有你说的这种问题：
create table tt(id int not null identity(1,1))
insert into tt
default valuesinsert into tt
default valuesinsert into tt
default valuesinsert into tt
default valuesinsert into tt
default valuesinsert into tt
default valuesinsert into tt
default values
select * from ttdelete 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
*/

这就是我纳闷的原因啊，按照sql语法，完全应该没问题的啊

Answer 5

？这个可以 学习了 
都有病吗？我是问这种现象的原因的，是问why，不是howWhy？逻辑上如果先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)

Answer 6

因为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 oninsert into tb1(ID)
 select 3 union all
 select 6 union all
 select 7set 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 oninsert into tb2(ID)
 select 3 union all
 select 6 union all
 select 7set identity_insert tb2 off
select max(ID) 'maxID'
 from (select top 2 ID from tb2) t/*
maxID
-----------
6(1 row(s) affected)
*/

Answer 7

哦，对对对，是索引的问题，不是Aggregate提前的问题，force order不用测了：Top 1 时是无序索引扫描 + Top + Aggregate
Top 2 时是倒序索引扫描 + Top + Aggregate

Answer 8

ID字段是主键，而且是默认的聚集索引，那为什么其他人在sql server r2和sql server 2012版本下测试结果正常呢？

Answer 9

直接执行没问题，目测是你的排序规则问题，你的数据库排序规则是什么？
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
*/

Answer 10

ID字段是主键，而且是默认的聚集索引，那为什么其他人在sql server r2和sql server 2012版本下测试结果正常呢？
难道是传说中的bug？不会吧

Answer 11

你们都用sql server2014了啊数据库设置都是默认，这是主键PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]GO

Answer 12

SELECT collation_name
FROM sys.databases 
WHERE name='库名'执行一下这句

Answer 13

--試試以下語句，看能不能解答你的困惑：
select max(ID) from (select top 2 ID from tb order by ID) t

Answer 14

简单的说就是你没加Order by的排序让SQL自由选择了一个执行计划导致结果非你所愿在MSSQL的官方说明里，没加排序的SELECT输出顺序是不固定的，虽然经过实践有一些规则，但官方从来没保证过这些规律必然一直如此，比如你的语句里加一个min()就会又变回2，1这样的结果所以你问这个问题就像问为啥rand每次出来的数都不一样似的，人家本来就没说过会出来一样的，哪怕你两次一样的语句出来两个结果也是有可能的。

Answer 15

用了 TOP 不用 ORDER BY ，出来奇怪的结果一点也不奇怪。

Answer 16

这和rand也能类比？我只能说你太能扯了