你好,是这样的,我这有两张表是一对多的关系,现在需要合并成一张表且同一个人只有一条数据,我在网上找了个sql语言。
原表是这样子的
预计效果是这样子的
但是我实际上查找到的是这样子的
这是建表语句:SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for t_user
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`age` int(5) NULL DEFAULT NULL,
`hobby` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of t_user
-- ----------------------------
INSERT INTO `t_user` VALUES (1, '张三', 18, '读书');
INSERT INTO `t_user` VALUES (2, '张三', 18, '打游戏');
INSERT INTO `t_user` VALUES (3, '张三', 18, '写代码');
INSERT INTO `t_user` VALUES (4, '王五', 20, '吃饭');
INSERT INTO `t_user` VALUES (5, '王五', 20, '睡觉');
INSERT INTO `t_user` VALUES (6, '王五', 20, '打豆豆');
这是查询语句:set @name = '王五';
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'(select hobby from (SELECT @rownum:=@rownum+1 AS rownum, t_user.*
FROM (SELECT @rownum:=0) r, t_user where name="',@name,'") t where t.rownum = ',
t.rownum,
') AS ',
CONCAT('hobby',rownum)
)
) INTO @sql
FROM (SELECT @rownum:=@rownum+1 AS rownum, t_user.*
FROM (SELECT @rownum:=0) r, t_user where name=@name) t;
SET @sql = CONCAT('select distinct name,age, ', @sql, ' from t_user where name ="',@name,'"');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;