MySQL 在 SELECT 语句中将列添加到一起(使用别名)

Question

这是我的选择语句。基本上我正在尝试将所有专栏加在一起以获得总数。

SELECT DISTINCT *, 
MATCH (crm_contacts_search.data) AGAINST ( '" . $keywords['email'] ."' IN BOOLEAN MODE) as exactEmailScore,
MATCH (crm_contacts_search.data) AGAINST ( '" . $keywords['firstName'] ."' IN BOOLEAN MODE) as firstNameScore,
MATCH (crm_contacts_search.data) AGAINST ( '" . $keywords['lastName'] ."' IN BOOLEAN MODE) as lastNameScore, 
MATCH (crm_contacts_search.data) AGAINST ( '" . $keywords['organizationName'] ."' IN BOOLEAN MODE) as orgScore 
FROM contacts_search;

我想添加到我的选择中的是:

(exactEmailScore + firstNameScore + lastNameScore + orgScore) as total

但是收到的是:“字段列表”中的未知列“exactEmailScore”。

我四处寻找，但找不到任何具体的东西。有什么解决办法吗？

Answer 1

如果 MySQL 中的情况与 TSQL/SQL Server 中的情况不同，请原谅我。

如果您将当前结果视为一组并用括号括起来，则可以执行另一个选择来获取结果加上新的计算值。

Select exactEmailScore, firstNameScore, lastNameScore, orgScore,
       CONCAT(exactEmailScore, firstNameScore, lastNameScore, orgScore) as total
 From
(SELECT DISTINCT *, 
    MATCH (crm_contacts_search.data) AGAINST ( '" . $keywords['email'] ."' IN BOOLEAN MODE) as exactEmailScore,
    MATCH (crm_contacts_search.data) AGAINST ( '" . $keywords['firstName'] ."' IN BOOLEAN MODE) as firstNameScore,
    MATCH (crm_contacts_search.data) AGAINST ( '" . $keywords['lastName'] ."' IN BOOLEAN MODE) as lastNameScore, 
    MATCH (crm_contacts_search.data) AGAINST ( '" . $keywords['organizationName'] ."' IN BOOLEAN MODE) as orgScore 
FROM contacts_search
) results