Блог

Я все еще новичок в SQL и разбираюсь во всей агрегации вложенных запросов, чтобы отобразить некоторые результаты, и искал несколько советов:

Таблицы могут выглядеть примерно так:

 Customer: (custID,  name, address)
Account: (accountID, reward_balance)
Shop: (shopID, name, address)
  

Реляционные таблицы:

 Holds (custID*, accountID*)
With (accountID*, shopID*)
  

Как я могу найти хранилище, которое имеет наименьший reward_balance?
(Информация о клиенте на данный момент не требуется)

Я пытался:

 SELECT accountID AS ACCOUNT_ID, shopID AS SHOP_ID, MIN(reward_balance) AS LOWEST_BALANCE
FROM Account, Shop, With
WHERE With.accountID = Account.accountID
AND With.shopID=Shop.shopID
GROUP BY
Account.accountID,
Shop.shopID
ORDER BY MIN(reward_balance);
  

Это работает так, как не предназначено:

 ACCOUNT_ID | SHOP_ID | LOWEST_BALANCE
 1         |   1     |   10
 2         |   2     |   40
 3         |   3     |   100
 4         |   4     |   1000
 5         |   4     |   5000
  

Как вы можете видеть, Shop_ID 4 на самом деле имеет баланс 6000 (1000 5000), поскольку в нем зарегистрированы два клиента. Я думаю, мне нужно суммировать наименьший баланс магазинов на основе их баланса и отображать его с низкого на высокий.

Я пытался объединить данные перед отображением, но здесь я отклеился:

 SELECT shopID AS SHOP_ID, MIN(reward_balance) AS LOWEST_BALANCE
FROM (SELECT accountID, shopID, SUM(reward_balance) 
     FROM Account, Shop, With
     WHERE 
         With.accountID = Account.accountID
         AND With.shopID=Shop.shopID
         GROUP BY
             Account.accountID,
             Shop.shopID;
  

Когда я запускаю что-то вроде этого оператора, я получаю ошибку недопустимого идентификатора.

 Error at Command Line : 1 Column : 24
Error report -
SQL Error: ORA-00904: "REWARD_BALANCE": invalid identifier
00904. 00000 -  "%s: invalid identifier"
  

Итак, я подумал, что, возможно, мое условие соединения неверно, а агрегированная сортировка неверна, и был бы очень признателен за любые общие советы.

Спасибо за долгое чтение!

Подходите к этой проблеме по одному шагу за раз.

Мы собираемся предположить (и нам, вероятно, следует это проверить), что по наименьшему reward_balance это относится к сумме всех reward_balance, связанных с магазином. И мы не просто ищем магазин с наименьшим индивидуальным балансом вознаграждений.

Сначала получите все отдельные «reward_balance» для каждого магазина. Похоже, что запрос должен включать три таблицы…

 SELECT s.shop_id
     , a.reward_balance
  FROM `shop` s
  LEFT
  JOIN `with` w
    ON w.shop_id = s.shop_id 
  LEFT
  JOIN `account` a
    ON a.account_id = w.account_id
  

Это даст нам подробные строки для каждого магазина вместе с отдельными суммами reward_balance, связанными с магазином, если таковые имеются. (Мы используем внешние соединения для этого запроса, потому что мы не видим никакой гарантии, что магазины будут связаны хотя бы с одной учетной записью. Даже если это верно для данного варианта использования, это не всегда верно в более общем случае.)

Как только у нас будут отдельные суммы, следующим шагом будет их суммирование для каждого магазина. Мы можем сделать это, используя GROUP BY предложение и SUM() агрегат.

 SELECT s.shop_id
     , SUM(a.reward_balance) AS tot_reward_balance
  FROM `shop` s
  LEFT
  JOIN `with` w
    ON w.shop_id = s.shop_id 
  LEFT
  JOIN `account` a
    ON a.account_id = w.account_id
 GROUP BY s.shop_id
  

На этом этапе с помощью MySQL мы могли бы добавить ORDER BY предложение для упорядочивания строк в порядке возрастания tot_reward_balance и добавить LIMIT 1 предложение, если мы хотим вернуть только одну строку. Мы также можем обработать случай, когда tot_reward_balance равно НУЛЮ, назначив ноль вместо нуля.

 SELECT s.shop_id
     , IFNULL(SUM(a.reward_balance),0) AS tot_reward_balance
  FROM `shop` s
  LEFT
  JOIN `with` w
    ON w.shop_id = s.shop_id 
  LEFT
  JOIN `account` a
    ON a.account_id = w.account_id
 GROUP BY s.shop_id
 ORDER BY tot_reward_amount ASC, s.shop_id ASC
 LIMIT 1
  

Если есть два (или более) магазина с одинаковым наименьшим значением tot_reward_amount , этот запрос возвращает только один из этих магазинов.

В Oracle нет предложения LIMIT, подобного MySQL, но мы можем получить эквивалентный результат, используя аналитическую функцию (которая недоступна в MySQL). Мы также заменяем функцию MySQL IFNULL() эквивалентной функцией Oracle NVL()…

 SELECT v.shop_id
     , v.tot_reward_balance
     , ROW_NUMBER() OVER (ORDER BY v.tot_reward_balance ASC, v.shop_id ASC) AS rn
  FROM ( 
         SELECT s.shop_id
              , NVL(SUM(a.reward_balance),0) AS tot_reward_balance
           FROM shop s
           LEFT
           JOIN with w
             ON w.shop_id = s.shop_id 
           LEFT
           JOIN account a
             ON a.account_id = w.account_id
          GROUP BY s.shop_id
       ) v
HAVING rn = 1
  

Как и запрос MySQL, это возвращает не более одной строки, даже если два или более магазинов имеют одинаковую «наименьшую» сумму reward_balance .

Если мы хотим вернуть все магазины с наименьшим tot_reward_balance, нам нужно использовать несколько иной подход.

Лучший подход к построению запросов — поэтапное уточнение; в этом случае начните с получения всех отдельных значений reward_amount для каждого магазина. Следующим шагом является объединение отдельного значения reward_amount в итоговое значение. Следующие шаги — выбрать строку (строки) с наименьшим общим вознаграждением_amount.

В SQL Server вы можете попробовать использовать CTE:

   ;with cte_minvalue as
    (
     select rank() over (order by Sum_Balance) as RowRank,
     ShopId,
     Sum_Balance
      from (SELECT Shop.shopID, SUM(reward_balance) AS Sum_Balance 
         FROM 
             With
             JOIN Shop ON With.ShopId = Shop.ShopId
             JOIN Account ON With.AccountId = Account.AccountId
         GROUP BY
            Shop.shopID)ShopSum
      )
      select ShopId, Sum_Balance from cte_minvalue where RowRank = 1