MySQL выбирает, используя количество строк и группировку

У меня есть 2 таблицы, заказы и order_lineitems. заказы содержат информацию о статусе заказа (дата продажи, номер счета, тип продажи и т. д.) order_lineitems содержит элементы для каждого заказа в отношении один ко многим. Поскольку мы предоставляем информацию о доставке по позициям, ship_date находится в таблице order_lineitems, ноль, если товар не отправлен, и дата, если он отправлен.

Я пытаюсь получить заказы, в которых все товары были отправлены, сравнивая количество строк позиций со строками позиций, у которых есть дата отгрузки. Хотя я успешно извлек всю эту информацию, я не могу сделать последний шаг, ограничивая набор результатов, чтобы включать только полностью отгруженные заказы (количество строк = количество строк, где 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

mysql sql group-by sum having-clause
person MikeG    schedule 22.01.2014    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Используйте пункт 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
person Saharsh Shah    schedule 22.01.2014
comment
Спасибо, Сахарш, это решило проблему. Я действительно пробовал HAVING, пока работал над этим, но, оглядываясь назад, я думаю, что это не сработало, потому что было включено что-то еще. Спасибо, что нашли время, чтобы исправить меня. - person MikeG; 22.01.2014
comment
@MikeG Проверьте мой обновленный ответ. Вы можете получить запись напрямую, не используя UNION - person Saharsh Shah; 22.01.2014
comment
Для обоих запросов без объединения необходимо изменить b.ship_date на a.ship_date, чтобы не запутать кого-либо еще, кто это увидит. И оба из них выполняются в два раза быстрее, чем объединение. Еще раз, спасибо! - person MikeG; 22.01.2014

arrow_upward
0
arrow_downward

Нужен еще один внешний запрос.

select * from 
(
    \\Your whole query here
) as Result
where Result.totalitems  = Result.totalshipped
person Akshay    schedule 22.01.2014
comment
Я пробовал это раньше и не смог заставить его работать, и я не собираюсь смущать себя публикацией примеров ‹ухмылка› - person MikeG; 22.01.2014