select t.sid,count(1) rnt from t_score t group by t.sid ;
select t.sid, round(avg(nvl(t.grade, 0)), 2) avg_grade
from t_score t
where t.sid in (select t.sid
from (select t.sid, count(1) rnt
from t_score t
where t.grade <= 59
group by t.sid) t
where rnt >= 2)
group by t.sid;
select t.sid, round(avg(nvl(t.grade, 0)), 2) avg_grade
from t_score t
where t.sid in (select t.sid
from t_score t
where t.grade <= 59
group by t.sid
having count(1) >= 2)
group by t.sid;
select * from t_score t ;
--level函数
select level from dual connect by level <= 7 ;
/*与时间粒度相关的 时间 函数*/
--获取当前时间
select sysdate from dual;
--获取年月日
select trunc(sysdate) from dual;
select to_char(sysdate, 'YYYYMMDD') from dual ;
--获取时分秒
select to_char(sysdate,'hh24:mi:ss') from dual;
--单个获取年月日时分秒
select sysdate from dual;
select to_char(sysdate, 'YYYY') from dual ;
select to_char(sysdate, 'MM') from dual ;
select to_char(sysdate, 'DD') from dual ;
select to_char(sysdate, 'hh24') from dual ;
select to_char(sysdate, 'mi') from dual ;
select to_char(sysdate, 'ss') from dual ;
--返回季度 1 2 3 4
select to_char(sysdate, 'Q') from dual;
--返回本月第几周
SELECT TO_CHAR(SYSDATE, 'W') FROM DUAL;
--DD 当月第几天
SELECT TO_CHAR(SYSDATE, 'DD') FROM DUAL;
--D 周内第几天
SELECT TO_CHAR(SYSDATE, 'D') FROM DUAL;
-- 周几
SELECT TO_CHAR(SYSDATE, 'DY') FROM DUAL;
--下周几的时间
select next_day(sysdate,'星期四') from dual ;
--返回两个时间的年代数
select to_char(sysdate,'YYYY') - to_char(t.hiredate,'YYYY') year from emp t where t.empno = 7369 ;
--返回两个时间精确的年数
select months_between(sysdate,t.hiredate)/12 year from emp t where t.empno = 7369 ;
--返回两个时间的精确月份数
select months_between(sysdate,t.hiredate) month from emp t where t.empno = 7369 ;
--返回两个时间的精确天数
select trunc(sysdate - t.hiredate) day from emp t where t.empno = 7369 ;
oracle——SQL复习04
最新推荐文章于 2016-04-14 18:11:16 发布