前置准备
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`
(`id` bigint(20) NOT NULL,`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '姓名',`relative_ids` json NOT NULL COMMENT '亲人',`friend_ids` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '朋友',`sex` int NOT NULL DEFAULT 0 COMMENT '性别 1:男,2:女',`object_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '对象id',PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDBCHARACTER SET = utf8mb4COLLATE = utf8mb4_general_ci COMMENT = '用户表';INSERT INTO `t_user` VALUES (1, '张伟', '[]', '3,4', 1, 5);
INSERT INTO `t_user` VALUES (2, '李明', '[4, 5]', '', 1, 0);
INSERT INTO `t_user` VALUES (3, '王强', '[]', '', 1, 4);
INSERT INTO `t_user` VALUES (4, '雨婷', '[]', '', 2, 3);
INSERT INTO `t_user` VALUES (5, '蕾蕾', '[]', '', 2, 1);数据分析
1、王强和雨婷、张伟和蕾蕾互为情侣
2、王强和雨婷都是张伟的朋友
3、雨婷和蕾蕾都是李明的亲人
需求实现
1、查询出“李明”的个人信息以及他的亲人信息
SELECTusr.id,usr.NAME,usr.relative_ids,(SELECTJSON_ARRAYAGG( JSON_OBJECT( 'id', relative.id, 'name', relative.NAME ) ) FROMt_user relative WHERErelative.id IN ( SELECT relative_ids.id FROM JSON_TABLE ( usr.relative_ids, '$[*]' COLUMNS ( id BIGINT PATH '$' ) ) AS relative_ids ) ) AS relative_infos,usr.friend_ids,usr.sex,usr.object_id
FROMt_user usr
WHEREid = 2
2、查询出“张伟”的个人信息以及他的朋友信息
SELECTusr.id,usr.NAME,usr.relative_ids,(SELECTJSON_ARRAYAGG( JSON_OBJECT( 'id', friend.id, 'name', friend.NAME ) ) FROMt_user friend WHEREFIND_IN_SET( friend.id, usr.friend_ids ) > 0 ) AS friend_infos,usr.friend_ids,usr.sex,usr.object_id
FROMt_user usr
WHEREid = 1
3、查询出“有对象”的个人信息以及他(她)的对象信息
SELECTusr.id,usr.NAME,usr.relative_ids,( SELECT JSON_OBJECT( 'id', object.id, 'name', object.NAME ) FROM t_user object WHERE object.id = usr.object_id ) AS object_info,usr.friend_ids,usr.sex,usr.object_id
FROMt_user usr
WHEREusr.object_id != 0
