一个简单的SQL查询语句,但是……请各位大虾帮帮俺,谢谢。俺想统计一下结算(js)表里结算日期(jsrq)为2007年度,累计现金支付(xjzf)大于30000的人员情况,写的语句如下:
select * from js where sum(js.xjzf)>=30000 and to_char(js.jsrq,'yyyy')='2007';可是提示错误如下:
ORA-00934: 此处不允许使用分组函数请教各位大虾应该如何实现这个统计功能呢?这个SQL语句到底应该如何写呢?谢谢!在线盼答
select * from js where sum(js.xjzf)>=30000 and to_char(js.jsrq,'yyyy')='2007';可是提示错误如下:
ORA-00934: 此处不允许使用分组函数请教各位大虾应该如何实现这个统计功能呢?这个SQL语句到底应该如何写呢?谢谢!在线盼答
用了GROUP BY,就不能用select * 来查询
select 字段1,字段2
from js
where to_char(js.jsrq,'yyyy')='2007';
group by 字段1,字段2
having sum(js.xjzf)> =30000
select * from js a where a.user_no in
(select b.user_no
from js b
where to_char(js.jsrq,'yyyy')='2007'
group by b.user_no
having sum(js.xjzf)> =30000 )或者
select * from js a where a.user_no in
(select b.user_no
from js b
group by b.user_no
having sum(js.xjzf)> =30000 )
where to_char(a.jsrq,'yyyy')='2007'
select js.xm 姓名,js.xjzf 现金支付 from js
where to_char(js.jsrq,'yyyy')='2007'
group by js.xm,js.xjzf
having(js.xjzf)>=30000;
再把GROUP BY 里面的 ,JS.XJZF去掉看看
select *
from
(
select 人员ID,人员其它信息,sum(JS.XJZF) over(patition by 人员ID) xjzf
from js
where
to_char(js.jsrq,'yyyy')='2007' ) a
where a.xjzf>=30000
insert into aa
select 'lili ', '5000 ', '2006' from dual
union all
select 'lili','30000','2007' from dual
union all
select 'wangming','45000','2006' from dual
union all
select 'wangming','45000','2007' from dual
union all
select 'liping','45000','2007' from dual;
方法一:
select * from aa group by name,pay,yeardate having sum(pay)>=30000 and yeardate='2007'
方法二:
select * from aa where yeardate='2007' group by name,pay,yeardate having sum(pay)>=30000
测试结果:
1 lili 30000 2007
2 liping 45000 2007
3 wangming45000 2007