1.oracle中group by 和order by同时存在时

关键点：order by 的栏位必须在group by 中有；
例如：select name from TABLE group by name,id order by id asc

2.Oracle数据库-where,group by,having,order by语句的执行顺序

select xxx_name,count(*) as counter from table_y where where_condition group by xxx_name having having_condition order by zzz 

当我们看到一个类似上述的包含了where,having， order by等关键字的sql时，我们要首先要知道其执行顺序是怎样的，才能判断出其所表达的含义；

下面列出其执行顺序：

1. 根据where子句选择行；
2. 根据group by 子句组合行；
3. 根据having子句筛选组；
4. 根据order by子句中的分组函数的结果对组进行排序，order by必须使用分组函数或者使用Group by子句中指定的列；

下面看一个例子：

select CategoryName,count(*),AVG(Rating) from BOOKSHELF where Rating>1 group by CategoryName having CategoryName like 'A%' order by count(*) desc 

我们现在知道，其执行顺序如下：

1.基于Where Rating>1 筛选出符合条件的行；
2.基于group by CategoryName 对筛选的结果进行分组；
3.为每个CategoryName组计算Count(*)
4. 基于having CategoryName like ‘A%’留下符合条件的组
5. 根据order by 的条件对剩下的行组进行排序，sql中的count(*)也是分组函数

oracle日期时间型timestamp相关转换

1、获取系统时间的语句(ssxff6获取小数点后面六位)

select sysdate,systimestamp,to_char(systimestamp,'yyyymmdd hh24:mi:ssxff6'),'yyyymmdd hh24:mi:ss.ff6') from dual;

2、字符型转成timestamp

select to_timestamp('2011-09-14 12:52:42.123456789','syyyy-mm-dd hh24:mi:ss.ff') from dual;

3、timestamp转成date型

select cast(to_timestamp('2011-09-14 12:52:42.123456789','syyyy-mm-dd hh24:mi:ss.ff') as date) timestamp_to_date from dual;

4、date型转成timestamp

select cast(sysdate as timestamp) date_to_timestamp from dual;

5、两date的日期相减得出的是天数，而两timestamp的日期相减得出的是完整的年月日时分秒小数秒

select sysdate-sysdate,systimestamp-systimestamp from dual;

select extract(day from inter) * 24 * 60 * 60 + extract(hour from inter) * 60 * 60 + extract(minute from inter) * 60 + extract(second from inter) "seconds" from ( select to_timestamp('2011-09-14 12:34:23.281000000','yyyy-mm-dd hh24:mi:ss.ff') - to_timestamp('2011-09-14 12:34:22.984000000','yyyy-mm-dd hh24:mi:ss.ff') inter from dual );

select extract(second from to_timestamp('2011-09-14 12:34:23.281000000','yyyy-mm-dd hh24:mi:ss.ff'))-extract(second from to_timestamp('2011-09-14 12:34:22.984000000','yyyy-mm-dd hh24:mi:ss.ff')) from dual;

**注：所以，timestamp要算出两日期间隔了多少秒，要用函数转换一下。
to_char函数支持date和timestamp,但是trunc却不支持TIMESTAMP数据类型。**

sql的where执行顺序

1 MysqL 从左到右.

一个原则，排除越多的条件放到第一个

例子：抄的。

SELECT … WHERE p.languages_id = 1 AND m.languages_id = 1 AND c.languages_id = 1 AND t.languages_id = 1 AND p.products_id IN (472,474)

这样查询需要20多秒，虽然在各个字段上都建立了索引。用分析Explain sql一分析，发现在第一次分析过程中就返回了几万条数据：

WHERE p.languages_id = 1 ，然后再依次根据条件，缩小范围。

而我改变一下WHERE 字段的位置之后，速度就有了明显地提高：

WHERE p.products_id IN (472,474) AND
p.languages_id = 1 AND m.languages_id = 1 AND c.languages_id = 1 AND t.languages_id = 1

这样，第一次的条件是p.products_id IN (472,474)，它返回的结果只有不到10条，接下来还要根据其它的条件来过滤，自然在速度上有了较大的提升。

2 ORCAL

从右到左

sql中查询类型为int的字段，返回null的异常

项目中用mybatis3.x，用SQL查询某个表类型为int的字段，那个表是空表，没有数据，结果抛异常了，原因是在对象中的属性为int，空表时,sql查询出来的是NULL，结果赋值时抛异常了。

org.apache.ibatis.binding.BindingException: Mapper method 'getSort' (xx.yy.cc.DxxDAO) attempted to return null from a method with a primitive return type (int).

抛这样的异常。

解决这个问题，是当查出来为NULL时，结一个默认值，如：0。

MysqL：

SELECT IFNULL(MAX(SORT),0)AS sort FROM web_d_info

oracle:

SELECT nvl(MAX(SORT),0) as sort FROM web_d_info

Oracle的NVL函数用法

从两个表达式返回一个非 null 值。

语法

NVL(eExpression1,eExpression2)

参数

eExpression1,eExpression2

如果 eExpression1 的计算结果为 null 值，则 NVL( ) 返回 eExpression2。如果 eExpression1 的计算结果不是 null 值，则返回 eExpression1。eExpression1 和 eExpression2 可以是任意一种数据类型。如果 eExpression1 与 eExpression2 的结果皆为 null 值，则 NVL( ) 返回NULL.。

返回值类型

字符型、日期型、日期时间型、数值型、货币型、逻辑型或 null 值

说明

在不支持 null 值或 null 值无关紧要的情况下，可以使用 NVL( ) 来移去计算或操作中的 null 值。

select nvl(a.name,'空得') as name from student a join school b on a.ID=b.ID

注意：两个参数得类型要匹配

SELECT T.d_fdate,T.vc_zhcode,Nvl(SUM(T.f_fzqsz),0)   f_price_b,Nvl(SUM(T.f_fzqcb),0)   f_cost_b,Nvl(SUM(T.f_fgz_zz),0)  f_gz_b,Nvl(SUM(T.f_fyzqsz),0)  f_price_Y,Nvl(SUM(T.f_fyzqcb),0)  f_cost_Y,Nvl(SUM(T.f_fygz_zz),0) f_gz_Y,T.vc_source,SYSDATE  d_updatetime
FROM   gz_fund_gzb T

比如这样的判断就很重要啦，因为你不知道哪一行是 is not null 的，也不知道接下来是否要对这个单元格进行运算操作，因此，不能给列填 null，就给它一个 0 ，便于查看，也便于运算。

oracle order by 处理null值

适用情况：
oracle 对查询结果进行排序时，被排序的栏位存在null值，且要指定NULL值排在最前面或者最后面

关键字：Nulls First；Nulls Last

默认情况：null 默认为最大值（即：asc 升序<小–>大>，null值排在最后；desc 降序<大–>小>，null值排在最前面）

指定：
1. Oracle Order by支持的语法
2. 指定Nulls first 则表示null值的记录将排在最前(不管是asc 还是desc)
3. 指定Nulls last 则表示null值的记录将排在最后(不管是asc 还是desc)
语法举例：(Table：Tab_A 有部分空值的栏位Col_A)

select * from Tab_A order by Tab_A. Col_A (asc/desc) nulls first ------>null 值排在最前面 select * from Tab_A order by Tab_A. Col_A (asc/desc) nulls last ------>null 值排在最后面

其他方法：
在order by 的时候，用Nvl、NVL2 、Decode、case .....when....end;等函数对栏位的null值进行处理
例如：

select * from Tab_A order by NVL（Tab_A. Col_A,'abc' ） (asc/desc) ;

Oracle使用order by排序关于null值处理

select * from dual order by age desc nulls last select * from test order by age asc nulls first

sqlserver 认为 null 最小。

升序排列：null 值默认排在最前。

要想排后面，则：order by case when col is null then 1 else 0 end,col

降序排列：null 值默认排在最后。

要想排在前面，则：order by case when col is null then 0 else 1 end,col desc

解决oracle语句中 含数字的字符串按数字排序问题

例如：

普通排序利用：order by 字段名 ASC

但是遇到有中文而且类型是varchar类型的结果就是这样

政采代(甲)字第0298号

政采代(甲)字第0421号

政采代(甲)字第1098号

政采代(甲)字第1267号(延续)

政采代(甲)字第1179号(延续)

但是我们要的结果应该是这样：

政采代(甲)字第0298号

政采代(甲)字第0421号

政采代(甲)字第1098号

政采代(甲)字第1179号(延续)

政采代(甲)字第1267号(延续)

解决办法：
利用oracle函数及正则表达式进行排序

order by to_number(regexp_substr(字段名,'[0-9]*[0-9]',1))