SQL> SELECT COUNT (r.rdj_grbh) rs, COUNT (DISTINCT f.bih_id) fs,
2 SUM (f.fcz_czmj) OVER (PARTITION BY f.bih_id) mj,
3 SUM (f.fcz_czjs) OVER (PARTITION BY f.bih_id) js
4 FROM sjcj_rydjbxx r, sjcj_fwdjbxx f
5 group by r.rzf_xzdxzqh;
SUM (f.fcz_czmj) OVER (PARTITION BY f.bih_id) mj,
*
第 2 行出现错误:
ORA-00979: 不是 GROUP BY 表达式
sjcj_rydjbxx r 中r.rdj_grbh 与sjcj_fwdjbxx f.bih_id 是一对多的关系求教!
SELECT COUNT (r.rdj_grbh) rs, COUNT (DISTINCT f.bih_id) fs,
SUM (f.fcz_czmj) OVER (PARTITION BY f.bih_id) mj,
SUM (f.fcz_czjs) OVER (PARTITION BY f.bih_id) js
FROM sjcj_rydjbxx r, sjcj_fwdjbxx f
所以分析函数不可能出现在用于得到select结果的group by 后面.
比如select empno,sum(sal)over(partition by deptno) from emp;
这个语句实际上相当于先执行select * from emp;
然后再对查询结果使用分析函数按deptno分组计算sal和,然后得到结果.
--有分析函数就可以了 去看看相关的文档
SQL> SELECT COUNT (r.rdj_grbh) rs, COUNT (DISTINCT f.bih_id) fs,
2 SUM (f.fcz_czmj) OVER (PARTITION BY f.bih_id) mj,
3 SUM (f.fcz_czjs) OVER (PARTITION BY f.bih_id) js
4 FROM sjcj_rydjbxx r, sjcj_fwdjbxx f
--有分析函数就可以了 去看看相关的文档
SQL> SELECT COUNT (r.rdj_grbh) rs, COUNT (DISTINCT f.bih_id) fs,
2 SUM (f.fcz_czmj) OVER (PARTITION BY f.bih_id) mj,
3 SUM (f.fcz_czjs) OVER (PARTITION BY f.bih_id) js
4 FROM sjcj_rydjbxx r, sjcj_fwdjbxx f