oracle——SQL复习04

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 ;