MSSQL(limit) → MYSQL

2012-09-24

14433
0

最近將MYSQL查詢改成MSSQL

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

MYSQL


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

MSSQL


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

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

--(方法A)效率較好



select top 123 * 
 from [user] 
 order by emp_id desc

--(方法B)效率較差


 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)效率較好



  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)效率較差




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

回首頁