[oracle] count()函数使用错误字段导致的查询失败（聚合函数聚合字段与分组字段不一致）

一 错误的SQL语句（原始的，格式是直接从word中拷贝的，存在多个错误）：

select to_char(t.fbsj, 'YYYY') as a, count(t.fbsj) from (

select fbsj from ods_t_ky_lw

    where

      to_char(FBSJ, 'YYYY') <= to_char(sysdate, 'YYYY')

        and

        to_char(FBSJ, 'YYYY') >= to_char(sysdate, 'YYYY') - 7) t

group by a ;

 附：建表语句及数据

链接: https://pan.baidu.com/s/1Qhv0ajPtefdqXd2P2LVXdw 提取码: q4bb 

 

二 调试过程

1 调整一（可执行的）：

1.1 取出来子sql直接执行，发现不行，报错ora-00911：invalid character

1.2 使用的是navicat这个工具，按说关键字是应该有颜色提示的，除非关键字没有被正确是被正确识别到，想到可能是因为sql中间有些oracle不认可的不可见字符，可以把所有空白符都删掉，自行处理分隔，如下即ok（如果直接拷贝使用，可能放到navicat里面还是要重新编辑一下）：

select fbsj from ods_t_ky_lw where

to_char(FBSJ, 'YYYY') <= to_char(sysdate, 'YYYY')

and

to_char(FBSJ, 'YYYY') >= to_char(sysdate, 'YYYY') - 7

 

2 调整二（不可执行）

直接加上外层sql，无法执行，说明外层sql还是有问题，SQL及报错如下：

select to_char(t.fbsj, 'YYYY') as a, count(t.fbsj) from (

select fbsj from ods_t_ky_lw where

to_char(FBSJ, 'YYYY') <= to_char(sysdate, 'YYYY')

and

to_char(FBSJ, 'YYYY') >= to_char(sysdate, 'YYYY') - 7) t

group by a ;

报错：ORA-00904：“A”：invalid identifier

 

3 调整三（不可执行）

3.1 从上一个报错结合字体颜色，会误以为A是个oracle关键字，那就换一个试试（比如fb)，实际上还是不行，报错类似，只不过提示的字符变了：ORA-00904：“FB”：invalid identifier

 

3.2 看SQL中，字段名称有时用小写，有时用大写，是不是因为大小写混用导致？oracle中大小写混用时，需要对字段加上双引号，实际结果还是不行（其实分析到这里是陷入歧途了，因为如果因为大小写混用导致错误，第一次调整中就有大小写混用没报错，因此不应该是大小写混用的问题），SQL及报错如下：

select to_char(t.fbsj, 'YYYY') as fb, count(t.fbsj) from (

select fbsj from ods_t_ky_lw where

to_char(FBSJ, 'YYYY') <= to_char(sysdate, 'YYYY')

and

to_char(FBSJ, 'YYYY') >= to_char(sysdate, 'YYYY') - 7) t

group by fb ;

报错：ORA-00904：“fbsj”：invalid identifier

 

3.3 然后又去掉对fbsj字段的双引号，对fb加双引号，也还是不行，报错跟上面差不多，只不过是换成fb是无效标识符，ORA-00904：“fb”：invalid identifier。

 

3.4 然后对fb加单引号，报错如下：

select to_char(t.fbsj, 'YYYY') as fb, count(t.fbsj) from (

select fbsj from ods_t_ky_lw where

to_char(FBSJ, 'YYYY') <= to_char(sysdate, 'YYYY')

and

to_char(FBSJ, 'YYYY') >= to_char(sysdate, 'YYYY') - 7) t

group by fb ;

报错：ORA-00923：from keywords not found where expected

原因：查找网上资料，有一篇文章提到oracle的列别名不能用单引号也不能用数字。

 

整个“调整三”的分析基本没有作用，不过3.2的结果可以保留，就是将别名a替换成fb，在不确定a是否关键字的情况下，换一个肯定不是关键字的，确保后面的如果有正确的尝试，不会因此受影响。

 

4 调整（最终是想要的结果的）：

分析又回到了原点，分析一下语义（格式什么的已经没有其他可尝试的了）会发现：fbsj是一个date类型的数据，oracle中date类型的数据至少会是年月日（不做格式转换的话，就是年月日时分秒），不可能只是四位的年份，而查询语句中需要将查询出来的fbsj只取年份做聚合，然后在查询语句中显示年份和年月日时分秒的统计结果？！该怎么统计呢？按照年份分组，然后统计的是fbsj这一秒多少条数据/是下一秒的又有多少条，你让oracle怎么给你展示？分组字段和分组聚合函数使用的字段不一致！！明确了原因了之后接下来又有两种修改方向：

4.1 既然是count里面的字段不对，直接将其改掉的了：

4.1.1 修改一sql：

select to_char(t.fbsj, 'YYYY') as fb, count(fb) from (

select fbsj from ods_t_ky_lw where

to_char(FBSJ, 'YYYY') <= to_char(sysdate, 'YYYY')

and

to_char(FBSJ, 'YYYY') >= to_char(sysdate, 'YYYY') - 7) t

group by fb ;

报错：ORA-00904：“FB”：invalid identifier

原因：fb是在最外层select中定义的，count也是在这一层，oracle怎么边计算着fb，count就直接拿来用了，要是没计算完怎么办？所以不能在同一层或更内层使用别名。

4.1.2 修改二sql：

select to_char(t.fbsj, 'YYYY') as fb, count(to_char(t.fbsj, 'YYYY')) from (

select fbsj from ods_t_ky_lw where

to_char(FBSJ, 'YYYY') <= to_char(sysdate, 'YYYY')

and

to_char(FBSJ, 'YYYY') >= to_char(sysdate, 'YYYY') - 7) t

group by fb ;

报错：ORA-00904：“FB”：invalid identifier

原因：其实按照计算来说这么做应该没什么错误，但是oracle的sql语法解释器可能还是会认为count里面的东西跟group by后的内容不一致导致报错。

 

4.1.3 修改三sql：

select to_char(t.fbsj, 'YYYY') as fb, count(to_char(t.fbsj, 'YYYY') as fb) from (

select fbsj from ods_t_ky_lw where

to_char(FBSJ, 'YYYY') <= to_char(sysdate, 'YYYY')

and

to_char(FBSJ, 'YYYY') >= to_char(sysdate, 'YYYY') - 7) t

group by fb ;

报错：ORA-00907：missing right paranthesis

原因：oracle中的内置函数里面的参数，如果前后多空格或无空格都不影响执行，只要参数个数对（用英文逗号分隔）就行，所以不用考虑空格的事情，不是因为前后空格导致误判右括号缺失。

其实oracle中很多“ORA-00907：missing right paranthesis”右括号缺失是因为其他问题导致的误判（除非真正发现括号数量不成对），这种情况一般都是因为sql中存在语法上的不完整或顺序颠倒（可参考https://blog.youkuaiyun.com/gggwfn1982/article/details/81627827）。

 

4.2 还是根据语义来修改，实际上外层只会使用到FBSJ字段的年份，因此对这个字段的处理可以放在内层select语句中，如下即可：

select fb, count(fb) from (

select to_char(fbsj, 'YYYY') as fb from ods_t_ky_lw

where

to_char(FBSJ, 'YYYY') <= to_char(sysdate, 'YYYY')

and

to_char(FBSJ, 'YYYY') >= to_char(sysdate, 'YYYY') - 7) t

group by fb ;