Общий объем продаж каждого региона в базе данных Northwind SQL Server?

Как я могу рассчитать общий объем продаж каждого региона в Northwind базе данных SQL Server? Я выполнил следующие запросы:

1)

 select sum( od.Quantity * od.UnitPrice ), v.rid from  
         Orders o , [Order Details] od  , (select et.EmployeeID eid, r.RegionID rid from  region r, territories t , EmployeeTerritories et  
                 where r.RegionID = t.RegionID and et.TerritoryID = t.TerritoryID    ) v
         where od.OrderID = o.OrderID and v.eid = o.EmployeeID
         group by v.rid;

2)

select sum(b) from
 (select sum(aa.UnitPrice * aa.Quantity)   b
 from region r, territories t , EmployeeTerritories et ,
    (select o.EmployeeID , od.Quantity , od.UnitPrice from  
         Orders o , [Order Details] od
        where od.OrderID = o.OrderID ) aa
 where
    t.regionid = r.regionid and 
    et.TerritoryID = t.TerritoryID and 
    et.EmployeeID  = aa.EmployeeID
    group by r.RegionID) x;

3)

select sum (Quantity * UnitPrice) from [Order Details];

Если вы запустите эти запросы, вы увидите, что третья сумма не равна первой! Это значит, что у нас есть повторные рекорды в каждом регионе!


sql sql-server northwind
person bahar    schedule 29.07.2013    source источник
comment
используйте внутреннее соединение, чтобы избежать дублирования   -  person developerCoder    schedule 29.07.2013

Ответы (3)


arrow_upward
1
arrow_downward

В таблице EmployeeTerritories у одного сотрудника может быть больше территорий, поэтому при объединении в нее вы умножаете все записи для каждого сотрудника, даже если все территории каждого сотрудника принадлежат одному региону.

Вы должны использовать подзапрос с GROUP BY, чтобы сначала найти RegionID для каждого сотрудника, а затем присоединиться к нему вместо EmployeeTerritories

Что-то вроде этого для вашего запроса A

WITH CTE_EmpRegion AS 
(
    SELECT EmployeeID, MAX(RegionID) RegionID
    FROM dbo.EmployeeTerritories et 
        INNER JOIN territories t ON t.TerritoryID = et.TerritoryID
    GROUP BY EmployeeID
)
SELECT RegionID, SUM(Quantity * UnitPrice) 
FROM Orders o 
    INNER JOIN [Order Details] od ON o.OrderID = od.OrderID
    INNER JOIN CTE_EmpRegion er ON o.EmployeeID = er.EmployeeID
GROUP BY RegionID

Также не следует использовать старые style JOINS, которые устарели уже более 20 лет.

person Nenad Zivkovic    schedule 29.07.2013

arrow_upward
0
arrow_downward

Без понимания ваших таблиц трудно дать вам четкий ответ, но ваши запросы выглядят так, как будто их можно очистить. Посмотрите на ВНУТРЕННИЕ СОЕДИНЕНИЯ. Они значительно упростят вашу жизнь. Неявные соединения трудно читать и отлаживать (что вы делаете со скобками), в то время как явные соединения чрезвычайно легко читаются. Учиться тоже не долго:

http://www.w3schools.com/sql/sql_join_inner.asp

Если вы отслеживаете продажи по регионам, ваша таблица должна быть одинаковой, чтобы предотвратить появление дубликатов. В противном случае у вас быстро будет кошмар на ваших руках. Кроме того, если единственной переменной является регион, вы сможете использовать тот же запрос для получения информации о регионе. Что-то вроде этого должно быть всем, что вам нужно:

SELECT SUM(od.Quantity * od.UnitPrice) total_sales FROM [Order Details] od
INNER JOIN Orders o ON o.orderID = od.orderID
INNER JOIN EmployeeTerritories et ON et.employeeID=od.employeeID
INNER JOIN Regions t ON t.regionID = et.regionID
INNER JOIN Territories t ON t.regionID = r.regionID
WHERE t.regionID=?

Опять же, я не видел вашу таблицу, поэтому мне пришлось поверить, что у вас есть идентификатор сотрудника в вашем списке сведений о заказе. Вам нужен способ присоединиться к этой таблице, которая ссылается на таблицу, которую вы уже установили в своей БД. Вот почему я предложил ВНУТРЕННИЕ СОЕДИНЕНИЯ... Они позволят вам легче увидеть структуру таблицы и ускорить отладку. Такая таблица будет работать, если вы введете новый идентификатор региона. Так просто.

Дайте мне знать, как это работает, или поделитесь дополнительной информацией. Спасибо!

person smcjones    schedule 29.07.2013

arrow_upward
0
arrow_downward

Вы можете использовать CTE (Common Table Expression) для решения этой проблемы.

USE Northwind;
GO 
WITH    EmployeeSales
          AS ( SELECT   e.EmployeeID ,
                        e.LastName ,
                        SUM(od.Quantity * od.UnitPrice) ESales
               FROM     dbo.Employees AS e
                        INNER JOIN dbo.Orders AS o ON e.EmployeeID = o.EmployeeID
                        INNER JOIN dbo.[Order Details] AS od ON o.OrderID = od.OrderID
               GROUP BY e.EmployeeID ,
                        e.LastName
             ),
        EmployeeRegion
          AS ( SELECT DISTINCT
                        EmployeeID ,
                        r.RegionID ,
                        RegionDescription
               FROM     dbo.Region AS r
                        INNER JOIN dbo.Territories AS t ON r.RegionID = t.RegionID
                        INNER JOIN dbo.EmployeeTerritories AS et ON t.TerritoryID = et.TerritoryID
             )
    SELECT  EmployeeRegion.RegionID ,
            EmployeeRegion.RegionDescription ,
            SUM(EmployeeSales.ESales) RegionTotalSale
    FROM    EmployeeSales
            INNER JOIN EmployeeRegion ON EmployeeSales.EmployeeID = EmployeeRegion.EmployeeID
    GROUP BY EmployeeRegion.RegionID ,
            EmployeeRegion.RegionDescription
  1. EmployeeSales: общий объем продаж для каждого сотрудника.
  2. EmployeeRegion: найдите regionID для каждого сотрудника.
  3. Наконец, сгруппируйте по RegionID и используйте функцию SUM(), чтобы найти общий объем продаж в каждом регионе. Результат
person mohammad Abouhamzeh    schedule 04.03.2016