mysql - 如何在同一列上多次使用 SELECT 查询，SQL

Question

我正在尝试根据交叉表信息生成两列。具体来说，我试图选择在讨论论坛中提问的学生，并将他们放入“提问者”栏中，并选择回答问题的学生，并将他们放入“回答者”栏中。查询单独工作，但是当我按如下方式用逗号连接它们时，出现语法错误:

SELECT author_id AS questioner
WHERE post_type='question',
group_concat(DISTINCT author_id SEPARATOR " ") AS answerers
WHERE post_type='answer'
FROM students;

语法错误:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
group_concat(DISTINCT author_id SEPARATOR " ") AS answerers
FROM students
WHERE' at line 12

我如何得到一列提问的人和一列回答问题的人？我假设错误是由于误解了 SELECT 语法。

Answer 1

我知道这在 SQL Server 中有效，但你可以在 MySQL 中尝试

SELECT a.author_id AS questioner, b.author_id AS answerers
FROM students a, students b
WHERE a.post_type='question'
AND b.post_type='answer'