MySql merged into a data, how to realize the multiple data points of box is it-CodePudding

Hello, is this, I have two pieces of this table is a one-to-many relationship, now need to merge into one table and the same person is only a data, I found a SQL language on the Internet,
This is the original table like

Expected effect is like that of

But I actually is this way to find the

This is built predicate sentence: SET NAMES utf8mb4;
The SET FOREIGN_KEY_CHECKS=0;

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
- Table structure for t_user
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
DROP TABLE IF the EXISTS ` t_user `;
The 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) the NULL DEFAULT NULL,
` hobby ` varchar (20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
PRIMARY KEY (` id `) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT CHARACTER SET==4 utf8 COLLATE=utf8_general_ci ROW_FORMAT=Compact;

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
- Records of t_user
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
INSERT INTO ` t_user ` VALUES (1, 'zhang SAN, 18,' read ');
INSERT INTO ` t_user ` VALUES (2, 'zhang SAN, 18,' game ');
INSERT INTO ` t_user ` VALUES (3, 'zhang SAN, 18,' writing code ');
INSERT INTO ` t_user ` VALUES (4, 'Cathy', 20, 'eat');
INSERT INTO ` t_user ` VALUES (5, 'Cathy', 20, 'sleep');
INSERT INTO ` t_user ` VALUES (6, 'Cathy', 20, 'play doug);

This is the query: set @ name='Cathy';
SELECT
GROUP_CONCAT (DISTINCT
CONCAT (
'(select hobby from (select @ rownum:=@ + 1 AS rownum, rownum t_user. *
The FROM (SELECT @ rownum:=0) r, t_user where name="' @ the name, '") t where t.r ownum='
T.r ownum,
') AS ',
CONCAT (' hobby 'rownum)
)
) INTO @ SQL
The FROM (SELECT @ rownum:=@ + 1 AS rownum, rownum t_user. *
The FROM (SELECT @ rownum:=0) r, t_user where name=@ name) t;
SET @ SQL=CONCAT (' the select distinct name, age, '@ SQL,' the from t_user where name="' @ the name, '"');
PREPARE STMT FROM @ SQL;
The EXECUTE STMT.
DEALLOCATE PREPARE STMT.

CodePudding user response:

Interestingly I split the SQL statements;

red box in the picture, each a SQL statement to search data,

But once combined to the second three data can't the

Ask bosses for what he did that is associated with mysql version,

CodePudding user response:

Each user variable @ rownum new name, don't repeat the use of a variable