我有一个表,存储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";