Mysql - Select 语句，对符合条件的记录进行分组

Question

我有下表“StockItems”(针对此示例进行了简化):

+----+----------+---------+---------+---------+-----+
| ID | SubCatID |  Item   | GroupID |  Size   | Def |
+----+----------+---------+---------+---------+-----+
|  1 |        1 |    40mm |       2 |    1    |   1 |
|  2 |        1 |    40mm |       2 |    1    |   0 |
|  3 |        1 |    75mm |       3 |    1    |   1 |
|  4 |        2 |    90mm |       4 |    NULL |   1 |
+----+----------+---------+---------+---------+-----+

这是我的查询:

SELECT ID, SubCatID, Item, GroupID 
FROM StockItems 
WHERE ((GroupID = 0 OR GroupID IS NULL) OR ((GroupID IS NOT NULL OR GroupID <> 0) AND Def = 1) OR Size IS NOT NULL) AND Del=0 
ORDER BY SubCatID, Size, Item+0, Item

其中Def是GroupID中的Default项，Del代表已删除

这行得通，但我还需要 1 个步骤才能获得我想要的结果:我不想检索具有相同 GroupID 和大小的记录 WHERE SubCatID = 1

如果任何 groupID 的 GroupID 和 Size 都相同，我希望它检索 Def = 1 的记录

我几乎通过使用以下查询到达那里:

SELECT ID, SubCatID, Item, GroupID 
FROM StockItems 
WHERE ((GroupID = 0 OR GroupID is Null) OR ((GroupID IS NOT NULL OR GroupID <> 0) AND Def = 1) OR Size IS NOT NULL) AND Del=0 
GROUP BY GroupID, Size 
ORDER BY SubCatID, Size, Item+0, Item

但我只想将 GROUP BY 应用于 SubCatID = 1 的记录

我实际上不确定 GROUP BY 是否是去这里的方式，它只是我所看到的最接近我需要的方式

第一个查询的输出:

+-----+----------+---------------+----------+
| ID  | SubCatID |     Item      | GroupID  |
+-----+----------+---------------+----------+
|   1 |        1 |    40mm Pipe  |  2       |
| 331 |        1 |    40mm Pipe  | 2        |
| 329 |        1 |    63mm Pipe  | 3        |
| 330 |        1 |    75mm Pipe  | 4        |
|   2 |        1 |    40mm Pipe  | 2        |
| 332 |        1 |    40mm Pipe  | 2        |
|   3 |        1 |    63mm Pipe  | 3        |
|   4 |        1 |    75mm Pipe  | 4        |
|   6 |        1 |    110mm Pipe | 6        |
|   7 |        1 |    125mm Pipe | 7        |
|   8 |        1 |    160mm Pipe | 8        |
|   9 |        1 |    200mm Pipe | 9        |
|  10 |        1 |    250mm Pipe | 10       |
|  11 |        1 |    315mm Pipe | 11       |
|  12 |        1 |    355mm Pipe | 12       |
|  13 |        1 |    400mm Pipe | 13       |
|  14 |        1 |    450mm Pipe | 14       |
|   5 |        1 |    90mm Pipe  |  5       |
|  15 |       18 |   63mm 90 Be  | NULL     |
|  16 |       18 |   75mm 90 Be  | NULL     |
|  17 |       18 |   90mm 90 Be  | NULL     |
|  18 |       18 |   110mm 90 Be | NULL     |
|  19 |       18 |   125mm 90 Be | NULL     |
|  20 |       18 |   160mm 90 Be | NULL     |
+-----+----------+---------------+----------+

后一个查询的输出:

+-----+----------+---------------+----------+
| ID  | SubCatID |     Item      | GroupID  |
+-----+----------+---------------+----------+
|   1 |        1 |    40mm Pipe  | 2        |
| 329 |        1 |    63mm Pipe  | 3        |
| 330 |        1 |   75mm Pipe   | 4        |
|   2 |        1 |    40mm Pipe  | 2        |
|   3 |        1 |    63mm Pipe  | 3        |
|   4 |        1 |    75mm Pipe  | 4        |
|   6 |        1 |    110mm Pipe | 6        |
|   7 |        1 |    125mm Pipe | 7        |
|   8 |        1 |    160mm Pipe | 8        |
|   9 |        1 |    200mm Pipe | 9        |
|  10 |        1 |    250mm Pipe | 10       |
|  11 |        1 |    315mm Pipe | 11       |
|  12 |        1 |    355mm Pipe | 12       |
|  13 |        1 |    400mm Pipe | 13       |
|  14 |        1 |    450mm Pipe | 14       |
|   5 |        1 |    90mm Pipe  |  5       |
|  15 |       18 |   63mm 9B     | NULL     |
| 327 |       61 |  Stainless St | 20       |
+-----+----------+---------------+----------+

这就是我想要的，但是有很多记录 GroupID=Null 和 Size=Null .. 因此这些不会显示在结果中。所以我只希望 GROUP BY 发生在 SubCatID = 1

Answer 1

您需要使用 HAVING。所以像下面这样:

SELECT ID, SubCatID, Item, GroupID 
FROM StockItems
GROUP BY GroupID
HAVING SubCatID = 1

MySQL Having