MySQL Having子句和Count()

Question

我有以下表格，并尝试检索每个项目的名称、描述、库存、地址、邮政编码和城市，满足以下条件：item1的库存>10，item2的库存>10，item3的库存>5。因此，我不想要只包含item1和item2的行，例如ID = 2的行。ID = 1的行也不符合条件，因为item1的库存<10。这意味着只有ID为3和4的行匹配。但我不想看到6行，我只需要包含名称、描述、库存、地址、邮政编码和城市的两行！

| ID | NAME | DESCR | STOCK | ADDRESS | POSTALCODE | CITY |
|  1  |  foo  |  item1 |  5 |  addr1 |  po1 | city1 |
| 1  | foo  | item2 | 10 | addr1 | po1 | city1 |
| 1  | foo  | item3 | 5 | addr1 | po1 | city1 |
| 2  | bar  | item1 | 40 | addr2 | po1 | city1 |
| 2  | bar  | item2 | 30 | addr2 | po1 | city1 |
| 3  | smth  | item1 | 25 | addr3 | po3 | city1 |
| 3  | smth  | item2 | 20 | addr3 | po3 | city1 |
| 3  | smth  | item3 | 10 | addr3 | po3 | city1 |
| 4  | els  | item1 | 45 | addr4 | po4 | city1 |
| 4  | els  | item2 | 30 | addr4 | po4 | city1 |
| 4  | els  | item3 | 10 | addr4 | po4 | city1 |

我认为我需要类似这样的东西：

SELECT name, descr, stock , address, postalcode, city
FROM table1
WHERE (descr like 'item1' AND stock >10) 
OR (descr like 'item2' AND stock >10) OR (descr like 'item3' AND stock >5)
GROUP BY name, descr, stock , address, postalcode, city
HAVING COUNT(distinct(somethingIdidNotFigureOutYet)) > 2

我试图计算id、名称甚至是描述，但结果不正确。请问有人能帮我解决这个问题吗？谢谢！（对于丑陋的表格格式，我很抱歉）

- b101

---

当有六行符合您的条件时，您想要看到哪两行？ - Gordon Linoff

Answer 1

我通常处理这类问题的方式是将它们分解成较小的组，然后根据需要组合查询。让我们考虑一些事情：

• 物品1和物品2的库存必须大于10，物品3的库存必须大于5。

我们可以像这样获取符合这些要求的行：

SELECT id
FROM myTable
GROUP BY id
HAVING 
  SUM(CASE WHEN descr = 'item1' THEN stock else 0 END) > 10 AND 
  SUM(CASE WHEN descr = 'item2' THEN stock else 0 END) > 10 AND 
  SUM(CASE WHEN descr = 'item3' THEN stock else 0 END) > 5;

这将返回id为3和4。巧合的是，这也满足我们必须至少拥有一个item1、item2和item3的条件，因为我们在having子句中使用了“AND”，这导致它排除了id为2的行。

这里有一个SQL Fiddle示例。

- AdamMc331

---

你的查询中的 DISTINCT 是相当无用的。 - Pரதீப்

我想这是因为我添加了 GROUP BY。 - AdamMc331

@McAdam331 非常感谢！非常成功。我也要感谢其他人的努力！ - b101

Answer 2

在这种情况下，给定WHERE过滤器，您可以将结果集组限制为那些HAVING COUNT(DISTINCT descr) = 3（或者如果descr在每个组中必须是唯一的，则简单地使用HAVING COUNT(*) = 3）。

然而，更一般的解决方案可能是明确验证每个条件是否被每个组满足：

HAVING SUM(descr = 'item1' AND stock > 10)
   AND SUM(descr = 'item2' AND stock > 10)
   AND SUM(descr = 'item3' AND stock >  5)

这是因为每个SUM()中的布尔表达式在为真时评估为1，在为假时评估为0。因此，在每个组上进行SUM()会导致所有记录均为假时结果为0，任何记录为真时结果为非零（当在AND操作/ HAVING子句的布尔上下文中使用时，产生所需的结果）。

虽然结果集不会受影响，但出于性能原因，我仍然会保留WHERE子句。

- eggyal

Answer 3

你需要从 select 和 group by 中删除 descr 并将其添加到 Having 子句以过滤组。

尝试这个。

SELECT ID,
       NAME, 
       stock, 
       address, 
       postalcode, 
       city 
FROM   table1 
WHERE  ( descr IN ( 'item1', 'item2' ) 
         AND stock > 10 ) 
        OR ( descr = 'item3' 
             AND stock > 5 ) 
GROUP  BY ID,
          NAME, 
          stock, 
          address, 
          postalcode, 
          city 
HAVING Count(DISTINCT descr) = 3

注意：使用LIKE运算符而没有wildcard是没有意义的，因此我已将其更改为=运算符。

- Pரதீப்

Answer 4

如果你想要姓名和地址信息，那么可以使用 HAVING 子句，但也需要修复 GROUP BY 子句。以下是一个示例：

SELECT name, address, postalcode, city
FROM table1
GROUP BY name, address, postalcode, city
HAVING SUM(CASE WHEN descr like 'item1' THEN stock END) > 10 AND
       SUM(CASE WHEN descr like 'item2' THEN stock END) > 10 AND
       SUM(CASE WHEN descr like 'item3' THEN stock END) > 5; 

注意：我刚刚将所有条件移动到HAVING子句中，以便过滤逻辑在一个地方。如果你愿意，可以在WHERE子句中重复它。

然后，如果你想要描述和库存量，一种方法是使用列表：

SELECT name, address, postalcode, city,
       GROUP_CONCAT(descr, ':', stock SEPARATOR '; ') as items
FROM table1
GROUP BY name, address, postalcode, city
HAVING SUM(CASE WHEN descr like 'item1' THEN stock END) > 10 AND
       SUM(CASE WHEN descr like 'item2' THEN stock END) > 10 AND
       SUM(CASE WHEN descr like 'item3' THEN stock END) > 5; 

如果你想要原始行，我建议使用IN或EXISTS：

select t.*
from table1 t
where exists (select 1
              from table1 t1
              where t1.id = t.id and t1.descr = 'item1' and t1.stock > 10
             ) and
      exists (select 1
              from table1 t1
              where t1.id = t.id and t1.descr = 'item2' and t1.stock > 10
             ) and
      exists (select 1
              from table1 t1
              where t1.id = t.id and t1.descr = 'item3' and t1.stock > 5
             ) and
      t.descr in ('item1', 'item2', 'item3');

注意：此版本假设给定的id没有重复的条目。第一个版本并不做出这个假设。

- Gordon Linoff

---

ن½؟用CASE WHEN descr LIKE 'item1'ن¸ژWHEN descr = 'item1'相و¯”，وک¯هگ¦وœ‰ن¼کهٹ؟è؟کوک¯هڈھوک¯ن¸ھن؛؛هپڈه¥½ï¼ں - AdamMc331

@McAdam331……我几乎从不使用简单的case语句，因为我发现逻辑经常需要修改为搜索的情况。第一次编写搜索的情况版本更简单。 - Gordon Linoff

这很有道理，因此在这方面更具有“可扩展性”，将来更容易进行更改？ - AdamMc331

@GordonLinoff 非常感谢！我真的很感激！！ - b101