未知列“building_requirements_count”在“字段列表”中

Question

我用 php 和 mysql 做了一个小游戏。现在我创建的 SQL 查询之一出现问题。这个想法是查询检查用户是否有足够的材料。

我有一个查询，如果我像这样使用它，它会起作用：

SELECT
(
  SELECT COUNT(*)
  FROM building_requirements
  WHERE building_id = '1'
) as building_requirements_count,
(
  SELECT COUNT(*)
  FROM user_materials, building_requirements
  WHERE user_materials.material_id = building_requirements.material_id
    AND user_id = '27'
    AND building_id = '1'
    AND (user_material_amount >= building_material_amount) = 1
) as user_materials_count;

但是当我添加一列使用这些子查询的结果时，它会失败：

SELECT
(
  SELECT COUNT(*)
  FROM building_requirements
  WHERE building_id = '1'
) as building_requirements_count,
(
  SELECT COUNT(*)
  FROM user_materials, building_requirements
  WHERE user_materials.material_id = building_requirements.material_id
    AND user_id = '27'
    AND building_id = '1'
    AND (user_material_amount >= building_material_amount) = 1
) as user_materials_count, 
building_requirements_count = user_materials_count as enough_materials;

我收到错误：

#1054 - Unknown column 'building_requirements_count' in 'field list'

有人可以向我解释为什么我不能使用子查询在这里？我该如何解决这个问题？

Answer 1

我想你的

building_requirements_count = user_materials_count as enough_materials;

意思可能是......

building_requirements_count - user_materials_count as enough_materials;

Answer 2

因为您的表定义中没有名为“building_requirements_count”的字段。
除了WHERE部分之外，这里不允许使用自定义字段。

为什么不在查询的 WHERE 部分使用自定义字段？

编辑：
您可以更轻松地从数据库中获取每个值并在 PHP 中进行计算。

$result = mysql_query("SELECT COUNT(*) FROM building_requirements WHERE building_id = '1'");
if ($result) {
  $row = mysql_fetch_row($result);
  $building_requirements_count  = $row[0];
}
else {
  $building_requirements_count = 0;
}

$query = "  SELECT COUNT(*)
  FROM user_materials, building_requirements
  WHERE user_materials.material_id = building_requirements.material_id
    AND user_id = '27'
    AND building_id = '1'
    AND (user_material_amount >= building_material_amount) = 1";

$result2 = mysql_query($query);
if ($result2) {

  $row = mysql_fetch_row($result2);
  $user_material_count  = $row[0];
}
else {
  $user_material_count = 0;
}

$enough_materials = ( $user_material_count >= $building_requirements_count) ? true : false;