MySQL group by with where 子句的计数大于 1
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9803004/
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
MySQL group by with where clause with having count greater than 1
提问by SammyBlackBaron
This should be really simple I know, but for the life of me I can't get my query to do what I need.
我知道这应该很简单，但对于我的生活，我无法让我的查询做我需要的。
I am looking to find all rows that are a certain status (paid) grouped by refbut only if there is more than 1 row found.
我希望找到所有按特定状态 ( paid) 分组的行，ref但前提是找到超过 1 行。
This is my sample table:
这是我的示例表：
+-----+----------+----------+-------+
| id | deleted | status | ref |
+-----+----------+----------+-------+
| 1 | 0 | pending | 10001 |
| 2 | 0 | paid | 10001 |
| 3 | 0 | paid | 10001 |
| 4 | 0 | paid | 10002 |
| 5 | 1 | pending | 10002 |
| 6 | 1 | paid | 10002 |
| 7 | 0 | pending | 10003 |
| 8 | 0 | paid | 10003 |
| 9 | 0 | paid | 10003 |
| 10 | 0 | paid | 10003 |
| 11 | 0 | pending | 10004 |
| 12 | 0 | paid | 10004 |
| 13 | 1 | pending | 10005 |
| 14 | 1 | paid | 10005 |
| 15 | 1 | paid | 10005 |
| 16 | 0 | paid | 10005 |
| 17 | 0 | pending | 10006 |
| 18 | 0 | paid | 10006 |
| 19 | 0 | paid | 10006 |
+-----+----------+----------+-------+
This is my SQL:
这是我的 SQL：
SELECT * FROM `orders`
WHERE `deleted` = 0 AND `status` = 'paid'
GROUP BY SUBSTR(`ref`,0,5)
HAVING COUNT(*) > 1
ORDER BY `id` DESC
I need to have it matching by SUBSTR due to refsometimes containing appended numbers.
由于ref有时包含附加数字，我需要通过 SUBSTR 匹配它。
The problem is that my query is returning this:
问题是我的查询返回了这个：
+-----+----------+---------+-------+
| id | deleted | status | ref |
+-----+----------+---------+-------+
| 2 | 0 | paid | 10001 |
+-----+----------+---------+-------+
When I'd like it to be returning refs 10001, 10003& 10006.
当我希望它返回refs 时10001，10003& 10006。
Can anyone help me work out what I am doing wrong?
谁能帮我弄清楚我做错了什么？
Thanks
谢谢
回答by Maximilian Mayerl
Try
尝试
SELECT * FROM `orders`
WHERE `deleted` = 0 AND `status` = 'paid'
GROUP BY SUBSTR(`ref`,1,5)
HAVING COUNT(*) > 1
ORDER BY `id` DESC
The position-argument for SUBSTR starts with 1, not with 0.
SUBSTR 的位置参数从 1 开始，而不是从 0 开始。
回答by barsju
From SUBSTR doc:
来自 SUBSTR 文档：
For all forms of SUBSTRING(), the position of the first character in the string from which the substring is to be extracted is reckoned as 1.
对于所有形式的 SUBSTRING()，要从中提取子字符串的字符串中第一个字符的位置被计算为 1。
So try this:
所以试试这个：
SELECT * FROM `orders`
WHERE `deleted` = 0 AND `status` = 'paid'
GROUP BY SUBSTR(`ref`,1,5)
HAVING COUNT(*) > 1
ORDER BY `id` DESC
