在程序开发过程中一段查询语句，where字句中使用到case when then，在此记录下
WHERE ACTUALNUMBER!=(CASE WHEN APPROVAL_DEPT IS NOT NULL THEN APPROVALNUMBER
WHEN APPROVAL_PERSON1 IS NOT NULL AND APPROVAL_DEPT IS NULL THEN ONUMBER
ELSE PLANNUMBER END)根据条件的不同ACTUALNUMBER的比较值也不同。
转载于:https://www.cnblogs.com/coolboys/p/where%e5%ad%97%e5%8f%a5%e4%bd%bf%e7%94%a8casewhenthen.html
数据库中简单的SQL语句以及where字句
select * from dept;
select * from emp;
--查询表中指定字段中的值 select 字段名1，字段名2，.....from表名，其中*代表所有
select empno from emp;
select empno,ename from emp;
--给查询结果中的字段名使用别名
--在字段名后使用关键字 字段名 as “别名”
--作用：方便查看查询结果
--注意：as关键字可以省略不写，别名中没有特殊字符，双引号也可以不写
select empno 员工编号，ename"员工 姓名"，job as 工作，mgr as "领导编号" from emp;
--连接符：select 字段名||‘字符’||字段名||‘字符’||...from 表名
--||为sql语句中的字符连接符，使用在select和from之间
--字符链接格式为：字段名 || ‘字符’ || 字段名
--注意：一个拼接好的连接在结果中是作为一个新的字段显示，可以使用别名优化字段显示
select empno || '的姓名是' || ename as"信息"，job || '哈哈'||mgr from emp; （as在这里是作为的意思）
--去除重复 select distinct 字段名，字段名，...from 表名
--注意：去除重复的规则是按照行进行去除的，多行数据完全相同取其一
select distinct job,mgr from emp;
--排序
--单字段排序：asc为升序排列，desc为降序排列
--多字段排序：--select * from emp order by 字段名1，字段明2...
--先按照字段一排序，如果字段一的值相同，则按照字段二的值排序....
select * from emp order by empno desc；
select empno,ename,job from emp order by ename asc；
select * from emp order by empno,ename；--多字段排序（不特意规定，都是从小到大排列）
--字段的逻辑运算
select * from emp
select empno,ename,job,sal*2+1000,sal+comm from emp;
-----------------------------------------------------------------------------------------------------------------------------
--使用where字据查询筛选
--select 字段名，字段名，...from 表名 where 筛选条件
--单筛选条件
--使用运算符进行筛选 =，<,>,<=,>=,<>单个条件中
--注意：如果条件中的值为字符，必须使用单引号括起来
--查询所有员工的工资信息
select empno,ename,sal+comm as 薪资 from emp;
--查询SMITH的个人信息
select * from emp where ename='SMITH';
--查询SMITH的薪资信息，逻辑运算符=
select empno,ename,sal+comm from emp where ename ='SMITH';
--查询工资大于2000的员工信息，逻辑符'>'
select * from emp where sal>'2000';
--查询工资不等于3000的员工信息
select * from emp where sal<>3000 order by sal
--练习：
--查看工作等于CLERK的员工信息
select * from emp where job ='CLERK';
--查看工资大于1250的员工姓名和工作
select ename,job from emp where sal>1250
--查看工资小于等于2000的员工信息
select * from emp where sal<=2000;
--查看工资大于等于2000的员工信息
select * from emp where sal>=2000;
--查看工资不等于1500的员工信息
select * from emp where sal<>1500;
--查看入职信息在81年后员工的信息
--注意：oracle默认日期格式为 日-月-年 实例“03-1月-1981”
select * from emp order by hiredate;
select * from emp where hiredate>='01-1月-1981' order by hiredate;--多条件查询（where字句关键字：and,or,like,is null,is not null,in,between...and...）
----查询工资在2000到3000之间的员工信息
----使用and关键字，多条件同时成立的筛选使用and关键字进行条件连接
select * from emp where sal >=2000 and sal <3000;
使用betwnne... and...关键字进行条件连接，包含两头的数据
select * from emp where sal between 2000 and 3000;
----查询工作为SALESMAN,ANALYST,MANAGER的员工信息
--使用or关键字，进行或条件筛选
select * from emp where job='SALESMAN' or job='ANALYST' or job='MANAGER' order by job;
--使用in关键字，同样能进行或筛选，但是in中的内容只能为一个字段中的值
select * from emp where job in('SALESMAN','ANALYST','MANAGER');
----查询姓名中包含S的，以s开头的，以s结尾的，第二个字符为A的，名字中包含'_'的员工
select * from emp where ename like '%S%'--包含S的
select * from emp where ename like 'S%'--以S开头的
select * from emp where ename like '%S'--以S结尾的
select * from emp where ename like '_A%'--第二个字符为A的 ，'_'表示一个任意的字符，'%'表示任意多个的任意字符
select * from emp where ename like '%/_%' escape'/'--表示名字中包含'_'的员工
----查询有津贴的员工信息
------ select * from 表名 where字段名is null 字段值为null
------ select * from 表名 where字段名is not null 字段值不为null
------多的条件使用关键字and进行连接，筛选的是所有符合条件的数据
---------- select * from 表名where筛选条件1 and 条件2 and ...
select * from emp where comm is not null and comm>0共计四种运算符：算术>连接>比较>逻辑
背景介绍
昨天在写sql脚本，与前端进行调试的时候发现，SQL中的字段别名在查询的时候竟然不识别，代码如下：
<select id="getDiscountUnionListByPage" resultType="Map"> select dis.merchant_id as merchantId, mer.name as merchantName, mer.address as merchantAddress, dis.id as couponId, discount_price as couponPrice, activity_end_date as couponEndDate, discount_name as couponName, discount_start as couponStart, discount_rest as couponNum, ROUND(6378.138*2*ASIN(SQRT(POW(SIN((#{longitude}*PI()/180-mer.longitude*PI()/180)/2),2) +COS(#{longitude}*PI()/180)*COS(mer.longitude*PI()/180)*POW(SIN((#{latitude}*PI()/180-mer.latitude*PI()/180)/2),2)))*1000) AS distance from tb_discount dis left join tb_union_merchant mer on dis.merchant_id = mer.id <where> del_flag = '0' and putaway_flag = '1' and vouchers_create = '3' and is_union ='1' and mer.industry_first = #{industryFirst} and mer.industry_twice != #{industrySecond} and distance <= 3000 </where> order by distance asc </select>其中distance为字段别名，表示的是根据经纬度计算的距离，报错如下：
问题解决
很显然，这里的distance别名是不被识别的，当时的紧急做法就是将脚本里面的distance都换成表达式，果然问题得到了解决，但是很纳闷的是为什么distance字段不能被识别呢（其实，这里面有两个distance，where语句中的distance是不被识别的，但是order by的是可以识别，当时以为orderby 也不能识别别名字段，浪费了好多时间查资料）。
解决方法：
方式一：将where中的别名换成表达式即可解决
方式二：使用子查询的方式，然后就可以在where子句中调用了
SELECT type,id,merchantId,name, address,industryFirst,productPrice,discountPrice,integral,distance,volume from ( SELECT '1' AS type,product.id AS id,product.merchant_id merchantId,product.product_name AS name, '' AS address, '' AS industryFirst, product.product_price AS productPrice,product.discount_price AS discountPrice,product.exchange_integral AS integral, ROUND(6378.138*2*ASIN(SQRT(POW(SIN((#{longitude}*PI()/180-longitude*PI()/180)/2),2) +COS(#{longitude}*PI()/180)*COS(longitude*PI()/180)*POW(SIN((#{latitude}*PI()/180-latitude*PI()/180)/2),2)))*1000) AS distance, COUNT(record.id) AS volume FROM tb_union_merchant_product product LEFT JOIN tb_union_merchant merchant ON product.merchant_id = merchant.id LEFT JOIN tb_union_merchant_examine examine ON merchant.id = examine.merchant_id LEFT JOIN tb_union_product_record record ON product.id = record.product_id WHERE distinguish= #{distinguish} AND merchant.status ='1' AND merchant.longitude IS NOT NULL AND merchant.latitude IS NOT NULL AND examine.examine_status='1' AND product.product_name LIKE '%${keyword}%' and product.product_states = '1' and product.del_flag = '0' GROUP BY product.id order by distance ,volume DESC问题拓展
为什么where子句中无法使用到字段别名作为筛选条件呢
(7) SELECT
(8) DISTINCT <select_list>
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) HAVING <having_condition>
(9) ORDER BY <order_by_condition>
(10) LIMIT <limit_number>
执行顺序依次为：
所以，以此可以看出，为什么在where语句中没法使用查询列的别名进行过滤了，因为调用where子句的时候，select子句还没有开始执行，所以不识别，同理，order by子句中是可以使用
在查询mysql的字段别名使用的时候，翻到了mysql的官方文档，里面对别名的使用场景进行了简要的介绍：
意思就是别名可以使在order by、having、group by 子句中，但是根据上面的SQL执行过程，很明显group by 和 having都在select之前啊，这里值得注意的是，mysql对group by 进行了优化加强，所以在group by子句中可以使用别名进行分类，但是其他数据库还是遵循着SQL的执行顺序
都是因为字段别名的问题展开的一系列思考，大多也还是基础知识吧，稍微再巩固一下，继续加油啦各位！
提高SQL查询效率
作者：@立雪三尺
（本文于2012年11月3日转载自新浪微博，最近开始学数据库，对于语句操作很头疼。看到这篇文章，和大家分享一下）
1,对查询进行优化，应尽量避免全表扫描，首先应考虑在where及order by涉及的列上建立索引。
,2,应尽量避免在where字句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描。如：
Select id from t where num is null
可以在num上设置默认值0.确保表中num列没有null值，然后这样查询：
Select id from t where num = 0
3, 应尽量避免在where子句中使用!=或<>操作符，否则将导致引擎放弃使用索引而进行全表扫描。
4，应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描。如：
Select id from t where num = 10 or num = 20
可以这样查询：
Select id from t where num = 10
union all
select id from t where num = 20
5，in 和not in 也要慎用，否则将会导致全表扫描，如：
Select id from t where num in(1,2,3)
对于连续的数值，能用between就不要用in了：
Select id from t where num between 1 and 3
6, 下面的查询也将导致全表扫描：
Select id from t where name like ‘%abc%’
若提高效率，可以考虑全文检索
7， 如果在where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因为无法作为索引选择的输入项。如下面语句将进行全表扫描：
Select id from t where num = @num
可以改为强制查询使用索引：
Select id from t with (index(索引名)) where num = @num
8，应尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
Select id from t where num/2 = 100
应改为：
Select id from t where num = 100*2
9,应尽量避免在where子句中对字段进行函数操作。这将导致引擎放弃使用索引而进行全表扫描。如：
Select id from t where substring(name, 1, 3)=’abc’ --name 以abc开头的id
Select id from t where datadiff(day, createdate, ‘2005-11-30’)=0 -- ‘2005-11-30’生成的id
应改为：
Select id from t where name like ‘abc%’
Select id from t where createdate >=’2005-11-30’ and createdate<’2005-12-1
10,不要再where子句中的‘=’左边进行函数，算术运算或者其他表达式运算，否则系统将可能无法正确使用索引。
11，在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引。否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
12，不要写一些没有意义的查询，如需要生成一个空表结构：
Select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
Create table #t(…)
13很多时候用exists代替in是一个好的选择：
Select num from a where num in(select num from b)
用下面的语句替换：
Select num from a where exists(select 1 from b where num = a.num)
14,并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male，female几乎各有一半，那么即使在sex上建了索引也对查询效率起不了作用。
15，索引并不是越多越好，索引固然可以提高响应的效率，但同时也降低了insert及update的效率，因为insert或update时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
16，应尽可能地避免更新clustered索引数据列，因为clustered索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新clustered索引数据列，那么需要考虑是否应将该索引建为clustered索引。
17,尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
18，尽可能的使用varchar/nvarchar代替char/nchar，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
19，任何地方都不要使用select * from t,用具体的字段列表代替“*”,不要返回用不到的任何字段。
20，尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
21，避免频繁创建和删除临时表，以减少系统表资源的消耗。
22，临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
23，在新建临时表时，如果一次性插入数据量很大。那么可以使用select into 代替create table，避免造成大量log，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table, 然后insert。
24，如果使用到了临时表，在存储过程的最后务必将所有的临时表显示删除，先truncate table，然后drop table， 这样可以避免系统表的较长时间锁定。
25，尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
26，使用基于游标的方法或临时表方法之前，应先寻找基集的解决方案来解决问题，基于集的方法通常更有效。
27，与临时表一样，游标并不是不可使用。对小型数据集使用FAST_FORWARD游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪种方法的效果更好。
28，在所有的存储过程和触发器的开始处设置SERT NOCOUNT ON，在结束时设置SET NOCOUNT OFF。无需在执行过程和触发器的每个语句后向客户端发送DNE_IN_PROC消息。
29尽量避免大事务操作，提高系统并发能力。
30，尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
转载于:https://www.cnblogs.com/codingcook/archive/2012/11/03/2752793.html
