MySQL 如何在 have 子句中设置两个条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13896857/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I make two condition in having clause
提问by user1810868
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
I need to query the number of Domainsthat share the same Numberand their Filename ends with _1. I tried the following:
我需要查询Domains共享相同Number且File名称以_1. 我尝试了以下方法：
select count(domain) as 'sum domains', file
from table
group by Number
having
count(Number) >1 and File like '%\_1';
It gives me:
它给了我：
sum domains | file
------------------------------
2 | aaa.com
2 | bbb.com
I expected to see the following:
我希望看到以下内容：
sum domains | file
------------------------------
1 | aaa.com
2 | bbb.com
Because the Number111 appears once with Fileends with _1and _2, so it should count 1 only. How can I apply the 2 conditions that I stated earlier correctly ?
因为Number111File以_1and结尾出现一次_2，所以它应该只计为 1。如何正确应用我之前陈述的 2 个条件？
回答by eggyal
As documented under SELECTSyntax:
如SELECT语法下所述：
The
HAVINGclause is applied nearly last, just before items are sent to the client, with no optimization.
该
HAVING子句几乎最后应用，就在项目被发送到客户端之前，没有优化。
In other words, it is applied afterthe grouping operation has been performed (in contrast with WHERE, which is performed beforeany grouping operation). See WHERE vs HAVING.
换句话说，它在执行分组操作之后应用（与在任何分组操作之前WHERE执行相反）。请参阅WHERE 与 HAVING。
Therefore, your current query first forms the resultset from the following:
因此，您当前的查询首先从以下内容形成结果集：
SELECT COUNT(domain) AS `sum domains`, file
FROM `table`
GROUP BY Number
See it on sqlfiddle:
| SUM DOMAINS | FILE | --------------------------- | 2 | aaa.com_1 | | 2 | bbb.com_1 | | 1 | eee.com_1 |
As you can see, the values selected for the filecolumn are merely one of the values from each group—as documented under MySQL Extensions to GROUP BY:
如您所见，为file列选择的值只是来自每个组的值之一——如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.
服务器可以自由地从每个组中选择任何值，因此除非它们相同，否则选择的值是不确定的。
Your current query then proceeds to filter these results according to your HAVINGclause:
您当前的查询然后继续根据您的HAVING子句过滤这些结果：
HAVING COUNT(Number) > 1 AND file LIKE '%\_1'
With the values of fileselected 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 queryare:
使用file上面选择的值，每个组都符合第二个条件；前两组符合第一个标准。因此，完整查询的结果是：
| SUM DOMAINS | FILE | --------------------------- | 2 | aaa.com_1 | | 2 | bbb.com_1 |
Following your comments above, you want to filter the records on filebeforegrouping and then filter the resulting groups for those containing more than one match. Therefore use WHEREand HAVINGrespectively (and select Numberinstead of fileto identify each group):
按照上面的评论，您希望file在分组之前过滤记录，然后过滤包含多个匹配项的结果组。因此分别使用WHERE和HAVING（并选择Number而不是file标识每个组）：
SELECT Number, COUNT(*) AS `sum domains`
FROM `table`
WHERE file LIKE '%\_1'
GROUP BY Number
HAVING `sum domains` > 1
See it on sqlfiddle:
| NUMBER | SUM DOMAINS | ------------------------ | 222 | 2 |
回答by Julie Kalu
You cannot have the file name in the SELECTstatement if it is not also in the GROUP BY. You have to get your GROUP BYresult than JOINback to the original and add the filter logic like so:
如果文件名SELECT不在GROUP BY. 您必须获得GROUP BY结果而不是JOIN返回原始结果并添加过滤器逻辑，如下所示：
SELECT *
FROM
(
select count(domain) as 'sum_domains', Number
from table
group by Number
having
count(Number) >1
) result
join table t on result.Number = t.Number
WHERE file like '%\_1'
回答by Ahmed Awan
i am using by following
我正在使用以下
having ( SUM(qty) > 4 AND SUM(qty) < 15 )
回答by Yogendra Singh
Try the nested query below:
试试下面的嵌套查询：
select count(domain) as 'sum domains', domain as fileName
from
(select domain, file from tableName
group by Number
having count(Number) >1) as temp
WHERE file like '%\_1';
