有一批商品id，求每一个商品id在product_third_code_mapping表中最大的发货码行数据

本文链接：https://blog.youkuaiyun.com/qq_38117037/article/details/120502979

建表语句

CREATE TABLE `product_third_code_mapping` (
  `id` bigint(20) NOT NULL COMMENT '主键id',
  `store_product_id` bigint(20) NOT NULL COMMENT '商品id',
  `third_product_code` varchar(50) DEFAULT NULL COMMENT '第三方商品编码',
  `source_channel` varchar(20) DEFAULT NULL COMMENT '',
  `stock_num` decimal(12,4) DEFAULT NULL COMMENT '库存数量',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `is_used` int(1) DEFAULT '0' COMMENT '是否正在使用 0:尚未使用;1:正在被使用',
  `is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否删除 0-未删除 ，1-已删除',
  `create_userid` bigint(20) DEFAULT NULL COMMENT '创建人ID',
  `create_username` varchar(20) DEFAULT NULL COMMENT '创建人姓名',
  `update_userid` bigint(20) DEFAULT NULL COMMENT '最后修改人ID',
  `update_username` varchar(20) DEFAULT NULL COMMENT '最后修改人姓名',
  PRIMARY KEY (`id`),
  KEY `idx_store_product_id` (`store_product_id`) USING BTREE,
  KEY `idx_third_channel` (`third_product_code`,`source_channel`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

新增数据

INSERT INTO `product`.`product_third_code_mapping`(`id`, `store_product_id`, `third_product_code`, `source_channel`, `stock_num`, `create_time`, `update_time`, `is_used`, `is_deleted`, `create_userid`, `create_username`, `update_userid`, `update_username`) VALUES (2106220002849726, 45782, 'CCL134004G', 'CKERP', 23.0000, '2021-06-22 16:31:15', '2021-09-27 09:30:02', 0, 0, NULL, NULL, NULL, NULL);
INSERT INTO `product`.`product_third_code_mapping`(`id`, `store_product_id`, `third_product_code`, `source_channel`, `stock_num`, `create_time`, `update_time`, `is_used`, `is_deleted`, `create_userid`, `create_username`, `update_userid`, `update_username`) VALUES (2106220002849727, 45782, 'CBG109005GBJ1', 'CKERP', 22.0000, '2021-06-22 16:31:15', '2021-09-27 09:29:41', 0, 0, NULL, NULL, NULL, NULL);
INSERT INTO `product`.`product_third_code_mapping`(`id`, `store_product_id`, `third_product_code`, `source_channel`, `stock_num`, `create_time`, `update_time`, `is_used`, `is_deleted`, `create_userid`, `create_username`, `update_userid`, `update_username`) VALUES (2106220002849728, 45782, 'BME081002C', 'CKERP', 1.0000, '2021-06-22 16:31:15', '2021-09-26 17:48:46', 0, 0, NULL, NULL, NULL, NULL);
INSERT INTO `product`.`product_third_code_mapping`(`id`, `store_product_id`, `third_product_code`, `source_channel`, `stock_num`, `create_time`, `update_time`, `is_used`, `is_deleted`, `create_userid`, `create_username`, `update_userid`, `update_username`) VALUES (2106220002923245, 45782, 'EZD006068G', 'CKERP', 1.0000, '2021-06-22 16:31:15', '2021-09-26 17:48:42', 1, 0, NULL, NULL, NULL, NULL);

INSERT INTO `product`.`product_third_code_mapping`(`id`, `store_product_id`, `third_product_code`, `source_channel`, `stock_num`, `create_time`, `update_time`, `is_used`, `is_deleted`, `create_userid`, `create_username`, `update_userid`, `update_username`) VALUES (2106220002849729, 40537, 'CBG109005G', 'CKERP', 136.0000, '2021-06-22 16:31:15', '2021-09-27 10:09:37', 1, 0, NULL, NULL, NULL, NULL);
INSERT INTO `product`.`product_third_code_mapping`(`id`, `store_product_id`, `third_product_code`, `source_channel`, `stock_num`, `create_time`, `update_time`, `is_used`, `is_deleted`, `create_userid`, `create_username`, `update_userid`, `update_username`) VALUES (2106220002849730, 40537, 'CBG109005GBJ1', 'CKERP', 0.0000, '2021-06-22 16:31:15', '2021-06-30 11:32:29', 0, 0, NULL, NULL, NULL, NULL);

store_product_id 可以有多个 third_product_code，每个third_product_code对应了自己的库存stock_num，现在需要根据store_product_id 查询出来最大 stock_num

SELECT
 stock_num,
 `store_product_id`,
 `third_product_code`,
 `source_channel`,
 `create_time`,
 `update_time`,
 `is_used` 
FROM
 (
 SELECT
  stock_num,
  `store_product_id`,
  `third_product_code`,
  `source_channel`,
  `create_time`,
  `update_time`,
  `is_used` 
 FROM
  product_third_code_mapping 
 WHERE
  is_deleted = 0 
  AND store_product_id IN ( 45782,40537) 
 HAVING
  stock_num != 0 
 ORDER BY
  stock_num DESC 
 ) AS temp 
GROUP BY
 temp.store_product_id