MySQL连接Join Group By和Group Concat

Question

我正在追踪连接到Web应用程序并在问题反馈中留下问题的用户。

用户

id  name
---------
1   Jim
2   Joe

由于用户可以从各种浏览器连接到应用并具有多个连接，因此我使用一个令牌表来跟踪用户连接:

令牌

id  userid
----------
1   1
2   1
3   2
4   2

那么用户提出问题：

问题

id  userid  pageno text
------------------------------------------------
1   1       1      'Whats going on here?'
2   1       2      'How are you?'
4   2       2      'Good and you?'

我的目标是输出以下内容

username  questions                               Questions Page 1        Questions Page 2
-----------------------------------------------------------------------------------
Jim       'Whats going on here?', 'How are you?'  'Whats going on here?'  'How are you?'
Joe       'Good and you?'                                                 'Good and you?'

现在我尝试直接查询用户表，但是我的数据库中有很多用户，所以为了提高性能，我需要查询令牌表而不是用户表，然后按userid分组，因为一个用户可以获得多个令牌。

虽然现在我按userid分组，但我不确定如何将不同的问题合并为一组。 以下是查询语句：

SELECT  u.id, u.name, q.text
FROM token t
LEFT JOIN user as u ON t.userid = u.id
LEFT JOIN question as q ON t.userid = q.userid
GROUP BY t.userid

我的问题是我只得到了第一个问题，尝试使用group concat却没有成功。

忘记添加了，如果能够动态完成这个任务将会很棒，因为页面数量可能会有很大的变化。你能帮忙吗？ 谢谢

- SammyDev

Answer 1

以最易理解和简单的方式获取 OP 所要求的确切结果的方法是将问题分成多个子查询，而不是一个单一的查询。

我创建了许多中间 VIEW 来获取最终结果。使用中间 VIEW 而不是中间 TABLE 的好处在于原始表中的任何更新也会导致 VIEW 中结果的更新。

子查询 1：

CREATE VIEW q AS
    -> SELECT userid,  
    -> GROUP_CONCAT(text ORDER BY userid SEPARATOR ", ") AS questions
    -> FROM Questions
    -> GROUP BY userid;
    
SELECT * FROM q;
+--------+----------------------------------------+
| userid | questions                              |
+--------+----------------------------------------+
|      1 | 'How are you?', 'Whats going on here?' |
|      2 | 'Good and you?'                        |
+--------+----------------------------------------+

子查询2：

CREATE VIEW q1 AS
    -> SELECT userid, text AS questions1
    -> FROM Questions
    -> WHERE pageno = 1;

SELECT * FROM q1;
+--------+------------------------+
| userid | questions1             |
+--------+------------------------+
|      1 | 'Whats going on here?' |
+--------+------------------------+

子查询3：

CREATE VIEW q2 AS
    -> SELECT userid, text AS questions2
    -> FROM Questions
    -> WHERE pageno = 2;
    
SELECT * FROM q2;
+--------+-----------------+
| userid | questions2      |
+--------+-----------------+
|      1 | 'How are you?'  |
|      2 | 'Good and you?' |
+--------+-----------------+

子查询 4：

CREATE VIEW ql AS
    -> SELECT u.id, q.questions
    -> FROM Users AS u
    -> LEFT OUTER JOIN
    -> q ON u.id = q.userid;
    
SELECT * FROM ql;
+----+----------------------------------------+
| id | questions                              |
+----+----------------------------------------+
|  1 | 'How are you?', 'Whats going on here?' |
|  2 | 'Good and you?'                        |
+----+----------------------------------------+

子查询 5：

CREATE VIEW q1l AS
    -> SELECT u.id, q1.questions1
    -> FROM Users AS u
    -> LEFT OUTER JOIN
    -> q1 ON u.id = q1.userid;

SELECT * FROM q1l;
+----+------------------------+
| id | questions1             |
+----+------------------------+
|  1 | 'Whats going on here?' |
|  2 | NULL                   |
+----+------------------------+

子查询 6：

CREATE VIEW q2l AS
    -> SELECT u.id, q2.questions2
    -> FROM Users AS u
    -> LEFT OUTER JOIN
    -> q2 ON u.id = q2.userid;

SELECT * FROM q2l;
+----+-----------------+
| id | questions2      |
+----+-----------------+
|  1 | 'How are you?'  |
|  2 | 'Good and you?' |
+----+-----------------+

子查询 7：

CREATE VIEW vt AS
    -> SELECT u.name, 
    -> ql.questions, 
    -> q1l.questions1, 
    -> q2l.questions2
    -> FROM 
    -> Users AS u
    -> INNER JOIN
    -> ql ON u.id = ql.id
    -> INNER JOIN
    -> q1l ON ql.id = q1l.id
    -> INNER JOIN
    -> q2l ON q1l.id = q2l.id;
    
SELECT * FROM vt;
+------+----------------------------------------+------------------------+-----------------+
| name | questions                              | questions1             | questions2      |
+------+----------------------------------------+------------------------+-----------------+
| Jim  | 'How are you?', 'Whats going on here?' | 'Whats going on here?' | 'How are you?'  |
| Joe  | 'Good and you?'                        | NULL                   | 'Good and you?' |
+------+----------------------------------------+------------------------+-----------------+

最终查询：

CREATE VIEW v AS
    -> SELECT name AS username,
    -> COALESCE(questions, "") AS questions,
    -> COALESCE(questions1, "") AS "Questions Page 1",
    -> COALESCE(questions2, "") AS "Questions Page 2"
    -> FROM vt;
    
SELECT * FROM v;
+----------+----------------------------------------+------------------------+------------------+
| username | questions                              | Questions Page 1       | Questions Page 2 |
+----------+----------------------------------------+------------------------+------------------+
| Jim      | 'How are you?', 'Whats going on here?' | 'Whats going on here?' | 'How are you?'   |
| Joe      | 'Good and you?'                        |                        | 'Good and you?'  |
+----------+----------------------------------------+------------------------+------------------+

- Payel Senapati

Answer 2

在尝试了这个GROUP_CONCAT(DISTINCT q.text) as question FROM之后，您得到了什么？

SELECT  u.id, u.name, GROUP_CONCAT(DISTINCT q.text) as question
FROM token t
LEFT JOIN user as u ON t.userid = u.id
LEFT JOIN question as q ON t.userid = q.userid
GROUP BY t.userid

- Pathik Vejani

---

@Sdev，这在我的本地环境中运行良好。我已经尝试过了。 - Pathik Vejani

谢谢，这个方法可行，但我已经编辑了帖子，因为我错过了原始问题的一部分，即按页码进行筛选。 - SammyDev

@SDev，“Question Page 1 Question Page 2”是什么？ - Pathik Vejani

你想将“问题页面1 问题页面2”作为列标题吗？ - Pathik Vejani

我已经编辑过这篇文章以提高其清晰度。列标题并不是那么重要，我无法实现的是在我的查询中按页数查询问题。 - SammyDev

Answer 3

查询应该是这样的：

SELECT temp.*, (SELECT GROUP_CONCAT(q.text) FROM questions q WHERE q.pageno = temp.pageno) as Pagewiseqtn FROM (SELECT  u.id, u.name, GROUP_CONCAT(DISTINCT q.text) as question, q.pageno
FROM tokens t
LEFT JOIN users as u ON t.userid = u.id
LEFT JOIN questions as q ON t.userid = q.userid
GROUP BY t.userid) as temp

输出：

- Pathik Vejani

---

谢谢Rick，但我不确定这是否是正确的数据输出：在这个例子中，Jim在第2页问了“你好吗？”，但它出现在第二行，也就是Joe的行。 - SammyDev

@SDev 我认为这可以通过编写代码实现。 - Pathik Vejani