mysql group by with where返回完全其他结果

I try to select in a one to many relation childs where records of childs is 1. For fetching childs with one record I use the following query.

我尝试在一对多关系子项中选择子项的记录为1.对于使用一个记录获取子项，我使用以下查询。

Here is the simple query which works if I do not use wherestatement

这是一个简单的查询，如果我不使用wherestatement

select a.iid, 
account_id,
count(*) as props

from  accounts_prop a
group by a.account_id
having props = 1

when I use where I get back totally other result. In this case I get records which shows that props are having 1 record but actually having more than one

当我使用的地方我完全取回其他结果。在这种情况下，我得到的记录显示道具有1条记录但实际上有多条记录

    select a.iid, 
    account_id,
    count(*) as props

    from   accounts_prop a
    where a.von >= '2017-08-25'
    group by a.account_id

having props = 1

What I'm missing in this case

我在这种情况下缺少什么

2 个解决方案

#1

Upon closer inspection, your original query is not even determinate:

仔细检查后，您的原始查询甚至无法确定：

SELECT
    a.iid,              -- OK, but which value of iid do we choose?
    a.account_id,
    COUNT(*) AS props
FROM accounts_prop a
GROUP BY a.account_id
HAVING props = 1

The query makes no sense because you are selecting the iid column while aggregating on other columns. Which value of iid should be chosen for each account? This is not clear, and your query would not even run on certain versions of MySQL or most other databases.

查询没有意义，因为您在聚合其他列时选择了iid列。应为每个帐户选择哪个iid值？这不清楚，您的查询甚至不会在某些版本的MySQL或大多数其他数据库上运行。

Instead, put your logic to find accounts with only a single record into a subquery, and then join to that subquery to get the full records for each match:

相反，将您的逻辑用于查找只有一条记录到子查询中的帐户，然后加入该子查询以获取每个匹配的完整记录：

SELECT a1.*
FROM accounts_prop a1
INNER JOIN
(
    SELECT account_id
    FROM accounts_prop
    GROUP BY account_id
    HAVING COUNT(*) = 1
) a2
    ON a1.account_id = a2.account_id
WHERE a1.von >= '2017-08-25'

#2

the where condition filter you original rows so

where条件过滤原始行所以

 where a.von >= '2017-08-25'

reduce the number of rows involved in query

减少查询中涉及的行数

the having clause work on the result of a query so in you have filter with a where (or not ) you obtain different result
In your case in the first query your count is calculate on all the rows in your in the second query your count is calculated only on a subset

having子句处理查询的结果，所以在你有过滤器的地方（或没有）你获得不同的结果在第一个查询的情况下你的计数是在第二个查询中的所有行上计算你的计数是仅在子集上计算

This can explain why you obtain different resul from the two query

这可以解释为什么从两个查询中获得不同的结果

#1