多行作為 SQLite 中的列

我有這三個表：

學生桌

| _id | name      |
| --- | --------- |
| 1   | Student 1 |
| 2   | Student 2 |
| 3   | Student 3 |
| ... | ...       |

主題表

| _id | name      |
| --- | --------- |
| 1   | Subject 1 |
| 2   | Subject 2 |
| ... | ...       |

關係

| student | subject |
| ------- | ------- |
| 1       | 5       |
| 1       | 4       |
| 2       | 7       |
| 3       | 8       |

我想要的是進行如下查詢：

| student   | subject1  | subject2  |
| --------- | --------- | --------- |
| Student 1 | Subject 5 | Subject 4 |
| Student 2 | Subject 7 | NULL      |
| Student 3 | Subject 8 | NULL      |

每個學生最多有 1 或 2 個科目。

試過了

SELECT st.name AS student,
      su.name AS subject1,
      su2.name AS subject2
 FROM student AS st
      JOIN
      student_subjects AS ss ON ss.student = st._id
      JOIN
      subject AS su ON ss.subject = su._id
      JOIN
      subject AS su2 ON ss.subject = su2._id
GROUP BY st._id;

但結果是

| student   | subject1  | subject2  |
| --------- | --------- | --------- |
| Student 1 | Subject 5 | Subject 5 |
| Student 2 | Subject 7 | Subject 7 |
| Student 3 | Subject 8 | Subject 8 |

確保最多有兩個科目會讓生活變得更輕鬆：

select st.*, su1.*, su2.*
  from student st
  left outer join subject su1
    on su1.id = (select min(sub_id) from rel where stud_id = st.id)
  left outer join subject su2
    on su2.id = (select max(sub_id) from rel where stud_id = st.id)
    and su2.id > su1.id

這給了我們：

1|Student 1|4|Subject 4|5|Subject 5
2|Student 2|7|Subject 7||
3|Student 3|8|Subject 8||

引用自：https://dba.stackexchange.com/questions/200122