VIEW中的ORDER BY无法正常工作 [英] ORDER BY in VIEW not working

问题描述

我有以下视图，如果我使用vwRouteReference作为行数源

用于MS Access表单中的组合框或运行SELECT * FROM

vwRouteReference"在SQL查询分析器中，行不会通过Numb排序

。


我在网上看到的所有内容都表明包括TOP

指令应在视图中启用ORDERY BY。有人有这个想法

为什么这个特定视图的排序不正常？谢谢。


创建视图vwRouteReference

AS

SELECT TOP 100 PERCENT tblItem.ID，

tblItem.Numb +'' - ''+ tblQuestion.DescrPrimary AS FullName，

tblItem.Numb，tblQuestion.DescrPrimary AS Type

FROM tblItem INNER JOIN tblQuestion

ON（tblItem.ID = tblQuestion.Item_ID）

WHERE（（（tblItem.Category_ID）> 0））

UNION

SELECT TOP 100 PERCENT tblItem.ID，

tblItem.Numb +'' - ''+ tblItem.Type +''：''+ tblItem.Class AS FullName，

tblItem.Numb，tblItem.Type +''：''+ tblItem.Class AS Type

FROM tblItem

WHERE（（（tblItem.Type）=''分配''OR（tblItem.Type）=''检查''或

（tblItem.Type）=''路线''））

ORDER BY tblItem.Numb

I have the view below and if I use vwRouteReference as the rowsource
for a combo box in an MS Access form or run "SELECT * FROM
vwRouteReference" in SQL Query Analyzer, the rows don''t come through
sorted by Numb.

Everything I''ve read on the web suggests that including the TOP
directive should enable ORDERY BY in views. Does someone have an idea
why the sorting is not working correctly for this particular view? thanks.

CREATE VIEW vwRouteReference
AS
SELECT TOP 100 PERCENT tblItem.ID,
tblItem.Numb + '' - '' + tblQuestion.DescrPrimary AS FullName,
tblItem.Numb, tblQuestion.DescrPrimary AS Type
FROM tblItem INNER JOIN tblQuestion
ON (tblItem.ID = tblQuestion.Item_ID)
WHERE (((tblItem.Category_ID)>0))
UNION
SELECT TOP 100 PERCENT tblItem.ID,
tblItem.Numb + '' - '' + tblItem.Type + '' : '' + tblItem.Class AS FullName,
tblItem.Numb, tblItem.Type + '' : '' + tblItem.Class AS Type
FROM tblItem
WHERE (((tblItem.Type) = ''Assignment'' OR (tblItem.Type) = ''Check'' OR
(tblItem.Type) = ''Route''))
ORDER BY tblItem.Numb

推荐答案

你不应该使用SELECT TOP 100 PERCENT。从视图中删除它 - 和

以及ORDER BY一样。然后，从视图中选择并指定ORDER BY。


-

Tom


--- ------------------------------------------------- < br $>
Thomas A. Moreau，理学士，博士，MCSE，MCDBA

SQL Server MVP

专栏作家，SQL Server专业版

加拿大多伦多
www.pinpub.com


" Beowulf" <是***************** @ hotmail.com>写在消息

新闻：mssBf.23262

You shouldn''t use SELECT TOP 100 PERCENT. Remove that from the view - as
well as the ORDER BY. Then, select from the view and specify the ORDER BY.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com

"Beowulf" <be*****************@hotmail.com> wrote in message
news:mssBf.23262

Ez3.18813@trnddc03 ...

我有以下观点，如果我使用vwRouteReference作为行数源

用于MS Access表单中的组合框或运行SELECT * FROM

vwRouteReference。在SQL查询分析器中，行不会通过Numb排序

。


我在网上看到的所有内容都表明包括TOP

指令应在视图中启用ORDERY BY。有人有这个想法

为什么这个特定视图的排序不正常？谢谢。


创建视图vwRouteReference

AS

SELECT TOP 100 PERCENT tblItem.ID，

tblItem.Numb +'' - ''+ tblQuestion.DescrPrimary AS FullName，

tblItem.Numb，tblQuestion.DescrPrimary AS Type

FROM tblItem INNER JOIN tblQuestion

ON（tblItem.ID = tblQuestion.Item_ID）

WHERE（（（tblItem.Category_ID）> 0））

UNION

SELECT TOP 100 PERCENT tblItem.ID，

tblItem.Numb +'' - ''+ tblItem.Type +''：''+ tblItem.Class AS FullName，

tblItem.Numb，tblItem.Type +''：''+ tblItem.Class AS Type

FROM tblItem

WHERE（（（tblItem.Type）=''分配''OR（tblItem.Type）=''检查''或

（tblItem.Type）=''路线''））

ORDER BY tblItem.Numb

Ez3.18813@trnddc03...
I have the view below and if I use vwRouteReference as the rowsource
for a combo box in an MS Access form or run "SELECT * FROM
vwRouteReference" in SQL Query Analyzer, the rows don''t come through
sorted by Numb.

Everything I''ve read on the web suggests that including the TOP
directive should enable ORDERY BY in views. Does someone have an idea
why the sorting is not working correctly for this particular view? thanks.

CREATE VIEW vwRouteReference
AS
SELECT TOP 100 PERCENT tblItem.ID,
tblItem.Numb + '' - '' + tblQuestion.DescrPrimary AS FullName,
tblItem.Numb, tblQuestion.DescrPrimary AS Type
FROM tblItem INNER JOIN tblQuestion
ON (tblItem.ID = tblQuestion.Item_ID)
WHERE (((tblItem.Category_ID)>0))
UNION
SELECT TOP 100 PERCENT tblItem.ID,
tblItem.Numb + '' - '' + tblItem.Type + '' : '' + tblItem.Class AS FullName,
tblItem.Numb, tblItem.Type + '' : '' + tblItem.Class AS Type
FROM tblItem
WHERE (((tblItem.Type) = ''Assignment'' OR (tblItem.Type) = ''Check'' OR
(tblItem.Type) = ''Route''))
ORDER BY tblItem.Numb

订购只是2000和7.0中声明的副作用;订单

BY与TOP一起输出订单只能通过在视图本身上使用

ORDER BY来实现....


从vwRouteReference订单中选择* ....


Tony。


-

Tony Rogerson

SQL Server MVP
http：// sqlserverfaq。 com - 免费视频教程

" Beowulf" <是***************** @ hotmail.com>在消息中写道

news：mssBf.23262

Hi,

Ordering was only a side effect of the statement in 2000 and 7.0; the ORDER
BY goes with the TOP and the output is order can only be gaurenteed by using
ORDER BY on the view itself as below...

select * from vwRouteReference order by ....

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Beowulf" <be*****************@hotmail.com> wrote in message
news:mssBf.23262

这篇关于VIEW中的ORDER BY无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！