mysql - 如何使用表别名返回具有重复列的所有数据

Question

我需要从两个表中获取所有列（包括重复项）。我得到了所有的数据，但是重复的列没有任何对别名的引用。我觉得数据应该这样回来（但事实并非如此）：

[0] => Array
        (
            [0] => 1
            [Alias1.id] => 1
            [1] => bay area
            [Alias1.city] => bay area
            [2] => CA
            [Alias1.state] => CA
            [3] => 1
            [Alias2.id] => 1
            [4] => san francisco
            [Alias2.city] => san francisco
            [5] => giants
            [Alias2.baseball_team] => giants
        )

但事实上，数据是这样回来的：

[0] => Array
        (
            [0] => 1
            [id] => 1
            [1] => bay area
            [city] => bay area
            [2] => CA
            [state] => CA
            [3] => 1
            [4] => san francisco
            [5] => giants
            [baseball_team] => giants
        )

以下是查询：

$result = mysqli_query($sql, SELECT  Alias1.id, Alias1.city, Alias1.state, Alias2.id, Alias2.city, Alias2.baseball_team 
          FROM database.table1 AS Alias1 
          LEFT JOIN database.table2 AS Alias2 
          ON Alias1.id = Alias2.id
)

以下是我获取数据的方法：

while($row = mysqli_fetch_array($result))
{
     $data[] = $row;
}
print_r($data);die();

这个问题是一个精致的 How to return mysql data using table aliases with left join and duplicate columns

Answer 1

这样试试

SELECT Alias1.id `Alias1.id`, 
       Alias1.city `Alias1.city`, 
       Alias1.state `Alias1.state`, 
       Alias2.id `Alias2.id`,
       Alias2.city `Alias2.city`, 
       Alias2.baseball_team `Alias2.baseball_team`
  FROM table1 AS Alias1  LEFT JOIN table2 AS Alias2 
    ON Alias1.id = Alias2.id

样本输出：
|别名1.ID | ALIAS1.CITY | ALIAS1.STATE | ALIAS2.ID | ALIAS2.CITY | ALIAS2.ball |球队|
|-----------|-------------|--------------|-----------|---------------|----------------------|
1湾湾区CA 1旧金山巨人
这里是 SQLFiddle演示