RowID為人工生成的排序序號。
如果移除 and employeeid = 39312這個篩選條件,則查詢時間會大幅增加。
原因在於最外層的查詢結果有292筆,那SELECT子句內的子查詢就會反覆查詢292次。
在加上 and employeeid = 39312 的查詢條件後 , 那麼子查詢就只會查詢一次。
可想而知,效率會有顯著提昇。
SELECT [EmployeeID]
,(select count(*) from [Test].[dbo].[Employees] as b
where hiredate >= ’2009-05-01′ and hiredate <= ’2009-05-30′
and b.employeeid <= a.employeeid
) as rowid
,[HireDate]
FROM [Test].[dbo].[Employees] as a
where hiredate >= ’2009-05-01′ and hiredate <= ’2009-05-30′
and employeeid = 39213
order by employeeid
