仅当组中的每条记录符合MySQL中的特定条件时才选择分组记录？ [英] How to select grouped records only if every record in a group meets a certain criteria in MySQL?

问题描述

我正在使用 MySQL 5.0.88 / Coldfusion8 ，并且我有一个包含基于条形码/ EAN的产品的表。

因此，一个大小 S，M，L，XL 的产品123将在表中有四个记录

 产品尺寸ean qty disregard_inventory 
 123 S 1111111111111 5 0 
 123 M 1111111111112 7 0 
 123 L 1111111111113 1 0 
 123 XL 1111111111114 2 0 
  

现在我正在像这样搜索这张表：

  SELECT count（a.id）AS total_records，a.disregard_inventory，a.qty 
从artikelstammdaten a 
 ... 
 GROUP BY a.style 
 HAVING sum（a.qty）！= 0 OR（a.disregard_inventory = 1）
  

这可以正常工作，并选择所有未售罄的产品（总计> 0）/始终可用

我现在想添加一个func因此用户可以搜索每个尺寸至少有1个的产品。在这种情况下，风格123

  123 S 1 
 123 M 0 
 123 L 12 
 123 XL 9 
  

不会包含在结果集中，因为 size M 已售罄。

但是我无法让它工作。这是我的（生产垃圾）：

  GROUP BY a.style 
< cfif form.select_type EQ running_sizes > 
 HAVING a.qty！= 0 OR（a.disregard_inventory = 1）
< cfelse> 
 HAVING sum（a.bestand）！= 0 OR（a.disregard_inventory = 1）
< / cfif> 
  

问题：

是否有可能按 style 进行分组，并且每个 ean style >数量> 0？如果是这样，感谢指针！

编辑：

这里是我的完整查询， ：

  SELECT count（a.id）AS gesamt_datensaetze，a.nos，a.nos_anzeige，a.bestand，SUM（a .bestand> 0）AS 
 FROM artikelstammdaten a 
 
 WHERE a.aktiv =ja
 AND a.firma LIKEsome_company
 
 // group by seller_id，style 
 GROUP BY a.iln，a.artikelnummer 
 HAVING sum（a.bestand）！= 0 OR（a.nos =jaAND a.nos_anzeige =ja ）
 AND（SUM（a.bestand> 0）= COUNT（*））
  

< （$（$）$（$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $' a.bestand）！= 0）OR（a.nos =jaAND a.nos_anzeige =ja））
AND（SUM（a.bestand> 0）= gesamt_datensaetze）

这是有效的。

解决方案

<我建议以下查询：

  SELECT COUNT（a.id）AS total_records，a.disregard_inventory，a.qty 
从artikelstammdaten a 
 ... 
 GROUP BY a.style 
 HAVING（SUM（a.qty）！= 0 OR（a.disregard_inventory = 1））
 AND（SUM（qty> 0）= total_records ）
  

我添加到查询中的最后一个条件只能返回一个样式，此产品（ total_records ）等于此产品可用尺寸的数量（ SUM（qty> 0））） 。
qty> 0 将返回0（当产品不可用于给定大小或1（当它可用时）。所以 SUM（qty> 0）将返回一个介于0和总数之间的整数。

I'm using MySQL 5.0.88/Coldfusion8 and I have a table that contains products based on barcodes/EANs.

So a product 123 in size S,M,L,XL will have four records in the table

 product size  ean              qty    disregard_inventory
 123     S     1111111111111     5     0
 123     M     1111111111112     7     0
 123     L     1111111111113     1     0
 123     XL    1111111111114     2     0

Right now I'm searching this table like so:

  SELECT count(a.id) AS total_records, a.disregard_inventory, a.qty
      FROM artikelstammdaten a
      ...
  GROUP BY a.style
  HAVING sum(a.qty) != 0 OR (a.disregard_inventory = 1) 

This works ok and selects all products which are not sold out (sum > 0 across all eans)/always available

I now want to add a function, so users can search for products that have at least 1pc in each size. In this case, style 123

   123   S   1
   123   M   0
   123   L   12
   123   XL  9

would not be included in the resultset as size M is sold out.

However I can't get it to work. This is what I have (produces rubbish):

  GROUP BY a.style
  <cfif form.select_type EQ "running_sizes">
  HAVING a.qty!= 0 OR ( a.disregard_inventory = 1 )
  <cfelse>
  HAVING sum(a.bestand) != 0 OR (a.disregard_inventory = 1) 
  </cfif>   

Question:
Is it at all possible to group by style and only include style when each underlying ean has a quantity > 0? If so, thanks for pointers!

EDIT:
here is my full query, which I'm testing with:

   SELECT count(a.id) AS gesamt_datensaetze, a.nos, a.nos_anzeige, a.bestand, SUM(a.bestand>0) AS what
       FROM artikelstammdaten a

        WHERE a.aktiv = "ja"
        AND a.firma LIKE "some_company" 

        // groups by seller_id, style
        GROUP BY a.iln, a.artikelnummer
        HAVING sum(a.bestand) != 0 OR (a.nos = "ja" AND a.nos_anzeige = "ja")   
        AND ( SUM(a.bestand > 0) = COUNT(*)) 

Solution:
Partenthesis mising:

HAVING (( sum(a.bestand) != 0 ) OR (a.nos = "ja" AND a.nos_anzeige = "ja" ))
AND  ( SUM(a.bestand > 0) = gesamt_datensaetze  )

This works.

解决方案

I suggest the following query:

SELECT COUNT(a.id) AS total_records, a.disregard_inventory, a.qty
    FROM artikelstammdaten a
    ...
GROUP BY a.style
HAVING (SUM(a.qty) != 0 OR (a.disregard_inventory = 1))
       AND (SUM(qty>0) = total_records)

The last condition I added to the query enables to return a style only if the number of sizes for this product (total_records) is equal to the number of available sizes for this product (SUM(qty>0)).
qty>0 will either return 0 (when the product is not available in the given size, or 1 (when it is available). So SUM(qty>0) will return an integer number between 0 and the total number of sizes.

这篇关于仅当组中的每条记录符合MySQL中的特定条件时才选择分组记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！