在mysql中如何添加第三个条件?

gcmastyq  于 2021-06-21  发布在  Mysql
关注(0)|答案(3)|浏览(387)

使用冲锋队;

SELECT imperial_battlegroup.BGID, imperial_battlegroup.Designation, imperial_battlegroup.HQ_LocationX, imperial_battlegroup.HQ_LocationY,
stormtrooper_unit.STUID, stormtrooper_unit.UnitCmd, stormtrooper_unit.UnitType, stormtrooper_unit.Location_X, stormtrooper_unit.Location_Y,
ABS(stormtrooper_unit.Location_X - stormtrooper_unit.Location_Y) AS XYRange 
from imperial_battlegroup inner join
     stormtrooper_unit
     on imperial_battlegroup.BGID = stormtrooper_unit.UnitCmd
WHERE Designation = 'Battle Group I' and UnitType = 'Aslt Infantry' AND 
      XYRange > 100;

当我在没有“xyrange>100”的情况下执行文件时,它工作得非常好,但是我确实需要输出的xyrange大于100。
你觉得问题出在哪里??
提前谢谢

gjmwrych

gjmwrych1#

where子句中禁止使用别名。所以使用函数本身

where ABS(stormtrooper_unit.Location_X - stormtrooper_unit.Location_Y)> 100
6l7fqoea

6l7fqoea2#

sql不允许在中使用列别名 WHERE 条款。它确实扩展了 HAVING ,因此一种解决方案是:

SELECT bg.BGID, bg.Designation, imperial_battlegroup.HQ_LocationX, bg.HQ_LocationY,
        u.STUID, u.UnitCmd, u.UnitType, u.Location_X, u.Location_Y,
ABS(stormtrooper_unit.Location_X - stormtrooper_unit.Location_Y) AS XYRange 
from imperial_battlegroup bg inner join
     stormtrooper_unit
     on bg.BGID = u.UnitCmd
WHERE Designation = 'Battle Group I' and UnitType = 'Aslt Infantry' 
HAVING XYRange > 100;

另一种选择是在 WHERE .

yhuiod9q

yhuiod9q3#

试试这个:

SELECT imperial_battlegroup.BGID, imperial_battlegroup.Designation, 
imperial_battlegroup.HQ_LocationX, imperial_battlegroup.HQ_LocationY,
stormtrooper_unit.STUID, stormtrooper_unit.UnitCmd, 
stormtrooper_unit.UnitType, stormtrooper_unit.Location_X, 
stormtrooper_unit.Location_Y,
ABS(stormtrooper_unit.Location_X - stormtrooper_unit.Location_Y) AS XYRange 
from imperial_battlegroup inner join
stormtrooper_unit
on imperial_battlegroup.BGID = stormtrooper_unit.UnitCmd 
WHERE Designation = 'Battle Group I' and UnitType = 'Aslt Infantry' AND 
ABS(stormtrooper_unit.Location_X - stormtrooper_unit.Location_Y) > 100;

刚刚更新了最后一行,其余都一样。

相关问题