应用中发现sql中的and及or的执行效率问题
ｓｑｌ语句，为什么把最后的ｏｒ换成ａｎｄ，查询的就很快，使用的是mssql范例中northwind数据库为例，
select * from Orders a left join [Order Details] b on a.orderid = b.orderid
where a.customerid like '%ics%' or b.productid in (42,72)
追踪了语句的执行方案,发现使用or 时 是先执行的a 与b 交叉表，从结果集中再where出符合的记录，由于是全集交叉所以输出的数据量较大,执行的时间也就效长.
select * from Orders a left join [Order Details] b on a.orderid = b.orderid
where a.customerid like '%ics%' and b.productid in (42,72)
而and是先执行的where筛选出了符合的记录再做的交叉表,所以数据量少执行起来快.
相对于or 为了提高执行速度,想了一个办法,如下人为控制执行方案先筛选wher后交叉left
set statistics time on
select * from (select * from Orders where customerid like '%ics%') a left join
 (select * from [Order Details] b where productid in (42,72)) b on a.orderid = b.orderid
set statistics time off ......

导出表结构:
Tools-->Export User Objects -->选择要导出的表(包括Sequence等)-->.sql文件
导出表数据:
Tools-->Export Tables-->选择表,选择SQL Inserts-->.sql文件
执行这些.sql文件时,要使用新建Command Window来执行. ......

INSERT INTO
 tableName ( columnName1, columnName2, columnName3, columnName4)
VALUES      (  '45', '西藏办',  TO_Date( '2010-03-10 12:00:00', 'YYYY-MM-DD HH24:MI:SS'), NULL,);
commit; ......

SELECT g.rid, room_no, planed_count,cc from
    (SELECT rid,room_no, planed_count from YC_HOTEL_ROOM_T ) r
left join
    (SELECT rid,count(*) cc  from  YC_GUEST_INFO_T group by rid ) g
ON   r.rid = g.rid  WHERE planed_count<>cc ......

select ks.login_name,ks.exam_name,ks.start_time,ks.end_time,cj.score
from (
     select u.user_id,u.login_name,e.* from  cphrms.EXAM_USER eu, cphrms.users u, cphrms.exam_info e
     where eu.user_id = u.user_id and eu.exam_id = e.exam_id
) ks
left join  
(select t.exam_id, t.user_id, sum(t.score) as score  from cphrms.user_answer t group by t.exam_id, t.user_id) cj
on
   ks.user_id = cj.user_id and ks.exam_id=cj.exam_id 
where ks.login_name = '19901' 
order by start_time ......

从Table 表中取出第 m 条到第 n 条的记录：(Not In 版本)
SELECT TOP n-m+1 *
  from Table
  WHERE (id NOT IN (SELECT TOP m-1 id from Table ))
--从TABLE表中取出第m到n条记录 (Exists版本)
SELECT TOP n-m+1 * from TABLE AS a WHERE Not Exists
(Select * from (Select Top m-1 * from TABLE order by id) b Where b.id=a.id )
Order by id
--m为上标，n为下标,例如取出第8到12条记录,m=8,n=12,Table为表名
Select Top n-m+1 * from Table
Where Id>(Select Max(Id) from
(Select Top m-1 Id from Table Order By Id Asc) Temp)
Order By Id Asc
1.  Select Top n-m+1 * from Table Where Id>(Select Top m-1 Id from Table Order By Id Asc) Order By Id Asc
2.  SQL Server 2005 中可以用 ROW_NUMBER（） OVER（）配合取分页数据了。
DECLARE @StartRow INT
DECLARE @PageSize INT
SET ROWCOUNT 0
SET @StartRow = 100
SET @PageSize = 10
SELECT * from
(
  SELECT ROW_NUMBER() OVER (ORDER BY Name) AS RowNumber,* from Person
) AS A WHERE A.RowNumber BETWEEN @StartRow + 1 AND @StartRow + ......