给出两个表:
membres:
numero (主键)
nameEmprunts:
numero (主键)
membre (外键对应membres的numero)
creele (创建时间)要求使用pl/sql语句块显示最近10个月以来在emprunts表里面记录前三个最多的人的名字(name)以及前三个最少的人的名字.本人刚接触这一块, 不是很明白, 希望各位高手指点指点.
谢谢
membres:
numero (主键)
nameEmprunts:
numero (主键)
membre (外键对应membres的numero)
creele (创建时间)要求使用pl/sql语句块显示最近10个月以来在emprunts表里面记录前三个最多的人的名字(name)以及前三个最少的人的名字.本人刚接触这一块, 不是很明白, 希望各位高手指点指点.
谢谢
直接top-n分析函数解决
优先选择SQL,SQL搞不定再用PL/SQL,PL/SQL搞不定用其他的比如外部过程之类的用SQL解决好处多多
(SELECT membre ,COUNT(*) AS CNT
FROM Emprunts
GROUP BY membre)
--先取最多三个
SELECT name
FROM TEMP T,membres M
WHERE T.membre=M.numero
AND ROWNUM<=3
ORDER T.CNT ASC
UNION ALL
--先取最多三个
SELECT name
FROM TEMP T,membres M
WHERE T.membre=M.numero
AND ROWNUM<=3
ORDER T.CNT DESC
不考虑在第2个表中没有记录的,楼主的意思是只选有记录的,如果只选择6个,前3和后3,那么使用row_number,相同记录数的随便选一个
如果相同记录数的在前3和后3中,也要选上用dense_rank,看测试,一条语句还搞不定这玩意,你只要名字的话,把其他的去掉,可能前和后3中有重复的,如果你只要名字不要重复的名字,请distinctSQL> select * from membres;
NUMERO NAME
---------- ----------
1 aa
2 bb
3 cc
4 dd
5 ee
6 ff
7 gg
8 hh
9 ii
10 jj
10 rows selected
SQL> select * from Emprunts;
NUMERO MEMBRE CREELE
---------- ---------- -----------
1 1 2010/5/3
2 1 2009/8/1
3 1 2010/5/3
4 3 2010/5/3
5 3 2010/5/9
6 4 2010/5/4
7 4 2010/5/1
8 4 2010/5/2
9 5 2010/4/3
10 5 2010/5/3
11 6 2010/5/3
12 7 2010/5/5
13 8 2010/5/1
14 9 2010/5/7
15 10 2010/5/2
16 10 2010/5/4
17 10 2010/5/6
18 10 2010/5/5
18 rows selected
SQL>
SQL> select name,maxrn,minrn,cnt from(
2 select a.name,
3 row_number() over(order by count(a.name) desc) maxrn,
4 row_number() over(order by count(a.name) ) minrn,
5 count(*) cnt
6 from
7 membres a,Emprunts b where a.numero = b.membre
8 and b.creele>=add_months(trunc(sysdate,'dd'),-10)
9 group by a.name
10 ) where maxrn <4 or minrn<4;
NAME MAXRN MINRN CNT
---------- ---------- ---------- ----------
ff 9 1 1
hh 7 2 1
ii 6 3 1
dd 2 7 3
aa 3 8 3
jj 1 9 4
6 rows selected
SQL>
SQL> select name,maxrn,minrn,cnt from(
2 select a.name,
3 dense_rank() over(order by count(a.name) desc) maxrn,
4 dense_rank () over(order by count(a.name) ) minrn,
5 count(*) cnt
6 from
7 membres a,Emprunts b where a.numero = b.membre
8 and b.creele>=add_months(trunc(sysdate,'dd'),-10)
9 group by a.name
10 ) where maxrn <4 or minrn<4;
NAME MAXRN MINRN CNT
---------- ---------- ---------- ----------
ff 4 1 1
hh 4 1 1
ii 4 1 1
gg 4 1 1
cc 3 2 2
ee 3 2 2
dd 2 3 3
aa 2 3 3
jj 1 4 4
9 rows selected
select case when min>3 then max else min end rn,
MAX(CASE WHEN MIN<=3 THEN NAME END) MIN,
max(case when min<=3 then counts end) min_counts,
MAX(CASE WHEN MAX<=3 THEN NAME END) MAX,
MAX(CASE WHEN MAX<=3 THEN counts END) MAX_counts
from(
select row_number()over(order by count(1)) min,
row_number()OVER(ORDER BY count(1) DESC) MAX,
count(1) counts,
a.name
from membres a,emprunts b
where a.numero=b.membre
AND b.creele>add_months(SYSDATE,-10)
group by a.numero,a.name)
where min<=3 or max<=3
GROUP BY CASE WHEN MIN>3 THEN MAX ELSE MIN END
order by 1;
for cur in (the_sql_statement) loop dbms_output.put_line('他要什么呢?给他'||cur.....);
end loop;