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
一般是这样写的,,我问下为什么那样写不可以呢。求指点
一般是这样写的,,我问下为什么那样写不可以呢。求指点
解决方案 »
- 怎么把一个string转化成符合命名规则的string
- 老位老兄打包过.net 2005
- 用C#作一个文本编辑器的一些问题,希望大家帮帮忙!!!!!!
- 求购DataGrid、自定义报表、打印、串口通讯等源码模块
- 请教C#调用C++ DLL的问题
- 请问关于读取远程xml文件的问题
- 求一段清除所有Temporary Internet Files 的c#代码!!!!!
- 请问c#中哪个控件像vb6中frame控件一样或相似
- 线程中开启窗体 请教下
- 请问一下在C#中com组件与.net组件有什么区别呀????????
- C# 写入文件把内存占光了,怎么解决呀,以下是代码
- ((DataTable)dgv_Account.DataSource).Rows.Add();报错了
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 中可以使用
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 employeeSELECT * 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 行受影响)*/
这是开窗函数 上面的几位也都说了原因
另注:开窗函数不能直接跟在where之后 如:
select * from TableA where row_number()over (order by customer)>1具体的开窗函数使用方法楼主自己搜索吧