mysql - 计算 GROUPed BY 的行数

Question

SELECT id,name,SUM(`lsa`) AS lst FROM 
    table_a AS sn,
    table_b AS fb 
WHERE sn.uid=fb.uid 
  AND COUNT(`lsa`)=6 
GROUP BY sn.uid 
ORDER BY SUM(`lsa`)

我有一个这样的查询，显然 AND COUNT(lsa)=6 位不是有效语法。我迷路了。

我想要实现的是一个列表，按 lsa 的总和排序，按 uid 分组，仅当有该 uid 的 6 个条目，即在该“组”中。我应该怎么做？

Answer 1

按聚合过滤是有效的，但需要进入 HAVING 子句，而不是 WHERE 所以使用

SELECT id,
       name,
       SUM(`lsa`) AS lst
FROM   table_a AS sn
       JOIN table_b AS fb
         ON sn.uid = fb.uid  /*Using Explicit Join syntax*/
GROUP  BY sn.uid,            /*And avoiding MySQL GROUP BY extension*/
          id,
          name
HAVING COUNT(`lsa`) = 6
ORDER  BY SUM(`lsa`)  

相反。