在sql server中创建带有顺序的视图 [英] creating view with order by in sql server

问题描述

表tblLeave的数据如下:(注意：leaveId是Primary，并且是唯一数据的索引）



leaveId leave

1寄宿家庭

2病假

3产假

4产假（男）

5哀悼假



实际上我希望结果如下数据，即leaveId应该是升序。

The data of table tblLeave is as below: (note: leaveId is Primary, and leave is in index for unique data)

leaveId leave
1 Home Leave
2 Sick Leave
3 Maternity Leave
4 Maternity Leave (Male)
5 Mourning Leave

Actually I want the result as below data that leaveId should be in ascending.

0   <Select All>
1   Home Leave
2   Sick Leave
3   Maternity Leave
4   Maternity Leave (Male)
5   Mourning Leave

但我可以不要让它上升。

我的代码如下：

But I Could not make it ascending.
My Code as below:

CREATE VIEW vLeave
SELECT TOP 100 PERCENT leaveId, leaveName FROM tblLeave
UNION ALL
SELECT 0,'<Select All>'
 order by leaveId

我也用这种方式..

CREATE VIEW vLeave AS

SELECT 0，'< Select All>'

UNION ALL

SELECT TOP 100 PERCENT leaveId，leaveName FROM tblLeave ORDER BY leaveId



但

当我运行查询时这样

select * from vLeave，它不起作用正如我所愿。

I used in this way also..
CREATE VIEW vLeave AS
SELECT 0,'<Select All>'
UNION ALL
SELECT TOP 100 PERCENT leaveId, leaveName FROM tblLeave ORDER BY leaveId

but
when I run query Like this
"select * from vLeave", it doesnot work as I want.

推荐答案

自SQL 2005以来ORDER BY的一部分w在SQL中具有不同的含义（或者最精确地通过不同的规则进行优化）...

它不对视图的结果进行排序，而仅对内部select语句进行排序...所以如果你有这个顶级声明，顺序将确保你得到一个有序列表的顶部（100％？！？！？）部分，但它不会影响视图的结果......

（我知道你添加了TOP以启用ORDER BY，但它仍然无法工作）...

你必须要了解的是视图不是临时表 - 一个数据视图没有存储在任何地方...所以不能订购那些数据...

你有两个选择：

1.在视图上执行订单而不是在它里面...

2.创建一个表值函数来返回有序值......

Since SQL 2005 the ORDER BY part of a view has a different meaning in SQL (or to be most precise it optimized by different rules)...
It does not order the outcome of a view but the inner select statement only...So if you have that top statement, order by will ensure that you get the top (100% ?!?!?) part of an ordered list, but it will not affect the outcome of the view...
(I do understand that you added that TOP to enable ORDER BY, but it still will not work)...
What you have to understand that view is NOT a temporary table - the data of a view not stored anywhere...So can not be order of that data...
You have two options:
1. do the order by on the view and not inside it...
2. create a table-valued function to return the ordered values...

这篇关于在sql server中创建带有顺序的视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！