MySQL将2行合并为一列

Question

我有两张这样的桌子：

Item
-------------------------------------
|ID | ImageURLID | BannerImageURLID |
-------------------------------------
| 1 |     1      |        2         |
| 2 |     3      |        4         |
| 3 |     5      |        6         |
-------------------------------------

ImageURL
--------------------------------------
|ID |   iOSURL    |   iOSRetinaURL   |
--------------------------------------
| 1 | www.test.at |   www.testR.at   |
| 2 | www.bann.at |   www.bannR.at   |
| 3 | www.test.at |   www.testR.at   |
| 4 | www.bann.at |   www.bannR.at   |
| 5 | www.test.at |   www.testR.at   |
| 6 | www.bann.at |   www.bannR.at   |
--------------------------------------

我要输出的是这样的：

-------------------------------------------------------------------------------------
|ID | ImageURLiOS | ImageURLiOSRetina | BannerImageURLiOS | BannerImageURLiOSRetina |
-------------------------------------------------------------------------------------
| 1 | www.test.at |   www.testR.at   |    www.bann.at     |      www.bannR.at       | 
| 2 | www.test.at |   www.testR.at   |    www.bann.at     |      www.bannR.at       | 
| 3 | www.test.at |   www.testR.at   |    www.bann.at     |      www.bannR.at       | 
-------------------------------------------------------------------------------------

我尝试了一些分组和内部连接，但没有真正的工作。是否可以使用SQL完成这一任务，还是需要进行一些PHP处理？

Answer 1

select i.id, 
       u.iOSURL as ImageURLiOS,
       u.iOSRetinaURL as ImageURLiOSRetina, 
       ur.iOSURL as BannerImageURLiOS,
       ur.iOSRetinaURL as BannerImageURLiOSRetina 
from item i 
left join imageurl u on u.id = i.imageurlid
left join imageurl ur on ur.id = i.bannerimageurlid