mysql - 'building_requirements_count' 中的未知列 'field list'

Question

我正在使用 mysql 用 php 制作一个小游戏。现在我创建的 SQL 查询之一出现问题。这个想法是查询检查用户是否有足够的 Material 。

我有一个疑问，如果我像这样使用它，它会起作用:

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;