Oracle面试题及答案整理

select fclass,max(fscore) from table1 group by fclass,fid

101a1001
101a1001
102a1002
102a1003
103a1004
104a1005
104a1006
105a1007
105a1007
105a1007

102a1002
102a1003
104a1005
104a1006

select t2.* from table1 t1, table1 t2 where t1.fid = t2.fid and t1.fno <> t2.fno;

(
Fempno varchar2(10) not null pk,
Fempname varchar2(20) not null,
Fage number not null,
Fsalary number not null
);

fsalary>9999 and fage > 35
fsalary>9999 and fage < 35
fsalary <9999 and fage > 35
fsalary <9999 and fage < 35

select sum(case when fsalary > 9999 and fage > 35
then 1
else 0end) as "fsalary>9999_fage>35",
sum(case when fsalary > 9999 and fage < 35
then 1
else 0
end) as "fsalary>9999_fage<35",
sum(case when fsalary < 9999 and fage > 35
then 1
else 0
end) as "fsalary<9999_fage>35",
sum(case when fsalary < 9999 and fage < 35
then 1
else 0
end) as "fsalary<9999_fage<35"
from empinfo;

MONTHS PERSON INCOME
---------- ---------- ----------200807 mantisXF 5000200806 mantisXF2 3500200806 mantisXF3 3000200805 mantisXF1 2000200805 mantisXF6 2200200804 mantisXF7 1800200803 8mantisXF 4000200802 9mantisXF 4200200802 10mantisXF 3300200801 11mantisXF 4600200809 11mantisXF 6800
11 rows selected
select months, max(incomes), max(prev_months), max(next_months)
from (select months,
incomes,
decode(lag(months) over(order by months),
to_char(add_months(to_date(months, 'yyyymm'), -1), 'yyyymm'), lag(incomes) over(order by months), 0) as prev_months, decode(lead(months) over(order by months), to_char(add_months(to_date(months, 'yyyymm'), 1), 'yyyymm'), lead(incomes) over(order by months), 0) as next_months from (select months, sum(income) as incomes from a group by months) aa) aaagroup by months;
MONTHS MAX(INCOMES) MAX(PREV_MONTHS) MAX(NEXT_MONTHS)---------- ------------ ---------------- ----------------200801 4600 0 7500200802 7500 4600 4000200803 4000 7500 1800200804 1800 4000 4200200805 4200 1800 6500200806 6500 4200 5000200807 5000 6500 0200809 6800 0 0

C1 c2
2005-01-01 1
2005-01-01 3
2005-01-02 5

select nvl(to_char(t02,'yyyy-mm-dd'),'合计'),sum(t01)from test
group by rollup(t02)