select top (5*1) Id from table where SourceId=22292id 108 107 106 95 93 然后select min(tt.id) from (select top (5*1) Id from table where SourceId=22292) ttid 6 请问这是为什么 我的id的索引时desc的 我这样写在另外的一个表可以,是不是我这张表有什么问题
另外我不top的时候他的最小值是6,我觉得他min的时候没有算top,可是怎么想不对select Id from table where SourceId=22292108 107 106 95 93 56 6
select min(tt.id) from (select top (5*1) Id from table where SourceId=22292 order by id desc) tt
这样是对的,我一order by 就对了,可是我只是想知道为什么,我的所以写的好好的 还有另外一张差不多一样的表到可以,就这个不行
select Id from table where SourceId=22292 这句返回的结果不止5条吧?
select min(id) from (select top (5*1) Id from table where SourceId=22292)
因为select Id from table where SourceId=22292得到的结果不止5条。select min(tt.id) from (select top (5*1) Id from table where SourceId=22292) tt
相当于 select min(tt.id) from (select top (5*1) Id from table where SourceId=22292 order by id asc) tt
select min(tt.id) from (select top (5*1) Id from table where SourceId=22292) tt
可问题是我这样写他的结果是1 一点里面要加order by才行 我就觉得奇怪
测试数据create table test ( id int, SourceId int )insert into test values(6,22292) insert into test values(108,22292) insert into test values(107,22292) insert into test values(106,22292) insert into test values(95,22292) insert into test values(93,22292)select top 5 Id from test where SourceId=22292select min(tt.id) from (select top 5 Id from test where SourceId=22292) tt 结果就是6 create table test ( id int, SourceId int ) insert into test values(108,22292) insert into test values(107,22292) insert into test values(106,22292) insert into test values(95,22292) insert into test values(93,22292) insert into test values(6,22292) select top 5 Id from test where SourceId=22292 select min(tt.id) from (select top 5 Id from test where SourceId=22292) tt 结果93,你会发现他跟你插入数据的顺序有关系,所以你要按照ID来order by
107
106
95
93
56
6
select min(tt.id) from
(select top (5*1) Id from table where SourceId=22292 order by id desc) tt
这样是对的,我一order by 就对了,可是我只是想知道为什么,我的所以写的好好的
还有另外一张差不多一样的表到可以,就这个不行
这句返回的结果不止5条吧?
(select top (5*1) Id from table where SourceId=22292)
(select top (5*1) Id from table where SourceId=22292) tt
相当于
select min(tt.id) from
(select top (5*1) Id from table where SourceId=22292 order by id asc) tt
select min(tt.id) from
(select top (5*1) Id from table where SourceId=22292) tt
可问题是我这样写他的结果是1
一点里面要加order by才行
我就觉得奇怪
(
id int,
SourceId int
)insert into test values(6,22292)
insert into test values(108,22292)
insert into test values(107,22292)
insert into test values(106,22292)
insert into test values(95,22292)
insert into test values(93,22292)select top 5 Id from test where SourceId=22292select min(tt.id) from
(select top 5 Id from test where SourceId=22292) tt 结果就是6 create table test
(
id int,
SourceId int
)
insert into test values(108,22292)
insert into test values(107,22292)
insert into test values(106,22292)
insert into test values(95,22292)
insert into test values(93,22292)
insert into test values(6,22292)
select top 5 Id from test where SourceId=22292
select min(tt.id) from
(select top 5 Id from test where SourceId=22292) tt 结果93,你会发现他跟你插入数据的顺序有关系,所以你要按照ID来order by