mysql - 在 where 子句中使用字段而不选择它

Question

我尝试在 where 子句中使用字段而不选择它

类似 SELECT field 1 FROM table WHERE field2 = 'foo'

$where= $onlyissuing ? " WHERE ISSUE = 1" : "";
$sql="
SELECT c.*
     , d.Name_fr as DEVISE 
  FROM 
     ( SELECT a.Currency_ISO3 as value
            , a.Rank as ISSUE
            , b.Name_fr as PAYS 
         FROM countries_currencies a
            , countrylist b
        where a.Country_ISO3 = b.Country_ISO3
     ) c
     , currencylist d 
 where c.value = d.Currency_ISO3
 ";
$sql='
SELECT e.*
     , CONCAT(e.value, " - ", e.PAYS, " - ", e.DEVISE ) AS label 
  FROM ('.$sql.')e
  ';
 $sql = $sql.$where;

因此它可以工作，但如果我不选择 a.Rank 作为问题，我会收到错误 fatal error :未捕获的 PDOException:SQLSTATE[42S22]:未找到列:1054 “where 子句”中的未知列“ISSUE”

感谢评论，工作解决方案:

$sql='SELECT a.Currency_ISO3 as value, b.Name_fr as PAYS, c.Name_fr as DEVISE, CONCAT(a.Currency_ISO3, " - ", b.Name_fr, " - ", c.Name_fr ) AS label FROM countries_currencies a INNER JOIN countrylist b on a.Country_ISO3=b.Country_ISO3 INNER JOIN currencylist c on a.Currency_ISO3=c.Currency_ISO3 WHERE a.Rank=1';

Answer 1

正如我在评论中所说，您可以从子查询外部访问的唯一字段是它选择的字段。也就是说，我真的不认为这个查询需要有任何子查询。 我的 PHP 有点弱，所以请仔细检查字符串连接。

sql = "
SELECT cc.Currency_ISO3 AS value
   , cc.Rank AS ISSUE
   , coL.Name_fr AS PAYS
   , cuL.Name_fr AS DEVISE
   , CONCAT(cc.Currency_ISO, ' - ', coL.Name_fr, ' - ', cuL.Name_fr) AS label
FROM countries_currencies AS cc
INNER JOIN countrylist AS coL ON cc.Country_ISO3 = coL.Country_ISO3
INNER JOIN currencylist AS cuL ON cc.Currency_ISO3 = cuL.Currency_ISO3
".($onlyissuing ? "WHERE cc.Rank = 1" : "")
;

您会注意到，由于没有子查询，Rank 可以直接在 WHERE 中引用，因此不需要将其包含在 SELECT (即使是，您也不能在该 WHERE 中使用该 SELECT 的别名。)