计算每个类型按照某个字段值大小取前几行数据

计算每个类型按照某个字段值大小取前几行数据

插入数据

/*
 Navicat Premium Data Transfer

 Source Server         : 1-10.0.30.59
 Source Server Type    : MySQL
 Source Server Version : 50632
 Source Host           : 10.0.30.59:3306
 Source Schema         : fof

 Target Server Type    : MySQL
 Target Server Version : 50632
 File Encoding         : 65001

 Date: 02/07/2019 14:43:54
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for good
-- ----------------------------
DROP TABLE IF EXISTS `good`;
CREATE TABLE `good`  (
  `price` decimal(10, 2) NULL DEFAULT NULL,
  `id` int(255) NOT NULL AUTO_INCREMENT,
  `type` int(2) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 18 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of good
-- ----------------------------
INSERT INTO `good` VALUES (3.00, 1, 1);
INSERT INTO `good` VALUES (5.00, 2, 1);
INSERT INTO `good` VALUES (2.00, 3, 1);
INSERT INTO `good` VALUES (1.00, 4, 1);
INSERT INTO `good` VALUES (1.22, 5, 1);
INSERT INTO `good` VALUES (3.33, 6, 1);
INSERT INTO `good` VALUES (2.12, 7, 2);
INSERT INTO `good` VALUES (2.23, 8, 2);
INSERT INTO `good` VALUES (3.55, 9, 2);
INSERT INTO `good` VALUES (3.66, 10, 2);
INSERT INTO `good` VALUES (1.22, 11, 2);
INSERT INTO `good` VALUES (5.12, 12, 3);
INSERT INTO `good` VALUES (5.22, 13, 3);
INSERT INTO `good` VALUES (3.23, 14, 3);
INSERT INTO `good` VALUES (6.72, 15, 3);
INSERT INTO `good` VALUES (8.21, 16, 3);
INSERT INTO `good` VALUES (6.65, 17, 3);

SET FOREIGN_KEY_CHECKS = 1;

查询

--  子查询
SELECT g1.*
FROM `good` g1
WHERE
(SELECT count(*) FROM `good` g2 WHERE g1.type=g2.type AND price > g1.price )  <1
order BY g1.type,g1.price desc

--  自连接
SELECT a.*
FROM
`good` a ,`good` b
WHERE a.type=b.type
AND  b.price >= a.price
GROUP BY a.id,a.type,a.price
HAVING count(b.id)<=1
ORDER BY a.type,a.price desc

• 计算结果