mysql - 使用别名从另一列获取值

Question

这是我的代码

select tblData.nama as namamhs,
       tblData.nilai as nilai_A,
       tblData1.nama,
       tblData1.nilai as nilai_B,
       case
          when(tblData.nilai-tblData1.nilai<0)
          then (tblData.nilai-tblData1.nilai)*-1
          when(tblData.nilai-tblData1.nilai>=0)
          then (tblData.nilai-tblData1.nilai)
       end as selisih
from tblData,tblData as tblData1
where tblData1.nama<>tblData.nama
order by tblData1.nama DESC;

我的问题是如何使用别名 nilai_A 从 tblData.nilai 获取值？

我试过了

case
when(nilai_A-nilai_B<0)

但是出现这个错误

ERROR 1054 (42S22) at line 13 in file: 'well.sql': Unknown column 'nilai_A' in 'field list'

Answer 1

如果要使用别名可以这样查询

SELECT nilai_A,nilai_B,
CASE WHEN ((nilai_A - nilai_B)<0) then /*do something*/ ELSE /*do something*/ END AS new_Alias
FROM (
     your query /*except for the ORDER BY Clause*/
     )A
ORDER BY /*your column alias*/ DESC;

在此查询中，您将使用您在原始查询中分配的所有别名。