MySQL：查詢所有使用者最後一條記錄

測試資料

DROP TABLE IF EXISTS `javakf`;
CREATE TABLE `javakf`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `time` datetime NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

INSERT INTO `javakf` VALUES (1, 'a', '2020-06-15 14:43:10');
INSERT INTO `javakf` VALUES (2, 'a', '2020-06-15 14:43:19');
INSERT INTO `javakf` VALUES (3, 'b', '2020-06-15 14:43:45');
INSERT INTO `javakf` VALUES (4, 'b', '2020-06-15 14:43:42');
INSERT INTO `javakf` VALUES (5, 'c', '2020-06-15 14:43:35');
           

取時間最大的資料

SELECT
	a.* 
FROM
	javakf a
	JOIN ( SELECT name, MAX( time ) time FROM javakf GROUP BY name ) b 
	ON 
		a.name= b.name
		AND a.time = b.time
           

SELECT
	a.* 
FROM
	javakf a,
	( SELECT name, max( time ) time FROM javakf GROUP BY NAME ) b 
WHERE
	a.name = b.name 
	AND a.time = b.time
           

SELECT
	* 
FROM
	javakf a,
	( SELECT max( time ) AS time FROM javakf GROUP BY name ) b 
WHERE
	a.time = b.time
           

SELECT
	a.* 
FROM
	javakf a 
WHERE
	EXISTS 
		( SELECT 1 FROM javakf b WHERE a.time > b.time AND a.NAME = b.NAME ) 
	OR 
	  ( SELECT	1 FROM javakf c WHERE a.NAME = c.NAME GROUP BY a.NAME HAVING count( c.NAME ) = 1 )
           

SELECT	a.* FROM ( SELECT * FROM javakf ORDER BY time DESC ) a GROUP BY name
           

取ID最大的資料

SELECT
	a.* 
FROM
	javakf a 
WHERE
	id IN ( SELECT MAX( id ) FROM javakf GROUP BY NAME );
           

SELECT	a.* FROM	( SELECT * FROM javakf ORDER BY id DESC ) a GROUP BY name
           

取時間不是最大的所有資料

插入測試資料

SELECT
	a.* 
FROM
	javakf a
	LEFT JOIN ( SELECT t.* FROM ( SELECT * FROM javakf ORDER BY time DESC ) t GROUP BY NAME ) b 
ON 
	a.id = b.id 
WHERE
	b.id IS NULL;