如何從列中選擇多列？

我有一張結構表

book_id int(11),
section_id int(11),
note_id int(11),
item_number int(11),
text varchar(255),
UNIQUE(book_id,section_id,note_id,item_number)

item_number 只能是 1 或 2。

我怎樣才能SELECT query為 item_number 1 和 2 分別獲取文本book_id,section_id,note_id

SELECT book_id, select_id, note_id, TEXT_number_1, TEXT_number_2

TEXT_number_1 = text (WHERE item_number=1)
TEXT_number_2 = text (WHERE item_number=2)

我考慮了兩種選擇：

1. GROUP BY，它可以給我一個 CONCAT(TEXT_number_1,TEXT_number_2)
2. JOIN，我不確定什麼是最好的查詢。

SELECT t0.book_id, t0.select_id, t0.note_id, t1.TEXT_number_1, t2.TEXT_number_2
FROM (SELECT DISTINCT book_id, select_id, note_id
     FROM `table`) t0
LEFT JOIN `table` t1 ON t0.book_id=t1.book_id
                    AND t1.item_number=1   
                 /* AND t0.select_id=t1.select_id 
                    AND t0.note_id=t1.note_id */
LEFT JOIN `table` t2 ON t0.book_id=t2.book_id 
                    AND t2.item_number=2   
                 /* AND t0.select_id=t2.select_id 
                    AND t0.note_id=t2.note_id */

使用GROUP_CONCAT，但一定要增加group_concat_max_len。

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