mysql - MariaDB 仅选择大于的结果

Question

我有一个如下所示的查询:

select uid,leadcount,salescount,leadcount/salescount as diff from 
(
SELECT x.uid, COUNT(*) leadcount, COUNT(DISTINCT x.fid)
FROM total_leads AS x
WHERE x.uid BETWEEN 1 AND 5
GROUP BY x.uid
) t1 left join 
(
SELECT ud.UserId, COUNT(*) salescount, COUNT(DISTINCT ud.SalesID)
FROM total_sales AS ud
WHERE ud.UserId BETWEEN 1 AND 5
GROUP BY ud.UserID
) t2 on t1.uid=t2.UserId

结果是:

我试图只显示 diff 大于 2.5 的结果(所以在这种情况下只显示 uid 5 应该在输出中可见)。

我尝试使用 WHERE diff >= 2.5 但我得到了“你的 SQL 语法有错误”。

Working example of MySQL

Answer 1

只需在 WHERE 子句中重复原始表达式而不是使用别名:

SELECT uid leadcount, salescount, leadcount/salescount AS diff
FROM
(
    ...
) t
WHERE leadcount/salescount >= 2.5;

在 WHERE 子句中引用 diff 的问题在于它尚不可用。另外，假设您不打算聚合，请参阅戈登的另一个选项的答案。但是，使用 HAVING 只是 MySQL 或 MariaDB 上的一个选项。