mysql用具有相同id的数据创建一行

puruo6ea 于 2021-06-19 发布在 Mysql

关注(0)|答案(3)|浏览(231)

所以，在检查了多个论坛和类似的问题，我没有设法找到一个正是我要找的。
我有两张table：

questions            answers
+-----+---------+    +------+------+---------+
| id_q| question|    | id_q | id_a | answer  |
+=====+=========+    +======+======+=========+
|  1  |question1|    |   1  |   1  | answer1 |
+-----+---------+    +------+------+---------+
|  2  |question2|    |   1  |   2  | answer2 |
+-----+---------+    +------+------+---------+
                     |   2  |   3  | answer3 |
                     +------+------+---------+
                     |   2  |   4  | answer4 |
                     +------+------+---------+

想得到这样的东西：

+-----+---------+---------+---------+
| id_q| question| answer  | answer2 |
+=====+=========+=========+=========+
|  1  |question1| answer1 | answer2 |
+-----+---------+---------+---------+
|  2  |question2| answer3 | answer4 |
+-----+---------+---------+---------+

已尝试：

SELECT questions.*,
GROUP_CONCAT(answers.answer SEPARATOR ' ; ') AS answers
FROM questions
LEFT OUTER JOIN answers ON questions.id_q = answers.id_q
GROUP BY questions.id_q

但这给了我：

+-----+---------+-------------------+
| id_q| question|       answers     |
+=====+=========+===================+
|  1  |question1| answer1 ; answer2 |
+-----+---------+-------------------+
|  2  |question2| answer3 ; answer4 |
+-----+---------+-------------------+

不知道是否有可能实现我所尝试的。有什么想法吗？
提前谢谢。

sql mysql

来源：https://stackoverflow.com/questions/53342326/my-sql-create-a-row-with-data-that-have-same-id

3条答案

按热度按时间

v7pvogib1#

SELECT  ID,QUES,ANS1,ANS2
FROM (
SELECT DISTINCT A.ID,A.QUES,A.ANS ANS1,B.ANS ANS2
,ROW_NUMBER() OVER (PARTITION BY A.ID ORDER BY A.ID ,A.ANS ,B.ANS) RN
 FROM (
SELECT ID,IDQ,QUES,ANS
 FROM MY_TEMP_TABLE1 TEM1
INNER JOIN MY_TEMP_TABLE TEM
ON TEM1.IDQ=TEM.ID
) A
INNER JOIN (
SELECT ID,IDQ,QUES,ANS
 FROM MY_TEMP_TABLE1 TEM1
INNER JOIN MY_TEMP_TABLE TEM
ON TEM1.IDQ=TEM.ID 
) B
ON A.ID=B.ID
WHERE A.ANS<>B.ANS
)
WHERE RN=1
;

赞(0）回复(0）举报 2021-06-20

vnjpjtjt2#

你可以用 correlated subqueries 以…的贡献 mod 功能：

SELECT q.id_q, q.question,
   ( SELECT a1.answer 
       FROM answers a1 
      WHERE a1.id_q = q.id_q 
        AND mod(a1.id_a,2) = 1 ) AS answer,
   ( SELECT a2.answer 
       FROM answers a2 
      WHERE a2.id_q = q.id_q 
        AND mod(a2.id_a,2) = 0 ) AS answer2
  FROM questions q
  LEFT OUTER JOIN answers a ON q.id_q = a.id_q
 GROUP BY q.id_q, q.question;

+-----+---------+---------+---------+
| id_q| question| answer  | answer2 |
+-----+---------+---------+---------+
|  1  |question1| answer1 | answer2 |
+-----+---------+---------+---------+
|  2  |question2| answer3 | answer4 |
+-----+---------+---------+---------+

rextester演示

赞(0）回复(0）举报 2021-06-20

vktxenjb3#

您只需自行加入答案表：

SELECT questions.id_q, questions.question, a.answer1, a.answer2 FROM questions
INNER JOIN 
(SELECT answers.id_q, answers.answer AS answer1, answers_1.answer AS answer2 
FROM answers INNER JOIN answers AS answers_1 
ON answers.id_q = answers_1.id_q AND answers.id_a < answers_1.id_a) AS a
ON a.id_q = questions.id_q

赞(0）回复(0）举报 2021-06-19

我来回答

mysql用具有相同id的数据创建一行

3条答案

相关问题

热门标签

最新问答