mysql - 如何在查询的 WHERE 部分使用定义的 AS 别名?
问题描述
为什么以下查询不起作用?
SELECT uid,
CONCAT(fname,' ',lname) AS name,
(SELECT COUNT(t1.id) FROM table1 t1 WHERE t1.reference = user_id) AS amount
FROM users
WHERE type = 'SLA' AND amount > 0;
在查询的 SELECT 部分中,我使用 AS 别名。哪个工作正常,但现在我想确保 ALIASES 实际上持有一些价值,并且只返回那些这样做的行。
我想我错过了什么?
解决方案
where
您不能在from子句中使用别名select
,我想您可以编写一个JOIN
子查询来获取另一个结果集来代替它。
SELECT u.uid,
CONCAT(u.fname,' ',u.lname) AS name,
t1.amount
FROM users u
INNER JOIN (
SELECT COUNT(*) as amount,
reference
FROM table1
GROUP BY reference
) t1 ON t1.reference = u.user_id
WHERE u.type = 'SLA' and t1.amount > 0
推荐阅读
- excel - VBA将Excel单元格“日期类”值转换为日期格式问题
- angular - Download an Firebase storage image to the PC Angular
- html - How to extract only the navbar code in this template
- woocommerce - wooCommerce product variations added through API not showing in the backend
- ios - iOS build script fails on defaults read command
- docker - entrypoint.sh: exec: gunicorn: not found
- r - How to loop a script through a list of tibbles?
- c# - c#数据到excel导出给出空文件
- questdb - 字段和“行”末尾的纳秒时间戳没有一致地存储
- javascript - SwiperJS 箭头在移动设备上被禁用