MySQL 使用行计数和分组进行选择

Question

我有 2 个表，订单和 order_lineitems。订单包含订单状态信息(销售日期、发票号、销售类型等)order_lineitems 包含一对多关系中每个订单的项目。由于我们按行项目提供运输信息，ship_date 在 order_lineitems 表中，如果未发货则为 null，如果已发货则为日期。

我试图通过比较订单项行数与具有发货日期的订单项行来提取所有商品已发货的订单。虽然我已经成功提取了所有这些信息，但我无法进行最后一步，将结果集限制为仅包含完全发货的订单(行数 = ship_date 不为空的行数)。

我知道我遗漏了一些简单的东西，但就是看不到它......

select sum(custom.lineitems) as totalitems, sum(custom.shipped) as totalshipped,
custom.invoice, z.shipregion
from (
 select a.invoice, count(a.invoice) as lineitems, 0 as shipped
 from order_lineitem a
 group by a.invoice

UNION ALL

 select b.invoice, 0 as notshipped, count(b.ship_date) as shipped
 from order_lineitem b
 where b.ship_date is not null
 group by b.invoice
) as custom

left join orders z on custom.invoice = z.invoice
where z.result = 0
and z.respmsg like 'Approved%'
and z.shipregion <> 'PENDING'
and z.cancelorder = 0
group by custom.invoice;

这将返回一个结果集(数据库中的每张发票一行)

   totalitems   totalshipped    invoice shipregion
  4           2     1000    REGION08
  1           1     10001   REGION07
  1           1     10004   REGION05
  3           1     10006   REGION05
  2           2     10007   REGION04
  1           1     10008   REGION08    
  7           7     10009   REGION01    
  1           1     1001    REGION08

我正在寻找的是这样的结果集 - 仅在 totalitems = totalshipped 的情况下

totalitems  totalshipped    invoice shipregion
  1           1     10001   REGION07
  1           1     10004   REGION05
  2           2     10007   REGION04
  1           1     10008   REGION08    
  7           7     10009   REGION01    
  1           1     1001    REGION08

Answer 1

使用HAVING子句

SELECT a.invoice, z.shipregion, COUNT(a.invoice) AS lineitems, 
       SUM(CASE WHEN a.ship_date IS NOT NULL THEN 1 ELSE 0 END) AS shipped
FROM order_lineitem a
LEFT JOIN orders z ON a.invoice = z.invoice AND z.result = 0 AND z.cancelorder = 0 AND 
                      z.respmsg LIKE 'Approved%' AND z.shipregion <> 'PENDING'                         
GROUP BY a.invoice HAVING lineitems = shipped

或

SELECT a.invoice, a.shipregion, a.lineitems, a.shipped 
FROM (SELECT a.invoice, z.shipregion, COUNT(a.invoice) AS lineitems, 
             SUM(CASE WHEN a.ship_date IS NOT NULL THEN 1 ELSE 0 END) AS shipped
      FROM order_lineitem a
      LEFT JOIN orders z ON a.invoice = z.invoice AND z.result = 0 AND z.cancelorder = 0 AND 
                            z.respmsg LIKE 'Approved%' AND z.shipregion <> 'PENDING'                         
      GROUP BY a.invoice
    ) AS a WHERE a.lineitems = a.shipped