SQL - найти учетную запись, для которой все заказы выполнены

Я бродил в Google с этим в течение нескольких часов.

Там две таблицы:

AccountTable 
ID | AccountID | OrderID

OrderTable
ID | OrderID | OrderStatus

OrderStatus может иметь значения 1,2,3,4, где 4 означает завершенный статус.

В таблицу заказов добавляется несколько строк по мере обновления статуса заказа. Таким образом, для выполненного заказа у вас будет 4 строки со статусом 1, 2, 3 и 4 соответственно в таблице заказов.

На одном аккаунте может быть несколько заказов.

Я хочу найти аккаунты, для которых выполнены все заказы.

Я нашел до этого много:

select * from AccountTable
INNER JOIN OrderTable
ON AccountTable.OrderID = OrderTable.OrderID
AND OrderTable.OrderStatus = 4

При этом будут выбраны учетные записи, для которых хотя бы один заказ находится в статусе выполненного.

Но это не распространяется на сценарий, когда один заказ выполнен, а другой заказ для той же учетной записи — нет. Тогда я не хочу выбирать аккаунт.

РЕДАКТИРОВАТЬ: я просто хотел показать некоторые данные, чтобы сделать это более понятным:

AccountTable
1 | Name1 | Order1
2 | Name1 | Order2
3 | Name2 | Order3
4 | Name2 | Order4

OrderTable
1 | Order1 | 1
2 | Order1 | 2
3 | Order1 | 3

4 | Order2 | 1
5 | Order2 | 2
6 | Order2 | 3
7 | Order2 | 4

8 | Order3 | 1
9 | Order3 | 2
10 | Order3 | 3
11 | Order3 | 4

12 | Order4 | 1
13 | Order4 | 2
14 | Order4 | 3
15 | Order4 | 4

Я хочу получить только Name2, а НЕ Name1.


sql oracle9i
person redDevil    schedule 29.10.2012    source источник
comment
Мне кажется, что у одной учетной записи может быть только 1 заказ, однако один и тот же заказ может существовать для многих учетных записей.   -  person Chris Gessler    schedule 29.10.2012
comment
нет, у одной учетной записи может быть много заказов, и каждый заказ будет иметь не более 4 строк в таблице заказов.   -  person redDevil    schedule 29.10.2012
comment
Oracle 9i? Действительно? Это уже давно не поддерживается.   -  person a_horse_with_no_name    schedule 29.10.2012
comment
@a_horse_with_no_name, моя последняя миграция с 9i на 11g происходит 1 декабря. Я не могу дождаться.   -  person Ben    schedule 29.10.2012

Ответы (3)


arrow_upward
1
arrow_downward

Предполагая, что ваша бизнес-логика не может быть повреждена, я думаю, вам нужны те учетные записи, количество выполненных заказов (статус == 4) равно количеству инициированных заказов (статус == 1):

  SELECT AccountID
    FROM (SELECT AccountTable.AccountID AS AccountID,
                 SUM(CASE OrderTable.OrderStatus WHEN 1 THEN 1 ELSE 0 END) AS o1,
                 SUM(CASE OrderTable.OrderStatus WHEN 4 THEN 1 ELSE 0 END) AS o4
            FROM AccountTable
            INNER JOIN
                 OrderTable
              ON AccountTable.OrderID = OrderTable.OrderID
        GROUP BY 1) d
   WHERE o1 = o4;

Вы можете ПРИСОЕДИНИТЬСЯ оттуда, чтобы получить все поля из AccountTable.

Кроме того, мне кажется, что ваши таблицы имеют вводящие в заблуждение имена. AccountTable — это таблица Account2Orders, а OrderTable — скорее таблица OrderStagesOrLifeCycle.

person pilcrow    schedule 29.10.2012
comment
Сколько раз люди не понимают, насколько важно дать таблицам базы данных или классам приложений подходящее имя!! - person JotaBe; 29.10.2012
comment
хм ... это не настоящие имена таблиц, на самом деле они довольно длинные и скучные, я просто дал эти имена, чтобы сделать вопрос более ясным ... тем не менее, точка зрения принята. Спасибо - person redDevil; 29.10.2012

arrow_upward
0
arrow_downward

Нет присоединения, подвыбор. Вы также не ищете завершенный заказ - вы ищете учетную запись, на которой нет заказа, который не имеет записи статуса 4. т.е. учетная запись, на которой СУЩЕСТВУЕТ заказ, не имеющий статуса 4.

Проверьте предложение SQL Exists и сгруппируйте его для этого. Сначала вам нужно будет получить запрос, который показывает заказы, не имеющие статуса 4, а затем вы можете добавить его в таблицу заказов.

person TomTom    schedule 29.10.2012

arrow_upward
0
arrow_downward

отредактировано - теперь лучше понять ваши данные:

Select
  c.AccountID
From (
  Select
    a.AccountID,
    o.OrderID,
    Max(o.OrderStatus) As OrderState
  From
    AccountTable a
      Inner Join
    OrderTable o
      On a.OrderID = o.OrderID
  Group By
    a.AccountID,
    o.OrderID
) c
Group By
  c.AccountID
Having
  Min(c.OrderState) = 4;
person Laurence    schedule 29.10.2012
comment
извините, но это по-прежнему дает мне учетную запись, когда у меня один заказ выполнен, а другой не выполнен для этой учетной записи. - person redDevil; 29.10.2012