一条sql语句怎么有错

[yung7086]

select *, row_number()over (order by customer) as num from TableA where num>1

这个执行的时候说num不存在

select * from (select *, row_number()over (order by customer)as num from TableA ) a where a.num>1
一般是这样写的，，我问下为什么那样写不可以呢。求指点

[小火龙果]

自己放sql里面执行一下不就知道哪里错了吗

[宝_爸]

select *, row_number()over (order by customer) as num from TableA where num>1 不行是因为where 在select前面执行，因此select 中的as给的别名, where是不知道的。

参考:
SQL SERVER – Logical Query Processing Phases – Order of Statement Execution
http://blog.sqlauthority.com/2009/04/06/sql-server-logical-query-processing-phases-order-of-statement-execution/


select * from (select *, row_number()over (order by customer)as num from TableA ) a where a.num>1可以是因为括号中的语句执行后，num 已经是一个column name了，因此where 中可以使用

[xiaoning8201]

select * from (select *,row_number() over(order by CODEID) as my from BASEDETAILMST) as b where my > 0 and my<=1

[EnForGrass]

其实很简单，既然是from TableA ，因为TableA 中没有num,所以不存在

[麦麦]

num是别名，并没有存在于TableA中，所以不存在。

[EnForGrass]

[Quote=引用 3 楼 的回复:]

其实很简单，既然是from TableA ，因为TableA 中没有num,所以不存在
[/Quote]

create table employee (empid int ,deptid int ,salary decimal(10,2))
insert into employee values(1,10,5500.00)
insert into employee values(2,10,4500.00)
insert into employee values(3,20,1900.00)
insert into employee values(4,20,4800.00)
insert into employee values(5,40,6500.00)
insert into employee values(6,40,14500.00)
insert into employee values(7,40,44500.00)
insert into employee values(8,50,6500.00)
insert into employee values(9,50,7500.00)

SELECT * FROM employee

SELECT * FROM (SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee) AS newtable WHERE newtable.rank>2
/*
empid       deptid      salary                                  rank
----------- ----------- --------------------------------------- --------------------
5           40          6500.00                                 3

(1 行受影响)

*/

[qxyywy]

[Quote=引用楼主 的回复:]
select *, row_number()over (order by customer) as num from TableA where num>1

这个执行的时候说num不存在

select * from (select *, row_number()over (order by customer)as num from TableA ) a where a.num>1
一……
[/Quote]

这是开窗函数 上面的几位也都说了原因
另注：开窗函数不能直接跟在where之后 如：
select * from TableA where row_number()over (order by customer)>1

具体的开窗函数使用方法楼主自己搜索吧