MSSQL(limit) → MYSQL
最近將MYSQL查詢改成MSSQL
關於查詢限制條件記錄於下：
MYSQL
1 |
select * from [table] where ... LIMIT 123 |
MSSQL
1 |
select top 123* from [table] where ... |
--限制資料筆數(只取前面0~123筆)
--(方法A)效率較好
1 |
select top 123 * |
2 |
from [user] |
3 |
order by emp_id desc |
--(方法B)效率較差
1 |
select * |
2 |
from (select ROW_NUMBER() over (order by emp_id asc) rownum, |
3 |
* |
4 |
from [user] ) as yourselect |
5 |
where rownum between 0 and 123 |
6 |
order by emp_id desc |
--用來做分頁查詢(只取第123~150筆)
--(方法C)效率較好
1 |
select * |
2 |
from ( select ROW_NUMBER() over (order by emp_id asc) rownum, |
3 |
* |
4 |
from [user] ) as yourselect |
5 |
where rownum between 123 and 150 |
6 |
order by emp_id desc |
--(方法D)效率較差
1 |
SELECT TOP 150 * |
2 |
FROM [user] WHERE user_id_seq NOT IN |
3 |
( SELECT TOP 123 user_id_seq FROM [user] ORDER BY user_id_seq ASC ) |
4 |
ORDER BY emp_id ASC |
