mysql - 在 where 子句中使用别名 - SQL

Question

我有一个汇总表 (data_summary) 和一个汇总详细信息表 (data_summary_dtl)。我在 data_summary_dtl 表中应用以下查询来获取

Number of records in detail table for each record in the summary table,select data if count is greater than 2

select summary_gid,COUNT(summary_gid) as varCount 
from data_summary_dtl 
group by summary_gid; 

我可以使用以下查询列出summary_id 以及计数，

这给了我很好的结果，现在我的问题是如何获得计数大于2的记录的结果。

我尝试了以下方法，但没有得到预期的结果:

select summary_gid,COUNT(summary_gid) as varCount
from data_summary_dtl
where varCount > 2 
group by summary_gid; 

select summary_gid,COUNT(summary_gid) as varCount 
from data_summary_dtl
where COUNT(summary_gid) > 2
group by summary_gid; 

还有其他可能得到结果吗？

Answer 1

请使用此查询

select summary_gid,COUNT(summary_gid) as varCount 
from  data_summary_dtl 
group by summary_gid
having COUNT(summary_gid)> 2 

Having 在应用 GROUP BY 后限制选择列表，并在查询语法中在其后使用。WHERE 在 GROUP BY 之前使用，并在 GROUP BY 中分组之前限制选择列表，因此您的聚合函数如 COUNT() 依赖于 GROUP BY 不会产生结果

关于您的查询:

select summary_gid,COUNT(summary_gid) as varCount from  data_summary_dtl 
where varCount > 2 group by summary_gid; 

这里的错误是

Invalid column name varCount

因为您不能使用 WHERE 或 GROUP BY、ORDER 中的 SELECT 列表中定义的别名或HAVING条件。

在第二个查询中

select summary_gid,COUNT(summary_gid) as varCount from  data_summary_dtl 
where COUNT(summary_gid) > 2 group by summary_gid;

您将收到有关

的错误

using aggregate function COUNT in WHERE clause

请注意，在 GROUP BY 语法中使用 WHERE 是完全可以的。例如，如果您想查看有关 summary_gid 的详细信息(大于 10 且小于 15)，则可以使用

select summary_gid,COUNT(summary_gid) as varCount from  data_summary_dtl 
where summary_gid > 10 AND  summary_gid > 20 group by summary_gid;

您还可以将 WHERE 和 HAVING 与 GROUP BY 一起使用，如下所示

select summary_gid,COUNT(summary_gid) as varCount from  data_summary_dtl 
where summary_gid > 10 AND  summary_gid > 20 group by summary_gid 
having COUNT(summary_gid)>1 ;