mysql ÃæÊÔÌâ

CREATE TABLE hard (qu char (11) ,co char (11) ,je numeric(3, 0))



insert into hard values ('A','1',3)

insert into hard values ('A','2',4)

insert into hard values ('A','4',2)

insert into hard values ('A','6',9)

insert into hard values ('B','1',4)

insert into hard values ('B','2',5)

insert into hard values ('B','3',6)

insert into hard values ('C','3',4)

insert into hard values ('C','6',7)

ÒªÇó²éѯ³öÀ´µÄ½á¹ûÈçÏ£º



qu co je

----------- ----------- -----

A 6 9

A 2 4

B 3 6

B 2 5

C 6 7

C 3 4

---

Èç¹ûÊÇÎÒ£¬¾ÍÕâô»Ø´ðÃæÊÔ¡£

SQL code:
select *
from hard a
where co<je
and 2>(select count(*)
from hard
where qu=a.qu
and co<je
and co>a.co)
order by 1,2 desc


ÓÃorder by 1,2 ±ÈÓÃorder by qu,co »á¸øÃæÊÔ¹Ù¸ü¶àÓ³Ïó¡£

SQL code:
mysql>