我有一个存储过程，它在调用时返回以下值:
EXEC @return_value = [dbo].[Activities_GetCrazyTables]
@iActivity = 49,
@iLanding = 6,
@iFirst = 1,
@iLast = 10,
@strSearch = '',
@bActiveOnly = 0
orderkey yellowpage_rownum yellowpage_id yellowpage_name
table4 ,,,,, 1,,,,,, 2022,,,,,, <牛山/kbd>
table4 ,,,,, 2,,,,,, 2055,,,,,, 门多西诺湖
table4 ,,,,, 3,,,,,, 2099,,,,,, 吊灯
但是当我在查询分析器中执行存储过程的实际代码时，它返回的结果不同:
orderkey yellowpage_rownum yellowpage_id yellowpage_name
table4 ,,,,, 1,,,,,, 2022,,,,,, 吊灯
table4 ,,,,, 2,,,,,, 2055,,,,,, 牛山
table4 ,,,,, 3,,,,,, 2099,,,,,, 门多西诺湖
与执行存储过程相比，为什么单独运行查询时会得到正确的排序顺序?
存储过程代码如下:
set @strSearch = LOWER(@strSearch)
select orderkey, y.* from
(
/* get the featured sponsors in order specified in the database */
select ROW_NUMBER() over (order by x.orderkey) as yellowpage_rownum, x.*
from (
/* top tier featured 1-5 */
select yellowpage_id, yellowpage_name, yellowpage_address1, yellowpage_address2, yellowpage_city, yellowpage_state, yellowpage_zip,
yellowpage_phone, yellowpage_url, yellowpage_latitude, yellowpage_longitude, yellowpage_featuredsponsor, yellowpage_premium, yellowpage_order,
yellowpageactivity_order, story_count,
'table1' as orderkey, yellowpage_active from
(select top 5 a.* from
(select ROW_NUMBER() over (order by yellowpage_name) as rownum, yellowpage_id, yellowpage_name, yellowpage_address1, yellowpage_address2, yellowpage_city, yellowpage_state, yellowpage_zip,
yellowpage_phone, yellowpage_url, yellowpage_latitude, yellowpage_longitude, yellowpage_featuredsponsor, yellowpage_premium, yellowpage_order,
yellowpageactivity_order, (select COUNT(storyyellowpage_story) from storyyellowpagexref join stories on (story_id=storyyellowpage_story) where story_active=1 and storyyellowpage_yellowpage=y.yellowpage_id) as story_count, yellowpage_active
from yellowpageactivityxref
join activities on (activity_id=yellowpageactivity_activity)
join yellowpages y on (yellowpage_id=yellowpageactivity_yellowpage)
join landingcities on (landingcity_city=yellowpage_city)
where yellowpageactivity_activity=@iActivity and landingcity_landing=@iLanding and yellowpage_featuredsponsor=1 and
(LOWER(yellowpage_name) LIKE '%' + @strSearch + '%' or LOWER(yellowpage_address1) LIKE '%' + @strSearch + '%')) as a
where a.rownum between 1 and 5 order by newid()) as b
union all
/* top tier featured 6-10 */
select yellowpage_id, yellowpage_name, yellowpage_address1, yellowpage_address2, yellowpage_city, yellowpage_state, yellowpage_zip,
yellowpage_phone, yellowpage_url, yellowpage_latitude, yellowpage_longitude, yellowpage_featuredsponsor, yellowpage_premium, yellowpage_order,
yellowpageactivity_order, story_count,
'table2' as orderkey, yellowpage_active from
(select top 5 a.* from
(select ROW_NUMBER() over (order by yellowpage_name) as rownum, yellowpage_id, yellowpage_name, yellowpage_address1, yellowpage_address2, yellowpage_city, yellowpage_state, yellowpage_zip,
yellowpage_phone, yellowpage_url, yellowpage_latitude, yellowpage_longitude, yellowpage_featuredsponsor, yellowpage_premium, yellowpage_order,
yellowpageactivity_order, (select COUNT(storyyellowpage_story) from storyyellowpagexref join stories on (story_id=storyyellowpage_story) where story_active=1 and storyyellowpage_yellowpage=y.yellowpage_id) as story_count, yellowpage_active
from yellowpageactivityxref
join activities on (activity_id=yellowpageactivity_activity)
join yellowpages y on (yellowpage_id=yellowpageactivity_yellowpage)
join landingcities on (landingcity_city=yellowpage_city)
where yellowpageactivity_activity=@iActivity and landingcity_landing=@iLanding and yellowpage_featuredsponsor=1 and
(LOWER(yellowpage_name) LIKE '%' + @strSearch + '%' or LOWER(yellowpage_address1) LIKE '%' + @strSearch + '%')) as a
where a.rownum between 6 and 10 order by newid()) as b
union all
/* top tier featured 11-15 */
select yellowpage_id, yellowpage_name, yellowpage_address1, yellowpage_address2, yellowpage_city, yellowpage_state, yellowpage_zip,
yellowpage_phone, yellowpage_url, yellowpage_latitude, yellowpage_longitude, yellowpage_featuredsponsor, yellowpage_premium, yellowpage_order,
yellowpageactivity_order, story_count,
'table3' as orderkey, yellowpage_active from
(select top 5 a.* from
(select ROW_NUMBER() over (order by yellowpage_name) as rownum, yellowpage_id, yellowpage_name, yellowpage_address1, yellowpage_address2, yellowpage_city, yellowpage_state, yellowpage_zip,
yellowpage_phone, yellowpage_url, yellowpage_latitude, yellowpage_longitude, yellowpage_featuredsponsor, yellowpage_premium, yellowpage_order,
yellowpageactivity_order, (select COUNT(storyyellowpage_story) from storyyellowpagexref join stories on (story_id=storyyellowpage_story) where story_active=1 and storyyellowpage_yellowpage=y.yellowpage_id) as story_count, yellowpage_active
from yellowpageactivityxref
join activities on (activity_id=yellowpageactivity_activity)
join yellowpages y on (yellowpage_id=yellowpageactivity_yellowpage)
join landingcities on (landingcity_city=yellowpage_city)
where yellowpageactivity_activity=@iActivity and landingcity_landing=@iLanding and yellowpage_featuredsponsor=1 and
(LOWER(yellowpage_name) LIKE '%' + @strSearch + '%' or LOWER(yellowpage_address1) LIKE '%' + @strSearch + '%')) as a
where a.rownum between 11 and 15 order by newid()) as b
union all
/* get the rest and sort by name */
select yellowpage_id, yellowpage_name, yellowpage_address1, yellowpage_address2, yellowpage_city, yellowpage_state, yellowpage_zip,
yellowpage_phone, yellowpage_url, yellowpage_latitude, yellowpage_longitude, yellowpage_featuredsponsor, yellowpage_premium, yellowpage_order,
yellowpageactivity_order, story_count,
'table4' as orderkey, yellowpage_active from
(select top 2147483647 a.* from
(select yellowpage_id, yellowpage_name, yellowpage_address1, yellowpage_address2, yellowpage_city, yellowpage_state, yellowpage_zip,
yellowpage_phone, yellowpage_url, yellowpage_latitude, yellowpage_longitude, yellowpage_featuredsponsor, yellowpage_premium, yellowpage_order,
yellowpageactivity_order, (select COUNT(storyyellowpage_story) from storyyellowpagexref join stories on (story_id=storyyellowpage_story) where story_active=1 and storyyellowpage_yellowpage=y.yellowpage_id) as story_count, yellowpage_active
from yellowpageactivityxref
join activities on (activity_id=yellowpageactivity_activity)
join yellowpages y on (yellowpage_id=yellowpageactivity_yellowpage)
join landingcities on (landingcity_city=yellowpage_city)
where yellowpageactivity_activity=@iActivity and landingcity_landing=@iLanding and yellowpage_featuredsponsor=0 and
(LOWER(yellowpage_name) LIKE '%' + @strSearch + '%' or LOWER(yellowpage_address1) LIKE '%' + @strSearch + '%')) as a
order by yellowpage_name) as b
) as x
) as y where y.yellowpage_rownum between @iFirst and @iLast
您的查询(Union的结果)没有使用order by语句。这意味着结果的顺序不是确定的，这意味着它在不同的运行中可能是不同的。
要解决这个问题，您需要在最后一个where语句后面添加一个Order by语句:
as y where y.yellowpage_rownum between @iFirst and @iLast Order by col1, col2
您还应该在最后一个查询中消除order by yellowpage_name语句，因为这将与最终结果集的排序无关。
这是一个非常复杂的查询，因此很难理解到底发生了什么。但是，您在完整的结果集中缺少一个Order By。
在关系理论中，集合(A, B, C)与(C, B, A)相同，序不相关。
你按orderkey排序，它不是唯一的。具有相同orderkey的行不一定以相同的顺序出现。
扩展你的order by条款来解决问题