SQL语法粗整理
1、在同一张表中，对前一条数据进行更新性插入操作，即：
对上一条记录的部分属性(id, handledpeople, handledmessage, handledtime)进行更新操作，并将更新后的数据，在不改变原记录的情况下，插入到数据库中，此方法的SQL语句如下：
insert into table select sys_guid(), userid, subjectid, applytime, applyreason, 'change_A', 'change_B', sysdate from table
where handledtime = (select max(handledtime) from table where subjectid = 'f30c77cc-fd2a-49b1-a5dd-0e1b60505b1d');
ps:其中id有唯一性约束，必须进行更新操作。
此句SQL表示，查询指定的SUBJECTID条中HANDLEDTIME为最大的那条语句，进行更新部分字段的值后，将新结果插入到数据库中。
2、Oracle中，对于日期的格式化有一下几种：
a、输出日期时的格式化函数：
to_char(date,'yyyy-MM-dd hh24:mi:ss')此为24小时格式，输出如：2014-10-10 09:20:30
to_char(date,'yyyy-MM-dd hh12:mi:ss PM')此为24小时格式，输出如：10-10月-2014 09:20:30 上午
b、存储为格式化的日期函数：
to_data(date, 'yyyy-MM-dd hh24:mi:ss')其后格式同上
3、关于SQL的执行效率的问题：
有两条SQL语句，如下：
a：select a.* from tableA a where a.id = (select b.Aid from tableB b where b.id = 'xxx')
b：select a.id, a.name, a.age, a.gender from tableA a join tableB b on a.id = b.Aid where b.id = 'xxx'
分析：从结果来看，两条sql的执行结果是一致的，都能从数据库中取得正确的结果集，但是，从效率来讲，b种写法比a种写法的执行效率更高。
4、左关联、右关联以及全关联查询：
左关联：left join on:左边表的查询结果全部显示，既是右边的条件不满足，也能显示，即以左表为中心，一切数据都为左表服务；
右关联：right join on:右边的表为中心，为右表服务；
全关联：join on:只有双方都得到满足时，数据方可显示出来，二表为平等关系；
5、多表查询之效率（细节影响效率篇）：
a、FROM子句后面的表顺序：
ORACLE在解析sql语句的时候对FROM子句后面的表名是从右往左解析的，是先扫描最右边的表，然后在扫描左边的表，然后用左边的表匹配数据，匹配成功后就合并。 所以，在对多表查询中，一定要把小表写在最右边；
-- tableA：100w条记录 tableB：1w条记录
select count(1) from tableA, tableB;
select count(1) from tableB, tableA;(效率更高)
还有一种是三张表的查询：
select count(1) from tableA a,tableB b ,tableC c where a.id=b.id and a.id=c.id;
上面中tableA 为交叉表，根据oracle对From子句从右向左的扫描方式，应该把交叉表放在最末尾，然后才是最小表，所以上面的应该这样写：
-- tableA a 交叉表
-- tabelB b 100w条记录
--tableC c 1w条记录
select count(1) from tableB b ,tableC c ,tableA a where a.id=b.id and a.id=c.id;
b、Where子句后面的条件过滤：
ORACLE对where子句后面的条件过滤是自下向上，从右向左扫描的，所以和From子句一样一样的，把过滤条件排个序，按过滤数据的大小，自然就是最少数据的那个条件写在最下面，最右边，依次类推，例如：
--No.1 不可取 性能低下
select * from tableA a where a.id>500 and a.lx = '2b' and a.id < (select count(1) from tableA where id=a.id);
--No.2 性能高
select * from tableA a where a.id < (select count(1) from tableA where id=a.id) and a.id>500 and a.lx = '2b';
c、使用select的时候少用*，最好写上字段名，因为ORACLE的查询器会把*转换为表的全部列名，这个会浪费时间的，所以在大表中少用；
d、充分利用rowid ，可以用rowid来分页，删除查询重复记录，效率很高的，如：
--oracle查找重复记录
select * from tableA a where a.rowid >= (select min(rowid) from tableB b where a.column = b.column);
--oracle删除重复记录
delete from tableA a where a.rowid>=(select min(rowid) from tableB b where a.column=b.column);
--分页 start=10 limit=10
--end 为 start + limit
--1.查询要排列的表A
--2.查询A表的Rownum找出小于end的数据组成表B
--3.查询B表通过rownum找出大于start的数据完成
--简单的说先根据end值过滤数据，然后在根据start过滤数据
SELECT * FROM (SELECT a.*, ROWNUM rn FROM (SELECT * FROM uim_serv_file_data ORDER BY OUID) a where ROWNUM<=20) b
where rn>10 order by ouid desc;
e、存储过程中需要注意的，多用commit，可以释放资源，但是要谨慎；
f、减少对数据库表的查询，尽量能程序实现的就不要使用sql操作；
g、少用in，多用exists来代替，如：
--NO.1 IN的写法
SELECT * FROM TABLEA A WHERE A.ID IN (SELECT ID FORM TABLEB B WHERE B.ID > 1);
--NO.2 exists 写法
SELECT * FROM TABLEA A WHERE EXISTS(SELECT 1 FROM TABLEB B WHERE A.ID = B.ID AND B.ID > 1);
6、查询在一段时间内的指定数据：
使用time相关的函数：to_char和to_date，如：
SELECT
appName,
to_char(CREATETIME, 'yyyy-MM-dd') time
FROM TABLE
WHERE CREATETIME BETWEEN to_date('begintime', 'yyyy-MM-dd hh24:mi:ss') AND to_date('endtime', 'yyyy-MM-dd hh24:mi:ss')
ORDER BY CREATETIME ASC;
说明：查询createtime在begintime和endtime范围内的数据，并以createtime从小到大排序输出。
7、多表查询：
现有日志表，记录了用户申请APP的流程，如下：
表结构为：用户没做一次申请的动作，便会产生一条记录；管理员每做一次审核的动作，便会在原来的记录的基础上产生一条新的记录，新记录的部分数据与原记录相同，但后半部分数据则与原数据不同。现在要求：查询出用户申请APP动作的最新的记录，如果管理员有审核，则按handledTime排序取最新的那条记录，如果管理员没有进行审核（此时的handledTime字段为null值），则取出用户单独操作的那条记录，查询语句如下（此语句中包含了关联的另一个表的数据，但无伤大雅）：
SELECT appName, applyTime, handledTime, handledMessage, handledResult FROM (
SELECT
s.NAME appName,
to_char(f.APPLYTIME, 'yyyy-MM-dd') applyTime,
to_char(f.HANDLEDTIME, 'yyyy-MM-dd') handledTime,
f.HANDLEDMESSAGE handledMessage,
f.HANDLEDRESULT handledResult,
dense_rank()
OVER (PARTITION BY f.SUBJECTID
ORDER BY f.HANDLEDTIME DESC NULLS LAST) o
FROM API_APPLY_FLOW f, API_SUBJECT s
WHERE f.SUBJECTID = s.ID AND s.USERID = '1f151f1ede88483eabf598fa77828a36'
) WHERE o = 1;
至于执行效率，没有做过测试，不知道。
ps：desc nulls last表示将null值放到排序序列的最后面，因为Oracle默认在时间排序中，null为最大值，如果没有此句，则取出的值与预期不符。对于MySQL，因为暂时没用所以就没测试。
8、关于几个排序函数(rank() over、dense_rank()、row_number())之间的区别 :
rank() over：相同行的排序结果相同，但占用排序的位数，即排序的结果不连续；
dense_rank()：相同行的排序结果相同，不占用排序的位数，排序结果是连续的；
row_number()：不考虑相同的行，一律顺序排次，排序结果当然也是连续的；
9、查询指定的一段时期内，符合要求的记录的总数，要求按周进行统计:
SELECT
TO_CHAR(y.CREATE_TIME, 'yyyy') year,
TO_CHAR(y.CREATE_TIME, 'iw') week,
COUNT(0) COUNT
FROM WXF_YHXX y
WHERE y.CREATE_TIME >= to_date('2016-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
AND y.CREATE_TIME < to_date('2016-09-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
GROUP BY TO_CHAR(y.CREATE_TIME, 'iw'), TO_CHAR(y.CREATE_TIME, 'yyyy')
ORDER BY week DESC;
10、一行转多列并进行关联查询：
问题说明：现遇到，在查询主表时，有一列的字段类型为varchar，但是其所存的值，却是一个数组，格式为这样：'76,70,71,72,73,74,75,15'，其中每一个数字，都为另一个表中对应的id的值。由于数据量很大，如果通过代码循环来做的话，对数据库的查询次数会呈笛卡儿积的形式增加，效率大大降低。所以，怎么通过一条sql语句来实现一次性查询呢？
解决办法：oracle中有一个函数，叫做：REGEXP_*, 这类函数有四种形式，每一种的使用方法也不一样，介绍如下：
1、REGEXP_REPLACE()，参数有六个，具体例子参见 这个。
2、REGEXP_LIKE()，参数有三个，具体例子参见 这个。
3、REGEXP_INSTR()，参数有六个，参见 这个。
4、REGEXP_SUBSTR()，参数有五个，参见 这个。
现在，要针对性的解决问题了，具体SQL为：
WITH a AS (SELECT '/ABC/AA/AD/ABD/JI/CC/ALSKD/ALDKDJ' id FROM dual) SELECT regexp_substr(id, '[^/]+', 1, rownum) id FROM a CONNECT BY rownum <= length(regexp_replace(id, '[^/]+'));
实际的开发应用的是这个：
SELECT REGEXP_SUBSTR(t.ITEM_IDS, '[^,]+', 1, LEVEL, 'i') item FROM (SELECT * FROM WXF_JCJL_SBXX sme WHERE sme.ID = '5995') t CONNECT BY LEVEL <= LENGTH(ITEM_IDS) - LENGTH(REGEXP_REPLACE(ITEM_IDS, ',', '')) + 1;
说明：替换的字符为逗号（，），输出结果为：'76,70,71,72,73,74,75,15'这些全都拆开了。
11、小问题：分页查询
这是个最常见也最容易忽视的一个东西。分页的目的或作用是：一是为了使前端展示起来比较美观，二是提高了数据库的性能，减少了非必要数据的查询从而减少了资源的浪费。
至于分页实现的原理什么的，戳这里。
现在来说说如何使用SQL语句来实现分页。分页的技术实现有好多种，不同数据库标准下的实现是不同的，现只针对ORACLE数据库进行说明，其他的出门右转有百度，左转有google，请自便。
实现方式：继续戳，对，就这里。
12、CASE ... WHEN ... THEN ... WHEN ... THEN ... ELSE ... END
作为查询，有时候会遇到一些没有具体汉字属性的值，库里面存的只是一串数字或者其他乱七八糟的标识符号，这些东西要是传给前端，人家又不知道那什么意思，来回问来问去的也麻烦，还不如自己在SQL里就给定义好了省事。
然后问题来了，怎么进行自定义返回的参数的值。现有CASE语句，便可解决这个问题。
例子：
SELECT COMPANY_ID, STATE FROM WXF_JCJL ;
查询结果为：
其中STATE属性有两个值，分别为1和0，1代表“已经处理”，0代表“尚未处理”。
使用CASE语句进行处理，方式为：
SELECT COMPANY_ID, CASE STATE WHEN 1 THEN '已经处理' WHEN 0 THEN '尚未处理' ELSE '未知' END AS state FROM WXF_JCJL;
结果为：
这样看起来就好多了。
需要注意的是，当 列名 放在CASE之后时，WHEN里面的值的类型必须要与列的类型一致，如果想要不一致，则将列名放到WHEN里就行了，此时的CASE后面是没有东西的。
13、将多条名称重复, 但是个别字段不重复的记录, 合并为单条, 将重复的字段拼接为一行, 输出.
这里的例子是:
在table表中, companyName是companyAddress重复出现的列, name的值, 对于每个相同的companyName而言都是不同的, 现在要将companyName去重输出, name用\符号拼接起来组成一个列, 并按照timeset的倒序排列输出:
SELECT companyName, companyAddress, timeset, TRANSLATE(LTRIM(text, '/'), '*/', '*,') name FROM (SELECT ROW_NUMBER() OVER (PARTITION BY companyName ORDER BY companyName, lvl DESC) rn, companyName, companyAddress, timeset, text FROM (SELECT companyName, companyAddress, timeset, LEVEL lvl, SYS_CONNECT_BY_PATH(name, ' \ ') text FROM (SELECT companyName, companyAddress, name, timeset, ROW_NUMBER() OVER (PARTITION BY companyName ORDER BY companyName, name) x FROM tableORDER BY companyName, timeset, name) a CONNECT BY companyName = PRIOR companyName AND x - 1 = PRIOR x)) WHERE rn = 1 ORDER BY timeset DESC
