Mysql：如何检索基于 COUNT() 结果的数据

Question

我很困惑如何从 MySQL 数据库检索一些数据。我的查询如下：

inspection_report table:
 - Inspection_datetime
 - Line
 - Model
 - Lot_no
 - Serial_number
 - Shift
 - Range_sampling
 - Accesories
 - Acc_qty
Employee table:
 - NIK
 - name

SELECT DATE(A.Inspection_datetime) AS Date, A.Line,TRIM(A.Model) AS Model, A.Lot_no,
       COUNT(A.Serial_number) AS Qty,GROUP_CONCAT(DISTINCT(A.Shift)) AS Shift,
       IF(RIGHT(A.Range_sampling,4)='0000',10000,RIGHT(A.Range_sampling,4))-MID(A.Range_sampling,5,4)+1 AS Merchandise,
       A.Accesories,A.Acc_qty,A.Range_sampling,B.name
FROM inspection_report A
LEFT JOIN Employee B
ON A.NIK=B.NIK
WHERE CHAR_LENGTH(A.Range_sampling) = 17 AND A.Issue='' AND A.Check='' AND A.Approve=''  
GROUP BY A.Line, A.Model, A.Lot_no, A.Range_sampling
ORDER BY Date DESC,A.Line

我想显示来自 COUNT(A.Serial_number) AS Qty 的数据，其结果如下：

• Qty = 20
• Qty = 8
• Qty = 32

我该如何检索？

---

结果如：

|Date      |Line   |Model |Lot_no| Qty|Shift|Merchandise|Accesories|Acc_qty|Range_sampling|name |
+----------+-------+------+------+----+-----+-----------+----------+-------+--------------+-----+
|2011-05-12| fa 02 |BlaBla|021A  | 20 |  A  |  200      |  OK      |11     |   1-200      |tom  |
|2011-05-12| fa 15 | foo  |021A  | 8  |  A  |  200      |  OK      |11     |   1-200      |Bill |
|2011-05-12| fa 01 | Boom |021A  | 32 |  A  |  200      |  OK      |11     |   1-200      | Jim | 

Answer 1

执行GROUP BY后，您可以使用HAVING来设置要保留聚合数据的条件：

SELECT DATE(A.Inspection_datetime) AS Date
     , A.Line
     , TRIM(A.Model) AS Model
     , A.Lot_no
     , COUNT(A.Serial_number) AS Qty
     , GROUP_CONCAT(DISTINCT(A.Shift)) AS Shift
     ,   IF( RIGHT(A.Range_sampling,4)='0000'
           , 10000
           , RIGHT(A.Range_sampling,4)
           )
       - MID( A.Range_sampling, 5, 4)
       + 1
       AS Merchandise
     , A.Accesories
     , A.Acc_qty
     , A.Range_sampling
     , B.name
FROM inspection_report A
    LEFT JOIN Employee B
       ON A.NIK=B.NIK
WHERE CHAR_LENGTH(A.Range_sampling) = 17 
  AND A.Issue='' 
  AND A.Check='' 
  AND A.Approve=''  
GROUP BY A.Line
       , A.Model
       , A.Lot_no
       , A.Range_sampling
HAVING COUNT(A.Serial_number) IN (20, 8, 32)
ORDER BY Date DESC
       , A.Line

---

最好不要将LIKE与数字一起使用字段或计算结果。 此条件：

Qty LIKE '20' OR Qty LIKE '8' OR Qty LIKE '32'

当 Qty 是整数时，

Qty = 20 OR Qty = 8 OR Qty = 32

可以写为：或等效地写为：

Qty IN (20, 8, 32)

Answer 2

到目前为止，我已经尝试自己使用SELECT inside SELECT：

SELECT X.Date,X.Line,X.Model,X.Qty,...........
FROM(
      SELECT DATE(A.Inspection_datetime) AS Date, A.Line,TRIM(A.Model) AS Model, A.Lot_no,
             COUNT(A.Serial_number) AS Qty,GROUP_CONCAT(DISTINCT(A.Shift)) AS Shift,
             IF(RIGHT(A.Range_sampling,4)='0000',10000,RIGHT(A.Range_sampling,4))-MID(A.Range_sampling,5,4)+1 AS Merchandise,
             A.Accesories,A.Acc_qty,A.Range_sampling,B.name
      FROM inspection_report A
      LEFT JOIN Employee B
      ON A.NIK=B.NIK
      WHERE CHAR_LENGTH(A.Range_sampling) = 17 
      AND A.Issue='' AND A.Check='' AND A.Approve=''  
      GROUP BY A.Line, A.Model, A.Lot_no, A.Range_sampling
      ORDER BY Date DESC,A.Line) X
WHERE X.Qty LIKE '20' OR X.Qty LIKE '8' OR X.Qty LIKE '32'

之后我得到了我想要的。
或者使用HAVING（最简单的方法）。