我如何在having子句中建立两个条件 [英] How can I make two condition in having clause

问题描述

我有一个类似于以下内容的表:

I have a table similar to:

domain      |   file      | Number 
------------------------------------     
aaa.com     | aaa.com_1   | 111
bbb.com     | bbb.com_1   | 222
ccc.com     | ccc.com_2   | 111
ddd.com     | ddd.com_1   | 222
eee.com     | eee.com_1   | 333

我需要查询共享相同Number的Domains的数量，并且它们的File名称以_1结尾.我尝试了以下方法:

I need to query the number of Domains that share the same Number and their File name ends with _1. I tried the following:

select count(domain) as 'sum domains', file
from table 
group by Number
having
count(Number) >1 and File like '%\_1'; 

它给了我

sum domains | file
------------------------------
2           | aaa.com
2           | bbb.com

我希望看到以下内容:

sum domains | file
------------------------------
1           | aaa.com
2           | bbb.com

因为Number 111出现一次，而File以_1和_2结尾，因此它应该只计数1.我该如何正确应用我先前所述的两个条件?

Because the Number 111 appears once with File ends with _1 and _2, so it should count 1 only. How can I apply the 2 conditions that I stated earlier correctly ?

推荐答案

如SELECT语法中所述:

HAVING子句几乎是最后一次应用，就在项目没有发送到客户端之前，没有优化.

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

换句话说，它是在执行分组操作之后应用的(与WHERE相反，在任何分组操作之前进行的WHERE).请参见位置与拥有空间.

In other words, it is applied after the grouping operation has been performed (in contrast with WHERE, which is performed before any grouping operation). See WHERE vs HAVING.

因此，您当前的查询首先形成以下结果集:

Therefore, your current query first forms the resultset from the following:

SELECT   COUNT(domain) AS `sum domains`, file
FROM     `table`
GROUP BY Number

在 sqlfiddle 上查看:

| SUM DOMAINS |      FILE |
---------------------------
|           2 | aaa.com_1 |
|           2 | bbb.com_1 |
|           1 | eee.com_1 |

如您所见，在file列中选择的值仅仅是每个组中的值之一-如

As you can see, the values selected for the file column are merely one of the values from each group—as documented under 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子句继续过滤这些结果:

Your current query then proceeds to filter these results according to your HAVING clause:

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

使用上面选择的file值，每个组都根据第二个条件进行匹配；并且前两个组在第一个条件上匹配.因此，完整查询的结果为:

With the values of file selected above, every single group matches on the second criterion; and the first two groups match on the first criterion. Therefore the results of the complete query are:

| SUM DOMAINS |      FILE |
---------------------------
|           2 | aaa.com_1 |
|           2 | bbb.com_1 |

您在上面的评论，您想要过滤分组之前file 上的记录，然后为包含多个匹配项的结果过滤结果组.因此，分别使用WHERE和HAVING(并选择Number而不是file来标识每个组):

Following your comments above, you want to filter the records on file before grouping and then filter the resulting groups for those containing more than one match. Therefore use WHERE and HAVING respectively (and select Number instead of file to identify each group):

SELECT   Number, COUNT(*) AS `sum domains`
FROM     `table`
WHERE    file LIKE '%\_1'
GROUP BY Number
HAVING   `sum domains` > 1

在 sqlfiddle 上查看它:

| NUMBER | SUM DOMAINS |
------------------------
|    222 |           2 |

这篇关于我如何在having子句中建立两个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！