001 table 查詢 區間

Post date: 2013/11/15 上午 03:10:43

最近將MYSQL查詢改成MSSQL

關於查詢限制條件記錄於下:

MYSQL

1

select * from [table] where ... LIMIT 123

MSSQL

1

select top 123* from [table] where ...

--限制資料筆數(只取前面0~123筆)

--(方法A)效率較好

1
2
3

select top 123 *

from [user]

order by emp_id desc

--(方法B)效率較差

1
2
3
4
5
6

select *

from (select ROW_NUMBER() over (order by emp_id asc) rownum,

  * 

from [user] ) as yourselect

where rownum between 0 and 123

order by emp_id desc

--用來做分頁查詢(只取第123~150筆)

--(方法C)效率較好

1
2
3
4
5
6

select *

from ( select ROW_NUMBER() over (order by emp_id asc) rownum,

 *

from [user] ) as yourselect

where rownum between 123 and 150

order by emp_id desc

--(方法D)效率較差

1
2
3
4

SELECT TOP 150 *

FROM [user] WHERE user_id_seq NOT IN

( SELECT TOP 123 user_id_seq FROM [user] ORDER BY user_id_seq ASC )

ORDER BY emp_id ASC