这个表,数据如下:
mysql> SELECT * FROM t1;
+----+----------+-----+
| id | category | num |
+----+----------+-----+
| 1 | a | 1 |
| 2 | a | 2 |
| 3 | a | 3 |
| 4 | a | 4 |
| 5 | b | 5 |
| 6 | b | 1 |
| 7 | c | 0 |
| 8 | c | 9 |
| 9 | d | 0 |
+----+----------+-----+
需求要查询出每种category里面,num第二大的那条记录。比如应该返回:
+----+----------+-----+
| id | category | num |
+----+----------+-----+
| 3 | a | 3 |
| 6 | b | 1 |
| 7 | c | 0 |
+----+----------+-----+
由于mysql数据库比较弱,没有oracle里面的类似row_NUMBER orer()这样的高级分析函数。所以要实现这样的效果还是比较麻烦。
并且效率很差劲。不过还是可以实现的。下面来看看:
C:\>mysql -P3306Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.11-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.37 sec)
mysql> CREATE TABLE t1 (
-> id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> category CHAR(1) NOT NULL,
-> num INT NOT NULL DEFAULT 0
-> ) ;
Query OK, 0 rows affected (0.40 sec)
mysql> desc t1;
+----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| category | char(1) | NO | | NULL | |
| num | int(11) | NO | | 0 | |
+----------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> INSERT INTO t1 (category, num)
-> VALUES
-> ('a', 1),
-> ('a', 2),
-> ('a', 3),
-> ('a', 4),
-> ('b', 5),
-> ('b', 1),
-> ('c', 0),
-> ('c', 9),
-> ('d', 0) ;
Query OK, 9 rows affected (0.16 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t1;
+----+----------+-----+
| id | category | num |
+----+----------+-----+
| 1 | a | 1 |
| 2 | a | 2 |
| 3 | a | 3 |
| 4 | a | 4 |
| 5 | b | 5 |
| 6 | b | 1 |
| 7 | c | 0 |
| 8 | c | 9 |
| 9 | d | 0 |
+----+----------+-----+
9 rows in set (0.00 sec)
mysql>
测试数据有了,怎么返回我们要的效果呢?
mysql> SELECT * FROM t1;
+----+----------+-----+
| id | category | num |
+----+----------+-----+
| 1 | a | 1 |
| 2 | a | 2 |
| 3 | a | 3 |
| 4 | a | 4 |
| 5 | b | 5 |
| 6 | b | 1 |
| 7 | c | 0 |
| 8 | c | 9 |
| 9 | d | 0 |
+----+----------+-----+
9 rows in set (0.00 sec)
mysql> SELECT
-> t1.`id`,
-> t1.`category`,
-> t1.`num`,
-> (SELECT
-> COUNT(*)
-> FROM
-> t1 inner_t1
-> WHERE inner_t1.category = t1.`category`
-> AND inner_t1.num >= t1.`num`) AS ct
-> FROM
-> t1;
+----+----------+-----+------+
| id | category | num | ct |
+----+----------+-----+------+
| 1 | a | 1 | 4 |
| 2 | a | 2 | 3 |
| 3 | a | 3 | 2 |
| 4 | a | 4 | 1 |
| 5 | b | 5 | 1 |
| 6 | b | 1 | 2 |
| 7 | c | 0 | 2 |
| 8 | c | 9 | 1 |
| 9 | d | 0 | 1 |
+----+----------+-----+------+
9 rows in set (0.00 sec)
这个效率不行,对于每条记录都回去描述一次原表。再提取出ct=2的记录即可:
mysql> SELECT
-> ttmp_1.id,
-> ttmp_1.category,
-> ttmp_1.num
-> FROM
-> (SELECT
-> t1.`id`,
-> t1.`category`,
-> t1.`num`,
-> (SELECT
-> COUNT(*)
-> FROM
-> t1 inner_t1
-> WHERE inner_t1.category = t1.`category`
-> AND inner_t1.num >= t1.`num`) AS ct
-> FROM
-> t1) AS ttmp_1
-> WHERE ttmp_1.ct = 2
-> ORDER BY ttmp_1.category ASC
-> ;
+----+----------+-----+
| id | category | num |
+----+----------+-----+
| 3 | a | 3 |
| 6 | b | 1 |
| 7 | c | 0 |
+----+----------+-----+
3 rows in set (0.00 sec)
mysql>
完成。