MySQL 分组取最新的一条

1.MySQL 分组取最新的一条

2.MySQL not in not exists 

CREATE TABLE `test_dept`  (
  `deptid` int(11) NOT NULL,
  `deptname` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`deptid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;


INSERT INTO `test_dept` VALUES (180001, '总公司');
INSERT INTO `test_dept` VALUES (180002, '运维部');
INSERT INTO `test_dept` VALUES (180003, '开发部');



CREATE TABLE `test_cust`  (
  `custid` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '0' COMMENT '客户编号',
  `cname` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '客户名称',
  `deptid` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' COMMENT '所属部门',
  `updatetime` datetime(0) DEFAULT NULL COMMENT '最后修改时间'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `test_cust` VALUES ('10001', '张三', '180003', '2020-02-01 13:50:42');
INSERT INTO `test_cust` VALUES ('10001', '张三', '180003', '2020-03-24 13:50:48');
INSERT INTO `test_cust` VALUES ('10002', '李四', '666', '2020-03-10 14:02:29');



####分组后取最后修改的那一条
select * from test_cust tc
where not exists(select 1  from test_cust where custid=tc.custid and updatetime>tc.updatetime)
GROUP BY custid;


####in 和 exits 和分组后取最后修改的那一条
select * from test_cust 
	where test_cust.deptid not in (select deptid from test_dept where test_dept.deptid=test_cust.deptid);
select * from test_cust 
	where test_cust.deptid in (select deptid from test_dept where test_dept.deptid=test_cust.deptid);

select * from test_cust 
	where exists  (select deptid from test_dept where test_dept.deptid=test_cust.deptid);
select * from test_cust 
where not exists  (select deptid from test_dept where test_dept.deptid=test_cust.deptid);