sql語法中的一個重點(轉)

sql語法中的一個重點(轉)[@more@]count(*) 比count(id)好

select count(DISTINCT t_enter,caller) as size from tab可以踢出重複統計

mysql可以斷章取義（從0開始10個）
select * as size from tab limit 0,10
也可以取前幾個
select * as size from tab limit 10
就等價與mssql的top
select top 10 * as size from tab

mssqlserver要實現limit的選取功能就比較麻煩比如從4～6可以如下寫法：
select * from (select top 3 * from (select top 6 * from @t order by id)a order by a.id desc)b order by b.id
===============================================以下是抄來的

create table [testtable] (
[id] [int] identity (1, 1) not null ,
[firstname] [nvarchar] (100) collate chinese_prc_ci_as null ,
[lastname] [nvarchar] (100) collate chinese_prc_ci_as null ,
[country] [nvarchar] (50) collate chinese_prc_ci_as null ,
[note] [nvarchar] (2000) collate chinese_prc_ci_as null
) on [primary]
go



插入資料：(2萬條，用更多的資料測試會明顯一些)
set identity_insert testtable on

declare @i int
set @i=1
while @i<=20000
begin
insert into testtable([id], firstname, lastname, country,note) values(@i, firstname_xxx,lastname_xxx,country_xxx,note_xxx)
set @i=@i+1
end

set identity_insert testtable off



-------------------------------------

分頁方案一：(利用not in和select top分頁)
語句形式：
select top 10 *
from testtable
where (id not in
(select top 20 id
from testtable
order by id))
order by id


select top 頁大小 *
from testtable
where (id not in
(select top 頁大小*頁數 id
from 表
order by id))
order by id

-------------------------------------

分頁方案二：(利用id大於多少和select top分頁）
語句形式：
select top 10 *
from testtable
where (id >
(select max(id)
from (select top 20 id
from testtable
order by id) as t))
order by id


select top 頁大小 *
from testtable
where (id >
(select max(id)
from (select top 頁大小*頁數 id
from 表
order by id) as t))
order by id


-------------------------------------

分頁方案三：(利用sql的遊標儲存過程分頁)
create procedure xiaozhengge
@sqlstr nvarchar(4000), --查詢字串
@currentpage int, --第n頁
@pagesize int --每頁行數
as
set nocount on
declare @p1 int, --p1是遊標的id
@rowcount int
exec sp_cursoropen @p1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as 總頁數--,@rowcount as 總行數,@currentpage as 當前頁
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @p1,16,@currentpage,@pagesize
exec sp_cursorclose @p1
set nocount off

其它的方案：如果沒有主鍵，可以用臨時表，也可以用方案三做，但是效率會低。
建議最佳化的時候，加上主鍵和索引，查詢效率會提高。

透過sql 查詢分析器，顯示比較：我的結論是:
分頁方案二：(利用id大於多少和select top分頁）效率最高，需要拼接sql語句
分頁方案一：(利用not in和select top分頁) 效率次之，需要拼接sql語句
分頁方案三：(利用sql的遊標儲存過程分頁) 效率最差，但是最為通用

在實際情況中，要具體分析。