SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for `a` -- ---------------------------- DROP TABLE IF EXISTS `a`; CREATE TABLE `a` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(100) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- ---------------------------- -- Records of a -- ---------------------------- INSERT INTO `a` VALUES ('1', 'admin'); INSERT INTO `a` VALUES ('2', 'guest'); INSERT INTO `a` VALUES ('3', 'demo');
-- ---------------------------- -- Table structure for `b` -- ---------------------------- DROP TABLE IF EXISTS `b`; CREATE TABLE `b` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a_id` int(11) NOT NULL, `action` varchar(10) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- ---------------------------- -- Records of b -- ---------------------------- INSERT INTO `b` VALUES ('1', '1', 'login'); INSERT INTO `b` VALUES ('2', '1', 'insert'); INSERT INTO `b` VALUES ('3', '1', 'delete'); INSERT INTO `b` VALUES ('4', '3', 'auth');
#3
结果是什么？
#4
假设B中ID唯一
SELECT * FROM b b1 LEFT JOIN a b2 ON b1.a_id=b2.id
WHERE NOT EXISTS(SELECT 1 FROM b WHERE b1.a_id=a_id AND b1.id
#5
假设以B中ID作为操作顺序标准
#6
结果是按A表查询出所有用户的B记录。
#7
用你的数据，结果是什么
#8
1 admin 1 1 login
2 guest
3 demo 4 3 auth
#9
SELECT * FROM a b2 LEFT JOIN b b1 ON b1.a_id=b2.id
WHERE NOT EXISTS(SELECT 1 FROM b WHERE b1.a_id=a_id AND b1.id>id)
京公网安备 11010802041100号