ORACLE日常小问题

1.varchar2(20)

支持数字、英文字符长度：20

支持汉字长度：10

EX1: 字段“item1”定义长度varchar2(7)

可以尝试：’1234567‘ 插入到 item1字段，成功

    ‘一二三四五六七’ 插入到item1字段，报错：长度为14，最大值是7

这里的长度是14,14是如何得来的呢？

select lengthb('一二三四五六七') from dual;

执行结果：

如果，想要将一个字符串插入到某个字段（如果前7个字段都是英文字符，数字，就取前七个数字英文），该字段长度varchar2(7)，超过长度，默认为‘0100010’，在长度范围内就取原始值

select case when lengthb(nvl(substr('一二三四五六七',0,7),'0100010'))>7 then '0100010' else nvl(substr('一二三四五六七',0,7),'0100010') end from dual;
-- 运行结果：0100010

select case when lengthb(nvl(substr('一二',0,7),'0100010'))>7 then '0100010' else nvl(substr('一二',0,7),'0100010') end from dual;
-- 运行结果：一二

2.空字段null

创建两张测试表：

Create table test007(
       id varchar2(7),
       name varchar2(7)
);

Create table test008(
       id varchar2(7),
       name varchar2(7)
);

插入数据：

insert into test007 values('1','');
insert into test007 values('2','');

insert into test008 values('1','');
insert into test008 values('2','');

查询两张表中，完全一致的记录

SELECT * from test007 a ,test008 b where a.name=b.name and a.id=b.id;

执行结果：

查询id相等，name不等的数据：

SELECT * from test007 a ,test008 b where a.name<>b.name and a.id=b.id;

执行结果：

因此，在查询中，空字段既不会认为是相等，也不能简单地认为不等

若业务上认为都为空的数据表示相等，可将可能为空的字段默认为一个值进行：

SELECT * from test007 a ,test008 b where nvl(a.name,0)=nvl(b.name,0) and a.id=b.id;

执行结果：

或，使用下面的方法：

SELECT * from test007 a ,test008 b where ((a.name is null and b.name is null) or (a.name = b.name)) and a.id=b.id;

执行结果：

下面看下，同一张表空字段分组：

1.首先，向test007表中插入两条数据

insert into test007 values('3','非空');
insert into test007 values('4','');

 当前，test007表中name字段为空的记录有三条，name非空记录有一条，现根据name字段分组，查询相应记录条数

select name,count(id) from test007 group by name;

执行结果：

name：空记录3条，非空记录1条。name空字段默认相等，与上述逻辑有偏差，为什么！！！我不知道......有能帮忙解答的么?

只怪自己接触太浅....

3.排序order by

先看个SQL吧

select distinct (select  codename
          from ldcode
         where codetype = 'selltype'
           and code = (select selltype from lccont where contno = a.contno)) 销售方式,
       b.contno 保单号,
       b.prtno 投保单号
  from ljapayperson a
  left join lcpol b
    on a.polno = b.polno
 where b.salechnl = '08'
   and exists (select 1
          from lccont
         where contno = b.contno
           and signdate >= trunc(sysdate, 'mm'))
 order by (select codename
             from ldcode
            where codetype = 'selltype'
              and code =
                  (select selltype from lccont where contno = a.contno)),b.contno;

这是一个报表的简化SQL，现在要求按子查询的结果及其中一个表的字段值进行排序，执行结果：

提示：不是selected表达式，原因是，排序的子查询条件中a表的字段contno，没有在查询结果中展示，无法按该字段进行排序，即使a表与b表对应字段相等，解决方案：

1.将查询结果项 b.contno 改为 a.contno,排序条件中b.contno 改为 a.contno

2.将排序条件的子查询中a.contno 改为b.contno

以此类推：使用order by时，order by的条件一定是在查询结果中的其中一项

4.分组group by

分组group by，说起来与order by很像，以刚刚的test007为例：

SELECT id,name FROM test007 group by name;

执行结果：

原因：使用group by进行分组时，在查询结果中展示的数据，都必须在group by后出现，聚合函数除外

常见的聚合函数：

1.求最大值函数max()
2.求最小值函数min()
3.求平均值函数avg()
4.求记录数函数count()
5.求和函数sum()

不常用的聚合函数
6.求中位数函数median()
7.求标准差函数stddev()
8.求协方差函数variance()

修改SQL：

1.group by添加分组条件

SELECT id,name FROM test007 group by name,id;

查询结果：

2.查询结果添加聚合函数

select max(id),name from test007 group by name;

查询结果：

分组后再进行排序筛选数据，取各组的第一条数据，要如何处理？

    ROW_NUMBER() OVER(PARTITION BY 分组字段 ORDER BY 排序字段 DESC) 作为新表的字段行数

示例，在test007表中插入数据：

insert into test007 values('5','非空');
insert into test007 values('6','第三组');
insert into test007 values('7','第三组');
insert into test007 values('8','第四组');
insert into test007 values('9','第四组');
insert into test007 values('10','第四组');

当前test007表中数据如下：

现要按name分组，按id倒序排序，取各组的前两项id（请注意，id的类型是varchar2()，10比9小哦）

首先根据name分组，id倒序，并对每组的数据标注序号

select row_number() over(partition by name order by id desc),id,name from test007;

执行结果：

再对结果进行筛选

select * from (select row_number() over(partition by name order by id desc) as rn,id,name from test007) m where m.rn<3;

哦了！

这次先写这么多吧...