Mysql
如何從列中選擇多列?
我有一張結構表
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)
我考慮了兩種選擇:
- GROUP BY,它可以給我一個 CONCAT(TEXT_number_1,TEXT_number_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
。