时间戳

首页 > 解决方案 > 如何从下表中获取记录

mysql - 如何从下表中获取记录

问题描述

我有 3 张桌子

QUESTION table with below 2 properties
1. ID (serial)
2. Question (varchar)

ANSWER table with below 4 properties
1. ID (serial)
2. QuestionID (foreign key to table QUESTION)
3. StudentID (foreign key to table STUDENT)
4. ANSWER (varchar)
5. SubmitDateTime (datetime)

STUDENT table with below properties
1. ID (serial)
2. Name (varchar)

我只想在每个答案中显示每个学生的记录（每个学生一个记录）。如果学生没有给出任何问题的答案，它将显示为空白。

例如：

QUESTION TABLE

| ID   | QUESTION  |
|----- | --------- |
| 1    | A FOR?    |
| 2    | B FOR?    |
| 3    | C FOR?    |

ANSWER TABLE

| ID   | QuestionID  | StudentID  | ANSWER | SubmitDateTime |
|----- | ----------- |------------|--------|----------------|
| 1    | 1           | 1          |Apple   | something date |
| 2    | 1           | 2          |Ant     | something date |
| 3    | 2           | 1          |Book    | something date |
| 4    | 3           | 2          |Cat     | something date |

STUDENT TABLE

| ID   | NAME      |
|----- | --------- |
| 1    | Jhon      |
| 2    | Lily      |

预期记录

Result table

| ID   | NAME      | Answers              |
|----- | --------- | ---------------------|
| 1    | Jhon      | Apple,Book,<blank>   |
| 2    | Lily      | Ant,<blank>,Cat      |

“空白”表示不会显示任何记录，而是显示空格或连字符。

我的实现：

SELECT s.ID,s.Name,
GROUP_CONCAT(a.answer SEPARATOR ',') AS answers
FROM student AS s 
LEFT JOIN answer AS a ON a.studentID=s.ID 
WHERE a.submitdate BETWEEN '<somedate>' AND '<somedate>' 
GROUP BY s.ID ORDER BY a.ID ASC

它没有给我一个空白的答案。如何获得这些？

标签： mysql

解决方案

尝试这个：

SELECT sid, sname, GROUP_CONCAT(IFNULL(a.answer,'-') ORDER BY qid)
FROM 
(SELECT q.id AS qid, s.id AS sid, s.Name AS sname 
         FROM question q CROSS JOIN student s) qs
LEFT JOIN answer a ON qs.qid=a.QuestionID AND qs.sid=a.StudentID
GROUP BY sid, sname;

基本查询是一个CROSS JOIN介于question和student表之间的表，它将是一个子查询并给出如下结果：

+-----+-----+-------+
| qid | sid | sname |
+-----+-----+-------+
|  1  |  2  | Lily  |
|  1  |  1  | Jhon  |
|  2  |  2  | Lily  |
|  2  |  1  | Jhon  |
|  3  |  2  | Lily  |
|  3  |  1  | Jhon  |
+-----+-----+-------+

如您所见，每个学生都将与所有现有问题配对，无论他们在answer表中的答案记录如何。这将是LEFT JOINwithanswer表的参考。

演示小提琴

推荐阅读