php - mysql:合并表中的列

Question

我有这4张 table

table FRUIT,  ID is Primary key

|     ID    |    code |    A   |    B      | 
--------------------------------------------
|     1     |    a01  |  apple1 |  quava1  |
|     2     |    a02  |  apple2 |  quava2  |


table FLOWER, ID is Primary key

|     ID    |    code |    C   |    D      | 
--------------------------------------------
|     1     |    a01  |  Rose1 |  Plumer1  |
|     2     |    a02  |  Rose2 |  Plumer2  |


table ANIMAL, ID is Primary key

|     ID    |    code |    E        |    F      | 
--------------------------------------------
|     1     |    a01  |  butterfly1 |  cat1     |
|     2     |    a02  |  butterfly2 |  cat2     |

table DAY, code is Primary key

|    code |    G    |     
-------------------------------------
|    a01  |  Monday |  
|    a02  |  Tuesday| 

我想像这样调用并组合这 4 个表

|  ID  |    A    |   B    |   C   |    D    |    E      |   F  |  G     |code  |
--------------------------------------------------------------------------------
|  1   |  apple1 | quava1 | Rose1 | Plumer1 |butterfly1 | cat1 |Monday  |a01   |
|  2   |  apple2 | quava2 | Rose2 | Plumer2 |butterfly2 | cat2 |Tuesday |a02   |

这是代码:

SELECT day*, fruit*, flower*, animal* 
       FROM day 
         LEFT JOIN fruit 
              ON day.code = fruit.code 
         LEFT JOIN flower 
              ON day.code = flower.code
          LEFT JOIN flower 
              ON day.code = animal.code
          ORDER BY day.code DESC;

据说列代码有歧义。并且无法显示表格。我需要帮助。我该如何解决这个问题？

Answer 1

我会用这种方式来做，使用 Aliases和 JOINs连接所有表(包括您忘记的animal表)，当然不要忘记在需要的地方包含句点.:

SELECT fr.ID, fr.A, fr.B, fl.C, fl.D, a.E, a.F, d.G, fr.code
FROM `day` AS d
LEFT JOIN `fruit`  AS fr  ON d.code = fr.code 
LEFT JOIN `flower` AS fl  ON d.code = fl.code
LEFT JOIN `animal` AS a   ON d.code = a.code
ORDER BY d.code ASC;