在 MySQL 中，我应该如何改变语法来添加“WHERE”？条款
示例：
SELECT s.title, t.description,
u1.value * s.cache_value as Cache,
u2.value * s.drive_value as Drive,
u3.value * s.error_value as Error,
u3.value * s.error_value + u3.value * s.error_value as ErrorHigh,
u3.value * s.error_value - u3.value * s.error_value as ErrorLow,
m.area
FROM storage s
JOIN status t on t.id = s.status_id
JOIN manufac m on m.id = s.area
JOIN unit u1 on s.cache_unit_id = u1.id
JOIN unit u2 on s.drive_unit_id = u2.id
JOIN unit u3 on s.error_unit_id = u3.id
期望的目标：
SELECT s.title, t.description,
u1.value * s.cache_value as Cache,
u2.value * s.drive_value as Drive,
u3.value * s.error_value as Error,
u3.value * s.error_value + u3.value * s.error_value as ErrorHigh,
u3.value * s.error_value - u3.value * s.error_value as ErrorLow,
m.area
FROM storage s
JOIN status t on t.id = s.status_id
JOIN manufac m on m.id = s.area
JOIN unit u1 on s.cache_unit_id = u1.id
JOIN unit u2 on s.drive_unit_id = u2.id
JOIN unit u3 on s.error_unit_id = u3.id
WHERE 123 BETWEEN ErrorHigh AND ErrorLow;
MySQL 返回语法错误未知列 ErrorHigh
注意：ErrorHigh 和 'ErrorLow` 是我创建的用于显示计算值的新列
Example:
SELECT s.title, t.description,
u1.value * s.cache_value as Cache,
u2.value * s.drive_value as Drive,
u3.value * s.error_value as Error,
u3.value * s.error_value + u3.value * s.error_value as ErrorHigh,
u3.value * s.error_value - u3.value * s.error_value as ErrorLow,
m.area
FROM storage s
JOIN status t on t.id = s.status_id
JOIN manufac m on m.id = s.area
JOIN unit u1 on s.cache_unit_id = u1.id
JOIN unit u2 on s.drive_unit_id = u2.id
JOIN unit u3 on s.error_unit_id = u3.id
Desired goal:
SELECT s.title, t.description,
u1.value * s.cache_value as Cache,
u2.value * s.drive_value as Drive,
u3.value * s.error_value as Error,
u3.value * s.error_value + u3.value * s.error_value as ErrorHigh,
u3.value * s.error_value - u3.value * s.error_value as ErrorLow,
m.area
FROM storage s
JOIN status t on t.id = s.status_id
JOIN manufac m on m.id = s.area
JOIN unit u1 on s.cache_unit_id = u1.id
JOIN unit u2 on s.drive_unit_id = u2.id
JOIN unit u3 on s.error_unit_id = u3.id
WHERE 123 BETWEEN ErrorHigh AND ErrorLow;
MySQL returns syntax Error unknown column ErrorHigh
Note: ErrorHigh and 'ErrorLow` are the the new columns I create to display the computed values
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
1054错误是因为SQL不支持在WHERE子句中引用列别名——MySQL最早支持的是
GROUP BY。但大多数其他数据库仅支持 ORDER BY 子句中的列别名。如果要继续使用列别名，则它所代表的操作需要在子选择、派生表/内联视图中执行。否则，您可以将列别名替换为其代表的操作 - 如下所示：
The 1054 error is because SQL doesn't support referencing column aliases in the WHERE clause - the earliest MySQL supports is the
GROUP BY. But most other databases only support column aliases in theORDER BYclause.If you want to continue to use the column alias, the operation it represents needs to be performed in a subselect, derived table/inline view. Otherwise, you can replace the column alias with the operation it represents - like this: