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 9A 2 4B 3 6B 2 5C 6 7C 3 4
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 9A 2 4B 3 6B 2 5C 6 7C 3 4
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 会给面试官更多映象。mysql> select * from hard;
+------+------+------+
| qu | co | je |
+------+------+------+
| A | 1 | 3 |
| A | 2 | 4 |
| A | 4 | 2 |
| A | 6 | 9 |
| B | 1 | 4 |
| B | 2 | 5 |
| B | 3 | 6 |
| C | 3 | 4 |
| C | 6 | 7 |
+------+------+------+
9 rows in set (0.00 sec)mysql> 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;
+------+------+------+
| qu | co | je |
+------+------+------+
| A | 6 | 9 |
| A | 2 | 4 |
| B | 3 | 6 |
| B | 2 | 5 |
| C | 6 | 7 |
| C | 3 | 4 |
+------+------+------+
6 rows in set (0.00 sec)mysql>
+------+------+------+
| qu | co | je |
+------+------+------+
| A | 6 | 9 |
| A | 2 | 4 |
| B | 3 | 6 |
| B | 2 | 5 |
| C | 6 | 7 |
| C | 3 | 4 |
+------+------+------+
6 rows in set (0.00 sec)
+------+------+
| qu | je |
+------+------+
| A | 3 |
| A | 4 |
| A | 2 |
| A | 9 |
| B | 4 |
| B | 5 |
| B | 6 |
| C | 4 |
| C | 7 |
+------+------+
需求结果是+------+------+
| qu | je |
+------+------+
| A | 9 |
| A | 4 |
| B | 6 |
| B | 5 |
| C | 7 |
| C | 4 |
+------+------+
select * from hard h1
where 2>(select count(*) from hard where qu=h1.qu and je>h1.je)
order by 1,2 desc;