oracleÅÅÃû²éѯ - Oracle / »ù´¡ºÍ¹ÜÀí

ÎÒÓÐÕâôһ¸ö±í
Óû§ ÊÕÒæ ÍƼöÊý ³É¹¦ÂÊ£¨£¥£©
a 20 4 80
b 30 2 70



ÿÔÂÍƼö4´Î£¬ÈôµÍÓÚ4Ö»µÄÅÅÃû½«±»½µÒ»µµ¡£
ÅÅÃû¼ÆËã·½·¨Îª£ºÔÚÒԳɹ¦ÂÊ·Öµµ»ù´¡ÉÏÒÔ ÊÕÒæ´Ó¸ßµ½µÍ½øÐÐÅÅÃû¡£

ÇëÎÊÕâ¸öÈçºÎ²éѯ±È½ÏºÃÄØ£¿

---

select * from
(select * from table order by ³É¹¦ÂÊ desc,ÊÕÒæ desc)
where rownum<5;

---

SQL code:
select * from
(select * from table order by ³É¹¦ÂÊ desc,ÊÕÒæ desc)
where rownum<5;

---

ÒýÓÃ

ÎÒÓÐÕâôһ¸ö±í
Óû§ ÊÕÒæ ÍƼöÊý ³É¹¦ÂÊ£¨£¥£©
a 20 4 80
b 30 2 70



ÿÔÂÍƼö4´Î£¬ÈôµÍÓÚ4Ö»µÄÅÅÃû½«±»½µÒ»µµ¡£
ÅÅÃû¼ÆËã·½·¨Îª£ºÔÚÒԳɹ¦ÂÊ·Öµµ»ù´¡ÉÏÒÔ ÊÕÒæ´Ó¸ßµ½µÍ½øÐÐÅÅÃû¡£

ÇëÎÊÕâ¸öÈçºÎ²éѯ±È½ÏºÃÄØ£¿

SQL code:

--²»ÖªµÀÄãÒªµÄÊÇʲôÑùµÄ½á¹û
select Óû§,ÊÕÒæ,ÍƼöÊý,³É¹¦ÂÊ,rownum ÅÅÃû
from tb
order by ³É¹¦ÂÊ,ÊÕÒæ desc

---

°ïÂ¥Ö÷ ¶¥ÁË

---

ÒÔÉϵÄÓ¦¸ÃÊǶ¼Ã»ÓÐʵÏÖ Ð¡ÓÚ4£¬¾Í½µÒ»µ²µÄ¹¦ÄÜ

---

SQL code:
case when stock_num<4 and succ_rate >=70 then 2
when stock_num<4 and succ_rate >=60 then 3