Блог

У меня есть таблицы:

заказы:

 id_order    id_customer    
1           1              
2           2              
3           1              
  

orders_history

 id_history  id_order    id_order_state    date_add
1           1           1                 2010-01-01 00:00:00
2           1           2                 2010-01-02 00:00:00
3           1           3                 2010-01-03 00:00:00
4           2           2                 2010-05-01 00:00:00
5           2           3                 2011-05-02 00:00:00
6           3           1                 2011-05-03 00:00:00
7           3           2                 2011-06-01 00:00:00
  

order_state

 id_order_state    name
1                 New
2                 Sent
3                 Rejected
4                 ...
  

Как получить все order_id, где последний id_order_state этого заказа (под последним я подразумеваю это с MAX (id_history) или MAX (date_add)) не равен 1 или 3?

 select oh.id_history, oh.id_order, oh.id_order_state, oh.date_add
from (
    select id_order, max(date_add) as MaxDate
    from orders_history
    where id_order_state not in (1, 3)
    group by id_order 
) ohm
inner join orders_history oh on ohm.id_order = oh.id_order 
    and ohm.MaxDate = oh.date_add
  

Я думаю, что ему нужно, чтобы заказы были завершены… т.е. их окончательный статус, а не те, которые не относятся конкретно к 1 и 3. Первым предварительным запросом должен быть максимальный идентификатор независимо от кода состояния

 select 
      orders.*
   from
      ( select oh.id_order, 
               max( oh.id_history ) LastID_HistoryPerOrder
           from
               orders_history oh
           group by
               oh.id_order ) PreQuery
      join orders_history oh2
         on PreQuery.ID_Order = oh2.id_order
         AND PreQuery.LastID_HistoryPerOrder = oh2.id_history
         AND NOT OH2.id_order_state IN (1, 3)   <<== THIS ELIMINATES 1's amp; 3's from result set
      join Orders                               <<= NOW, anything left after above ^ is joined to orders
         on PreQuery.ID_Order = Orders.ID_Order
  

Просто чтобы повторно отобразить ВАШИ данные… Я отметил последнюю ПОСЛЕДОВАТЕЛЬНОСТЬ (ID_History) для каждого ЗАКАЗА… Это то, что вернет ПРЕДВАРИТЕЛЬНЫЙ запрос…

 id_history  id_order    id_order_state    date_add
  1           1          1                2010-01-01 00:00:00
  2           1          2                2010-01-02 00:00:00   
**3           1          3                2010-01-03 00:00:00

  4           2          2                2010-05-01 00:00:00   
**5           2          3                2011-05-02 00:00:00

  6           3          1                2011-05-03 00:00:00   
**7           3          2                2011-06-01 00:00:00
  

Результатом «предварительного запроса» будет следующее подмножество

 ID_Order   LastID_HistoryPerOrder (ID_History)
1          3  (state=3)  THIS ONE WILL BE SKIPPED IN FINAL RESULT
2          5  (state=3)  THIS ONE WILL BE SKIPPED IN FINAL RESULT
3          7  (state=2)
  

Теперь результат этого затем повторно присоединяется к истории заказов только для этих двух элементов… тем не менее, добавлены критерии для ИСКЛЮЧЕНИЯ 1,3 записей для «состояния заказа».

В этом случае,

    1 would be rejected as its state = 3 (sequence #3), 
   2 would be rejected since its last history is state = 3 (sequence #5).  
   3 would be INCLUDED since its state = 2 (sequence #7)  
  

Наконец, все, что присоединено к orders, приведет к ОДНОМУ идентификатору и будет хорошо сопоставляться с таблицей orders только по Order_ID и получать желаемые результаты.

Другое возможное решение:

 SELECT DISTINCT
    id_order
FROM
    Orders_History OH1
LEFT OUTER JOIN Orders_History OH2 ON
    OH2.id_order = OH1.id_order AND
    OH2.is_order_state IN (1, 3) AND
    OH2.date_add >= OH1.date_add
WHERE
    OH2.id_order IS NULL
  

Я использую «ответ на мой вопрос», потому что мне нужно опубликовать результаты ваших запросов. So.

К сожалению, не все ваши ответы, ребята, работают. Давайте подготовим тестовую среду:

 CREATE TABLE `order_history` (
`id_order_history` int(11) NOT NULL AUTO_INCREMENT,
`id_order` int(11) NOT NULL,
`id_order_state` int(11) NOT NULL,
`date_add` datetime NOT NULL,
PRIMARY KEY (`id_order_history`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin2;

CREATE TABLE `orders` (
`id_order` int(11) NOT NULL AUTO_INCREMENT,
`id_customer` int(11) DEFAULT NULL,
PRIMARY KEY (`id_order`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin2;

INSERT INTO `order_history` 
(`id_order_history`, `id_order`, `id_order_state`,     `date_add`) VALUES 
(1,1,1,'2011-01-01 00:00:00'),
(2,1,2,'2011-01-01 00:10:00'),
(3,1,3,'2011-01-01 00:20:00'),
(4,2,1,'2011-02-01 00:00:00'),
(5,2,2,'2011-02-01 00:25:01'),
(6,2,3,'2011-02-01 00:25:59'),
(7,3,1,'2011-03-01 00:00:01'),
(8,3,2,'2011-03-01 00:00:02'),
(9,3,3,'2011-03-01 00:01:00'),
(10,3,2,'2011-03-02 00:00:01');
COMMIT;

INSERT INTO `orders` (`id_order`, `id_customer`) VALUES 
(1,1),
(2,2),
(3,3),
(4,4),
(5,5),
(6,6),
(7,7);
COMMIT;
  

Теперь давайте выберем последнее / максимальное состояние для каждого заказа, поэтому давайте запустим простой запрос:

  select id_order, max(date_add) as MaxDate
 from `order_history`
 group by id_order
  

это дает нам ПРАВИЛЬНЫЕ результаты, сейчас нет ракетостроения:

 id_order         MaxDate
--------- -------------------
1         2011-01-01 00:20:00  //last order_state=3
2         2011-02-01 00:25:59  //last order_state=3
3         2011-03-02 00:00:01  //last order_state=2
  

Теперь для простоты, чтобы не изменять наши запросы для получения заказов, где последнее состояние не равно 3.

Мы ожидаем получить результат в одну строку с id_order = 3.

Итак, давайте протестируем наши запросы:

ЗАПРОС 1, выполненный RedFilter:

 select oh.id_order, oh.id_order_state, oh.date_add
from (
    select id_order, max(date_add) as MaxDate
    from `order_history`
    where id_order_state not in (3)
    group by id_order
) ohm
inner join `order_history` oh on ohm.id_order = oh.id_order 
and ohm.MaxDate = oh.date_add
  

Результат:

 id_order    id_order_state    date_add
-------------------------------------------------
1           2                 2011-01-01 00:10:00
2           2                 2011-02-01 00:25:01
3           2                 2011-03-02 00:00:01
  

Так что это неправда

ЗАПРОС 2, выполненный Томом Х.:

 SELECT DISTINCT OH1.id_order
FROM order_history OH1
LEFT OUTER JOIN order_history OH2 ON
  OH2.id_order = OH1.id_order AND
  OH2.id_order_state NOT IN (3) AND
  OH2.`id_order_history` >= OH1.`id_order_history`
WHERE
  OH2.id_order IS NULL
  

Результат:

 id_order
--------
1
2
  

Так что это неправда

Любые предложения приветствуются.

Редактировать

Благодаря комментарию Andriy M. у нас есть правильное решение. Это модификация запроса Тома Х. все должно выглядеть следующим образом:

 SELECT DISTINCT
OH1.id_order
FROM
  order_history OH1
LEFT OUTER JOIN order_history OH2 ON
  OH2.id_order = OH1.id_order
AND OH2.date_add > OH1.date_add 
WHERE OH1.id_order_state NOT IN (3) AND OH2.id_order IS NULL
  

РЕДАКТИРОВАТЬ 2:

ЗАПРОС 3, выполненный DRapp:

 select
  distinct orders.`id_order`
from
  ( select oh.id_order,
           max( oh.id_order_history ) LastID_HistoryPerOrder
       from
           order_history oh
       group by
           oh.id_order ) PreQuery
  join order_history oh2
     on PreQuery.id_order = oh2.id_order
     AND PreQuery.LastID_HistoryPerOrder = oh2.id_order_history
     AND NOT oh2.id_order_state IN (1,3)  
join orders                              
  on PreQuery.id_order = orders.id_order
  

Результат:

 id_order
--------
3
  

Итак, это, наконец, правда