MySQL 最值查询

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50714
 Source Host           : 127.0.0.1
 Source Database       : test

 Target Server Type    : MySQL
 Target Server Version : 50714
 File Encoding         : utf-8

 Date: 03/07/2017 21:29:35 PM
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `test`
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键 Id',
  `key` varchar(45) NOT NULL COMMENT '键名',
  `val` varchar(45) NOT NULL COMMENT '键值',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8 COMMENT='测试表';

-- ----------------------------
--  Records of `test`
-- ----------------------------
BEGIN;
INSERT INTO `test` VALUES ('1', 'a', '10'), ('2', 'a', '11'), ('3', 'a', '12'), ('4', 'a', '13'), ('5', 'a', '14'), ('6', 'a', '15'), ('7', 'a', '16'), ('8', 'a', '17'), ('9', 'a', '18'), ('10', 'a', '19'), ('11', 'b', '20'), ('12', 'b', '21'), ('13', 'b', '22'), ('14', 'b', '23'), ('15', 'b', '24'), ('16', 'b', '25'), ('17', 'b', '26'), ('18', 'b', '27'), ('19', 'b', '28'), ('20', 'b', '29'), ('21', 'c', '30'), ('22', 'c', '31'), ('23', 'c', '32'), ('24', 'c', '33'), ('25', 'c', '34'), ('26', 'c', '35'), ('27', 'd', '40'), ('28', 'd', '41'), ('29', 'd', '42'), ('30', 'd', '43'), ('31', 'd', '44'), ('32', 'd', '45');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

1
2
3
4
5
6
7
8

SELECT
  a.`id`,
  a.`key`,
  MAX(a.`val`) AS val
FROM
  test.test AS a
GROUP BY
  a.`key`;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT
  a.`id`,
  a.`key`,
  a.`val`
FROM
  test.test AS a
WHERE
  a.`val` = (SELECT MAX(b.`val`)
             FROM test.test AS b
             WHERE b.`key` = a.`key`)
ORDER BY
  a.`key`;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT
  a.`id`,
  a.`key`,
  a.`val`
FROM
  test.test AS a
WHERE
  NOT EXISTS(SELECT 1
             FROM test.test AS b
             WHERE b.`key` = a.`key` AND b.`val` > a.`val`);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

SELECT
  a.`id`,
  a.`key`,
  a.`val`
FROM
  test.test AS a,
  (SELECT
     `key`,
     MAX(`val`) AS val
   FROM test.test
   GROUP BY `key`) AS b
WHERE
  a.`key` = b.`key`
  AND a.`val` = b.`val`
ORDER BY
  a.`key`;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

SELECT
  a.`id`,
  a.`key`,
  a.`val`
FROM
  test.test AS a
  INNER JOIN (SELECT
                `key`,
                MAX(`val`) AS `val`
              FROM test.test
              GROUP BY `key`) AS b ON b.`key` = a.`key`
                                      AND b.`val` = a.`val`
ORDER BY
  a.`key`;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT
  a.`id`,
  a.`key`,
  a.`val`
FROM
  test.test AS a
WHERE
  1 > (SELECT COUNT(*)
       FROM test.test AS b
       WHERE b.`key` = a.`key` AND b.`val` > a.`val`)
ORDER BY
  a.`key`;

1
2
3
4
5
6
7
8

SELECT
  a.`id`,
  a.`key`,
  MIN(a.`val`) AS val
FROM
  test.test AS a
GROUP BY
  a.`key`;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT
  a.`id`,
  a.`key`,
  a.`val`
FROM
  test.test AS a
WHERE
  a.`val` = (SELECT MIN(b.`val`)
             FROM test.test AS b
             WHERE b.`key` = a.`key`)
ORDER BY
  a.`key`;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT
  a.`id`,
  a.`key`,
  a.`val`
FROM
  test.test AS a
WHERE
  NOT EXISTS(SELECT 1
             FROM test.test AS b
             WHERE b.`key` = a.`key` AND b.`val` < a.`val`);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

SELECT
  a.`id`,
  a.`key`,
  a.`val`
FROM
  test.test AS a,
  (SELECT
     `key`,
     MIN(`val`) AS val
   FROM test.test
   GROUP BY `key`) AS b
WHERE
  a.`key` = b.`key`
  AND a.`val` = b.`val`
ORDER BY
  a.`key`;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

SELECT
  a.`id`,
  a.`key`,
  a.`val`
FROM
  test.test AS a
  INNER JOIN (SELECT
                `key`,
                MIN(`val`) AS val
              FROM test.test
              GROUP BY `key`) AS b ON b.`key` = a.`key`
                                      AND b.`val` = a.`val`
ORDER BY
  a.`key`;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT
  a.`id`,
  a.`key`,
  a.`val`
FROM
  test.test AS a
WHERE
  1 > (SELECT COUNT(*)
       FROM test.test AS b
       WHERE b.`key` = a.`key` AND b.`val` < a.`val`)
ORDER BY
  a.`key`;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT
  a.`id`,
  a.`key`,
  a.`val`
FROM
  test.test AS a
WHERE
  2 > (SELECT COUNT(*)
       FROM test.test AS b
       WHERE b.`key` = a.`key` AND b.`val` > a.`val`)
ORDER BY
  a.`key`;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

SELECT
  a.`id`,
  a.`key`,
  a.`val`
FROM
  test.test AS a
WHERE
  EXISTS(
      SELECT COUNT(*)
      FROM
        test.test AS b
      WHERE
        b.`key` = a.`key`
        AND b.`val` > a.`val`
      HAVING
        COUNT(*) < 2
  )
ORDER BY
  a.`key`;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

SELECT
  a.`id`,
  a.`key`,
  a.`val`
FROM
  test.test AS a
WHERE
  2 > (
    SELECT COUNT(*)
    FROM
      test.test AS b
    WHERE
      b.`key` = a.`key`
      AND b.`val` < a.`val`
  )
ORDER BY
  a.`key`;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

SELECT
  a.`id`,
  a.`key`,
  a.`val`
FROM
  test.test AS a
WHERE
  EXISTS(
      SELECT COUNT(*)
      FROM
        test.test AS b
      WHERE
        b.`key` = a.`key`
        AND b.`val` < a.`val`
      HAVING
        COUNT(*) < 2
  )
ORDER BY
  a.`key`;