mysql中可以直接获取最大值所在列的任意字段
执行
select id,MAX(number) from test GROUP BY category
结果:
| id | MAX(number) |
| -- | -- |
| 1 | 15 |
| 2 | 23 |
但是PGSQL中查找的字段必须出现在GROUP BY中,
目前只能嵌套查询再查一次得到结果
总感觉有点怪怪的,是否有更好的实现方式呢?
mysql表
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`createdtime` date NULL DEFAULT NULL COMMENT '日期',
`id` int(11) NOT NULL AUTO_INCREMENT,
`category` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '品类',
`number` int(11) UNSIGNED NULL DEFAULT NULL COMMENT '数量',
`finalamount` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `test` VALUES ('2021-06-02', 1, 'can', 10, 500);
INSERT INTO `test` VALUES ('2021-06-02', 2, 'pet', 23, 680);
INSERT INTO `test` VALUES ('2021-06-02', 3, 'can', 13, 570);
INSERT INTO `test` VALUES ('2021-05-13', 4, 'can', 15, 590);
INSERT INTO `test` VALUES ('2021-05-18', 5, 'pet', 17, 530);
pgsql表
DROP TABLE IF EXISTS "public"."testmember";
CREATE TABLE "public"."testmember" (
"id" int4 NOT NULL DEFAULT nextval('testmember_id_seq'::regclass),
"name" varchar COLLATE "pg_catalog"."default",
"platstatus" int4,
"mnumber" int4
);
ALTER TABLE "public"."testmember" ADD CONSTRAINT "testmember_pkey" PRIMARY KEY ("id");
INSERT INTO "public"."testmember" VALUES (1, 'aa', 1, 5);
INSERT INTO "public"."testmember" VALUES (2, 'bb', 2, 6);
INSERT INTO "public"."testmember" VALUES (3, 'cc', 1, 5);
INSERT INTO "public"."testmember" VALUES (4, 'aa', 1, 8);