https://www.cnblogs.com/hjianguo/p/6041617.html
如
病人ID 入院日期 出院日期
00001 2016-09-01 2016-09-10
00001 2016-09-15 2016-09-30
计算病人00001 是否10天内再次入院
select b.patient_id, trunc(b.admission_date_time)-trunc(d.discharge_date_time) from
(select a.patient_id ,a.visit_id,a.admission_date_time,a.discharge_date_time from pat_visit a where a.admission_date_time<to_date('2016-09-30','yyyy-mm-dd')+1 and a.admission_date_time>=to_date('2016-07-01','yyyy-mm-dd')) b
left join
(select c.patient_id ,c.visit_id,c.admission_date_time,c.discharge_date_time from pat_visit c) d
on b.patient_id=d.patient_id and b.visit_id=d.visit_id+1
left join operation e on d.patient_id=e.patient_id and d.visit_id=e.visit_id
where trunc(b.admission_date_time)-trunc(d.discharge_date_time)<30 ;
ORA-00935: group function is nested too deeply
select s.sname, to_char(sum(t.amount), 'fm$999999.00')
from transactions t, salespeople s
where t.sid = s.sid
group by s.sname, t.year
having (t.year = 1997) and max(sum(t.amount));
I'm trying to print sales person name who had the highest total sell in 1997
A:
1.
select sname, amount
from (
select sname, amount, row_number() over(order by amount desc) rno
from (
select s.sname, sum(t.amount) amount
from transaction t, salespeople s
where t.sid = s.sid
and t.year = 1997
group by s.sname))
where rno = 1
2.
select s.sname, (case when sum(t.amount) > sum(t2.amount)
then sum(t.amount)
else sum(t2.amount)
end) "TOTAL"
from transactions t, salespeople s
where t.sid = s.sid
group by s.sname, t.year
having t.year = 1997
union
select s.sname, sum(t2.amount)
from transactions t2, salespeople s
where t2.sid = s.sid
group by s.sname, t2.year
having t2.year = 1997;