mysql - 从多行中获取数据到一行
问题描述
所以，我有两张桌子，
在第一个中，我有一些与第二个表相关的数据。
这是第一个表的结构。
现在第二个表如下。
第一个表有一个问题列表，在第二个表中有多个值。
我写了这个查询，但它没有给我想要的结果。另外我想我的查询不是最佳的。
SELECT a.programming_question_bank_id AS id, a.programming_question_bank_questions_text AS question, a.programming_questions_bank_skill_set AS skill_set, a.programming_questions_bank_topic_id AS topic_id, b.programming_test_i_o_input AS stdInput, b.programming_test_i_o_output AS stdOutput FROM (
SELECT programming_question_bank_id, programming_question_bank_questions_text, programming_questions_bank_skill_set, programming_questions_bank_topic_id
FROM client_trials.programming_test_question_bank WHERE programming_question_bank_id = 1)a
INNER JOIN
(SELECT programming_test_i_o_question_id, programming_test_i_o_input, programming_test_i_o_output
FROM client_trials.programming_test_i_o WHERE programming_test_i_o_question_id = 1
)b ON a.programming_question_bank_id = b.programming_test_i_o_question_id
我得到的输出如下：
但我想要这样的输出：
+----+---------------+-----------+----------+----------------+----------------+----------------+-----------------+-----------------+-----------------+
| id | question | skill_set | topic_id | stdInput 1 | stdInput 2 | stdInput 3 | stdOutput 1 | stdInput 2 | stdInput 3 |
+----+---------------+-----------+----------+----------------+----------------+----------------+-----------------+-----------------+-----------------+
| 1 | Test Question | java-1 | 1 | test 1 input 1 | test 1 input 2 | test 1 input 3 | test 1 output 1 | test 1 output 2 | test 1 output 3 |
+----+---------------+-----------+----------+----------------+----------------+----------------+-----------------+-----------------+-----------------+
另外，我想写一个存储过程，但我不明白从哪里开始。
任何帮助表示赞赏。
用于创建表的 SQL：
CREATE TABLE `programming_test_question_bank` (
`programming_question_bank_id` int(11) NOT NULL AUTO_INCREMENT,
`creator_id` varchar(255) NOT NULL,
`programming_question_bank_questions_text` mediumtext NOT NULL,
`programming_questions_bank_standard_input` varchar(255) NOT NULL,
`programming_questions_bank_skill_set` varchar(255) NOT NULL,
`programming_questions_bank_topic_id` int(11) NOT NULL,
`programming_questions_bank_standard_output` varchar(255) NOT NULL,
`programming_questions_bank_created_at` datetime NOT NULL DEFAULT current_timestamp(),
`programming_questions_bank_updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`programming_question_bank_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='All Programming questions should be added here'
CREATE TABLE `programming_test_i_o` (
`programming_test_i_o_id` int(11) NOT NULL AUTO_INCREMENT,
`programming_test_i_o_question_id` int(11) NOT NULL,
`programming_test_i_o_input` varchar(255) NOT NULL,
`programming_test_i_o_output` varchar(255) NOT NULL,
`programming_test_i_o_created_at` datetime NOT NULL DEFAULT current_timestamp(),
`programming_test_i_o_updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`programming_test_i_o_id`),
KEY `programming_test_i_o_FK` (`programming_test_i_o_question_id`),
CONSTRAINT `programming_test_i_o_FK` FOREIGN KEY (`programming_test_i_o_question_id`) REFERENCES `programming_test_question_bank` (`programming_question_bank_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4
插入语句
INSERT INTO client_trials.programming_test_question_bank
(creator_id, programming_question_bank_questions_text, programming_questions_bank_standard_input, programming_questions_bank_skill_set, programming_questions_bank_topic_id, programming_questions_bank_standard_output, programming_questions_bank_created_at, programming_questions_bank_updated_at)
VALUES('1', 'Test Question - 1', '1,2', 'java-1', 1, '1,2', current_timestamp(), current_timestamp());
INSERT INTO client_trials.programming_test_i_o
(programming_test_i_o_question_id, programming_test_i_o_input, programming_test_i_o_output, programming_test_i_o_created_at, programming_test_i_o_updated_at)
VALUES(2, 'test 2 input 3', 'test 2 output 3', current_timestamp(), current_timestamp());