In字句中 出现率高优先顺序
在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数 eg: id in (2008, 2006, 2007, 2009)
or 替代 in
a=1 or a=2 如果a=1, 那么a=2将不会被计算和处理
a in(1,2) 如果编译器没有做优化, 则会先分解再判断, 时间会相对长. 如果编译器做了优化处理, 效率与or相当
between 替代 in
类似id 为int型或只包含整数值的情况
select fields from table where id in (1,2,3,4)
==>
select fields from table where id between 1 and 4
union/union all 替代 or
两个条件不互逆使用union，互逆使用union all
-- union all 替代 or
select fields from table where flag=4 or flag=9
==>
select fields from table where flag=4
union all
select fields from table where flag=9
-- union 替代 or
select fields from table where category = 'new' or date = '2018-01-26'
==>
select fields from table where category = 'new'
union
select fields from table where date = '2018-01-26'
函数
Count(*)
count(1) count(*) count(列名）
Select COUNT(*)的效率较低，尽量变通写法，而EXISTS快.同时请注意区别： select count(Field of null) from Table 和 select count(Field of NOT null) from Table 的返回值是不同的！
语句 1：
where stt.date_of_settlement <= @ddt
and stt.date_of_settlement >= khb.date_of_settlement
and stt.date_of_settlement >= crm.date_of_begin
and stt.date_of_settlement < crm.date_of_end
and stt.date_of_settlement >= scrm.date_of_begin
and stt.date_of_settlement < scrm.date_of_end
改进 2：
where stt.date_of_settlement
BETWEEN greast(khb.date_of_settlement, crm.date_of_begin, scrm.date_of_begin) and @ddt
and stt.date_of_settlement < least(crm.date_of_begin, scrm.date_of_end)
