提过了点数据,请各位帮忙看一下--第一个问题的数据 --创建视图ABC Create View ABC as SELECT TOP 100 PERCENT * FROM ( Select 1 id,'A' col Union All Select 2 id,'B' col Union ALL Select 3 id,'C' col Union All Select 4 id,'D' col Union All Select 5 id,'E' col )A ORDER BY ID DESC GO --视图中按ID倒排序了,可直接查询视图ABC时,排序失效 SELECT * FROM ABC GO --删除视图ABC Drop View ABC GO--第二个问题--第一个子查询中指定了id倒排,可最后的查询结果中倒排失效, --order by 只有写在最外层才可以的,如果里面的结果想排序怎么办。 SELECT * FROM ( SELECT TOP 100 PERCENT * FROM (Select 1 id,'一' col Union All Select 2 id,'二' col Union ALL Select 3 id,'三' col ) A ORDER BY id desc Union All SELECT TOP 100 PERCENT * FROM ( Select 4 id,'B' col Union ALL Select 5 id,'C' col Union All Select 6 id,'D' col Union All Select 7 id,'E' col )B ) AA GO
LZ 不会在视图中使用 TOP 100 PERCENT ... ORDER BY 语句,让视图生成排序的数据吧。在视图定义中使用 TOP 100 PERCENT ... ORDER BY 语句,SQL SERVER 也不能保证视图返回的数据按顺序排列,SQL SERVER 2K5 的查询优化器会忽略这样的排序操作。
select * from ( select top 99999 * from ( select 1 id,'一' col union all select 2 id,'二' col union all select 3 id,'三' col ) a order by id desc union all select top 100 percent * from ( select 4 id,'b' col union all select 5 id,'c' col union all select 6 id,'d' col union all select 7 id,'e' col )b ) aa 这样可以排
--创建视图ABC
Create View ABC
as
SELECT TOP 100 PERCENT * FROM (
Select 1 id,'A' col
Union All
Select 2 id,'B' col
Union ALL
Select 3 id,'C' col
Union All
Select 4 id,'D' col
Union All
Select 5 id,'E' col
)A ORDER BY ID DESC
GO
--视图中按ID倒排序了,可直接查询视图ABC时,排序失效
SELECT * FROM ABC
GO
--删除视图ABC
Drop View ABC
GO--第二个问题--第一个子查询中指定了id倒排,可最后的查询结果中倒排失效,
--order by 只有写在最外层才可以的,如果里面的结果想排序怎么办。
SELECT * FROM
(
SELECT TOP 100 PERCENT * FROM
(Select 1 id,'一' col
Union All
Select 2 id,'二' col
Union ALL
Select 3 id,'三' col
) A ORDER BY id desc
Union All
SELECT TOP 100 PERCENT * FROM
(
Select 4 id,'B' col
Union ALL
Select 5 id,'C' col
Union All
Select 6 id,'D' col
Union All
Select 7 id,'E' col
)B
) AA
GO
(
select top 99999 * from
(
select 1 id,'一' col
union all
select 2 id,'二' col
union all
select 3 id,'三' col
)
a order by id desc
union all
select top 100 percent * from
(
select 4 id,'b' col
union all
select 5 id,'c' col
union all
select 6 id,'d' col
union all
select 7 id,'e' col
)b
) aa
这样可以排