创建store表
DROP TABLE IF EXISTS `store`;
CREATE TABLE `store` (
`price` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`product` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`date` datetime(6) NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of store
-- ----------------------------
INSERT INTO `store` VALUES ('11.5', 'apple', '2018-12-24 17:53:05.000000');
INSERT INTO `store` VALUES ('11.5', 'apple', '2018-12-24 17:53:19.000000');
INSERT INTO `store` VALUES ('12', 'apple', '2018-12-24 17:53:29.000000');
INSERT INTO `store` VALUES ('13', 'banner', '2018-12-24 17:53:42.000000');
INSERT INTO `store` VALUES ('12', 'banner', '2018-12-24 17:53:54.000000');
INSERT INTO `store` VALUES ('12', 'banner', '2018-12-24 17:54:06.000000');
INSERT INTO `store` VALUES ('11.5', 'orange', '2018-12-24 17:54:19.000000');
INSERT INTO `store` VALUES ('12', 'orange', '2018-12-24 17:55:02.000000');
INSERT INTO `store` VALUES ('12', 'oranger', '2018-12-24 17:55:02.000000');
INSERT INTO `store` VALUES ('13', 'orange', '2018-12-24 17:55:02.000000');
INSERT INTO `store` VALUES ('13', 'orange', '2018-12-24 17:55:02.000000');
SET FOREIGN_KEY_CHECKS = 1;
distinct去重必须把'distinct'放在最前面。
语法:
SELECT DISTINCT 列名称 FROM 表名称
distinct多字段去重:
如果还想将date也查出来,但是又不想把date去重了。那么只用distinct就无法实现。这时候就可以用GROUP_CONCAT()+GROUP_BY
GROUP_CONCAT()+GROUP_BY通过group by按照price字段进行分组,然后用group_concat()函数将重复的字段组合装起来。
然后再将重复的price去重就行,
多字段去重:
这是未将product字段进行group by分组的查询结果
分组后最终得到想要的去重数据。