# 一.题目与数据

| 表名: Potential | |
| HospitalName | Potential |
| HP1 | 1000 |
| HP2 | 2500 |
| HP3 | 2700 |
| HP4 | 3000 |
| HP5 | 50 |
| HP6 | 215 |
| HP7 | 2410 |
| HP8 | 1292 |
| HP9 | 1031 |
| HP10 | 951 |
| HP11 | 278 |
| HP12 | 13 |
| HP13 | 706 |
| HP14 | 180 |
| HP15 | 970 |
| HP16 | 572 |
| HP17 | 650 |
| HP18 | 1598 |
| HP19 | 790 |
| HP20 | 605 |
| HP21 | 302 |
| HP22 | 697 |
| HP23 | 1240 |
| HP24 | 191 |
| HP25 | 278 |
| HP26 | 795 |
| HP27 | 795 |
| HP28 | 145 |
| HP29 | 808 |
| HP30 | 198 |
| HP31 | 787 |
| HP32 | 918 |
| HP33 | 1603 |

## 二.需求解读

### 三.编写

``````CREATE DEFINER=`root`@`localhost` PROCEDURE `mark_decile`()
BEGIN

set @gp = (select sum(potential) from potential);

select a.hosname, a.potential, o.sump, a.decile
from potential a
left join
(SELECT p1.hosname, (select sum(p2.potential) from potential p2 where p2.potential >= p1.potential) sump FROM potential p1 order by potential desc) o
on o.hosname = a. hosname
order by potential desc;

END``````

(=￣ω￣=)··· 暂无内容！