Mysql PHP使用相同的别名查询同一表5次，但每次对不同的列使用该别名 - mysql php query same table 5 times using the same alias but use that alias for a different column each time

mysql php query same table 5 times using the same alias but use that alias for a different column each time

本文关键字：别名 PHP 查询 Mysql 5次 | 更新日期: 2023-09-27

我有一个表，存储5个不同的网格坐标，我需要从给定坐标的最短距离列出。所以我想我会使用联合查询表5次，每次使用不同列的别名。但似乎每个别名都返回第一个选择的值。这是我尝试过的查询。

SELECT floor(sqrt(pow((cord_1x-$cord1x),2)+pow((cord_1y-$cord1y),2))) as distance,
  alliance_name, player_name, level, priority,cord_1x AS cordx, cord_1y AS cordy, 
  cord_2x, cord_2y, cord_3x, cord_3y, cord_4x, cord_4y, cord_5x, cord_5y 
FROM cords 
where alliance_id = " .$myalliance_id. " AND cord_1x <> '' AND active ='1'
UNION 
SELECT floor(sqrt(pow((cord_2x-$cord1x),2)+pow((cord_2y-$cord1y),2))) as distance, 
  alliance_name, player_name, level, priority,cord_1x, cord_1y, 
  cord_2x AS cordx, cord_2y AS cordy, cord_3x, cord_3y, cord_4x, cord_4y, cord_5x, cord_5y 
FROM cords 
where alliance_id = " .$myalliance_id. " AND cord_2x <> ''  and active ='1' 
order by distance ASC";

我想得到的结果是

player_name alliance_name level priority distance cord
乔恩坏人10高4 1,1
乔恩坏蛋10高6 2,2

我得到的是

player_name alliance_name level priority distance cord
乔恩坏人10高4 1,1
Jon bad guys 10 high 6 1,1

看起来我使用别名是不对的。它不会将下一列的值应用于第一次选择中使用的相同别名。

请记住，我只使用了2个选择用于测试目的，这更容易，但当我弄清楚这一点时，我会查询表5次

首先，使用UNION ALL。您不希望UNION决定合并两个结果集行，因为它们是相同的。

其次，对于UNION ALL操作中的两个子查询，您应该根据名称，数据类型和位置使列一致。您试图仅通过名称和数据类型来符合它们。

也就是说，你需要这样做:

SELECT floor(sqrt(pow((cord_1x-$cord1x),2)+pow((cord_1y-$cord1y),2))) as distance,
       alliance_name, player_name, level, priority,
       cord_1x AS cordx, cord_1y AS cordy,  /*alias columns */
       cord_1x, cord_1y, cord_2x, cord_2y,  /* cord_1 columns repeat */
       cord_3x, cord_3y, cord_4x, cord_4y, cord_5x, cord_5y 
  FROM cords 
 where alliance_id = " .$myalliance_id. " AND cord_1x <> '' AND active ='1'
UNION ALL
SELECT floor(sqrt(pow((cord_2x-$cord1x),2)+pow((cord_2y-$cord1y),2))) as distance, 
       alliance_name, player_name, level, priority,
       cord_2x AS cordx, cord_2y AS cordy,    /* different alias columns */
       cord_1x, cord_1y, cord_2x, cord_2y,    /* cord_2 columns repeat */
       cord_3x, cord_3y, cord_4x, cord_4y, cord_5x, cord_5y 
  FROM cords 
  where alliance_id = " .$myalliance_id. " AND cord_2x <> ''  and active ='1' 
  order by distance ASC";