mysql like having_mysql – 如何在having子句中创建两个条件

如SELECT语法中所述：

The HAVING clause is applied nearly last, just before items are sent to the client, with no optimization.

换句话说,在执行分组操作之后应用它(与在任何分组操作之前执行的WHERE相反).见WHERE vs HAVING.

因此,您当前的查询首先从以下形式生成结果集：

SELECT COUNT(domain) AS `sum domains`, file

FROM `table`

GROUP BY Number

| SUM DOMAINS | FILE |

---------------------------

| 2 | aaa.com_1 |

| 2 | bbb.com_1 |

| 1 | eee.com_1 |

如您所见,为文件列选择的值只是每个组中的一个值 – 如MySQL Extensions to GROUP BY中所述：

The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

然后,您当前的查询将根据您的HAVING子句过滤这些结果：

HAVING COUNT(Number) > 1 AND file LIKE '%\_1'

使用上面选择的文件值,每个组匹配第二个标准;前两组匹配第一个标准.因此the complete query的结果是：

| SUM DOMAINS | FILE |

---------------------------

| 2 | aaa.com_1 |

| 2 | bbb.com_1 |

在your comments above之后,您希望在分组之前过滤文件中的记录,然后对包含多个匹配的组过滤结果组.因此,分别使用WHERE和HAVING(并选择Number而不是file来标识每个组)：

SELECT Number, COUNT(*) AS `sum domains`

FROM `table`

WHERE file LIKE '%\_1'

GROUP BY Number

HAVING `sum domains` > 1

| NUMBER | SUM DOMAINS |

------------------------

| 222 | 2 |