我的 tmp 表中有此数据。如何获取包含所有 4 个不同库存地点的最新数据集。(该集合应仅包含 4 行)
这里的所有数据都来自 2018-12-20,但我不想在查询中使用日期,因为日期是可变的。
我该怎么做?谢谢
-- ----------------------------
-- Table structure for `OMENtmp`
-- ----------------------------
DROP TABLE IF EXISTS `OMENtmp`;
CREATE TABLE `OMENtmp` (
`id2` int(11) NOT NULL AUTO_INCREMENT,
`id` int(11) NOT NULL DEFAULT '0',
`stockplace` varchar(250) CHARACTER SET latin1 DEFAULT NULL,
`MCCSIDate` date DEFAULT NULL,
PRIMARY KEY (`id2`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of OMENtmp
-- ----------------------------
INSERT INTO `OMENtmp` VALUES ('1', '1825711', '%', '2018-12-21');
INSERT INTO `OMENtmp` VALUES ('2', '1824590', 'HK+TW+AX+KS+T', '2018-12-21');
INSERT INTO `OMENtmp` VALUES ('3', '1825710', '%', '2018-12-20');
INSERT INTO `OMENtmp` VALUES ('4', '1825136', 'DE+VI+PA+AS+BR+MC+MI+L+VX', '2018-12-20');
INSERT INTO `OMENtmp` VALUES ('5', '1824589', 'HK+TW+AX+KS+T', '2018-12-20');
INSERT INTO `OMENtmp` VALUES ('6', '1824039', 'US', '2018-12-20');
INSERT INTO `OMENtmp` VALUES ('7', '1825709', '%', '2018-12-19');
INSERT INTO `OMENtmp` VALUES ('8', '1825135', 'DE+VI+PA+AS+BR+MC+MI+L+VX', '2018-12-19');
INSERT INTO `OMENtmp` VALUES ('9', '1824588', 'HK+TW+AX+KS+T', '2018-12-19');
INSERT INTO `OMENtmp` VALUES ('10', '1824038', 'US', '2018-12-19');
答案1
我做的:
SELECT x.* from OMENtmp x join (select k.*,count(k.id2) as cc from OMENtmp k group by k.MCCSIDATE) p on p.MCCSIDate = x.MCCSIDAte where p.cc = 4 order by MCCSIDAte desc limit 4 ;
这是正确答案,也许它可以更简单!