sql — запрос продавцов по территории — объединить несколько операторов с предложением WITH

У нас есть отношения «территория/продавец», где: на каждой территории (TERR) может быть несколько продавцов (SALM), но у каждого продавца может быть только одна территория.

Пример:

Territory      Salesman   Sales$ Period   
    1             100      999     1
    1             100      999     2
    1             200      999     1 
    1             200      999     2    
    2             300      999     1
    2             300      999     2
    3             400      999     1
    3             400      999     2

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

  • показывает территорию,
  • количество продавцов для этой территории (** ПОЯСНЕНИЕ: ** это общее количество уникальных кодов продавцов, совершивших продажи на этой территории) и
  • список номеров продавцов в том же файле.

пример:

Territory # of Salesmen   Salesman   
--------- -------------   --------   
1             2            100       
1             2            200       
2             1            300       
3             1            400

SQL:

// First I get a list of territories and number of salesman in each territory
With tmpFile as (Select TERR, SALM, count(*) 
   FROM FILE
   GROUP BY TERR, SALM 
   ORDER BY TERR, SALM   
) 
SELECT tmpFile.TERR, count(*) FROM tmpFile GROUP BY          
tmpFile.TERR ORDER BY tmpFile.TERR

// Next step is to get a list of all salesmen in a territory
Select TERR, SALM
   FROM FILE
   GROUP BY TERR, SALM 
   ORDER BY TERR, SALM
)

// Последним шагом будет объединение двух предыдущих шагов. Я хотел бы объединить все три шага в одно утверждение, если это возможно. Есть ли более эффективный способ сделать это. Я использую IBM db/2 SQL.


sql db2 with-statement
person Mustapha George    schedule 21.08.2012    source источник
comment
Вы также можете пометить вопрос, чтобы указать, какую платформу вы используете: мейнфрейм, ibm-midrange или db2-luw.   -  person WarrenT    schedule 22.08.2012

Ответы (3)


arrow_upward
2
arrow_downward

Хотя я хотел бы использовать версию инструкции OLAP, моя версия DB2 (V6R1) не поддерживает функцию COUNT() в этом контексте (boohoo). Тем не менее, вот версия, которая действительно работает и должна работать на любой другой СУБД (я думаю):

SELECT DISTINCT terr, salm, salesmen
FROM File -- I really hope that's not your file name - it's a reserved word
JOIN (SELECT terr territory, COUNT(DISTINCT salm) salesmen
      FROM File
      GROUP BY terr) Salesmen_Per_Territory
ON territory = terr

Обратите внимание на DISTINCT внутри COUNT(), так как это мешает подсчету продавцов за каждый период.

person Clockwork-Muse    schedule 21.08.2012

arrow_upward
0
arrow_downward

Почему бы не сделать это в одном запросе?

SELECT Territory, SalesMan, COUNT(*) as [No. of Salesman]
FROM tableName
GROUP BY Territory, SalesMan

Они на другом столе? например, TERR и SALM?

SELECT  a.Territory, b.SalesMan, Count(b.Territory) as [No. of Salesman]
FROM    Terr a 
            INNER JOIN SALM b
                on a.Territory = b.Territory
GROUP BY a.Territory, b.SalesMan
person John Woo    schedule 21.08.2012
comment
все данные из одной таблицы - person Mustapha George; 21.08.2012
comment
в этой таблице есть сводка транзакций по территории/продавцу/периоду. Я хочу уточнить это до Terr/Salesman, а затем показать количество продавцов на территории в каждой строке. - person Mustapha George; 21.08.2012

arrow_upward
0
arrow_downward

Я бы сказал, что вы на правильном пути, используя общее табличное выражение [aka. WITH], но это можно легко и эффективно сделать в одном выражении.

WITH t as
(SELECT terr, count(distinct salm) as slspeople
   FROM yourfile
   WHERE salesamt > 0
   GROUP BY terr
)
SELECT DISTINCT s.terr, t.slspeople, s.salm
  FROM yourfile as s
  JOIN    t       using (terr)
  WHERE salesamt > 0
  ORDER BY s.terr, s.salm

Предложение using (terr) является сокращением для on s.terr = t.terr, где совпадающие столбцы должны иметь одинаковые имена в обеих таблицах.

Я добавил WHERE salesamt > 0 только потому, что одним из ваших требований к уточнению было указать только тех продавцов, у которых есть продажи. Если это не применимо, выньте это, конечно.

Может быть хорошей идеей взять различные предоставленные ответы и проанализировать их с помощью Наглядного объяснения. Это ценный инструмент, с помощью которого вы можете попробовать разные варианты и посмотреть, какой из них работает лучше. Я использую его на IBM i, но думаю, что он существует и на двух других платформах.

person WarrenT    schedule 22.08.2012
comment
еще один отличный пример! столько силы в нескольких коротких предложениях! - person Mustapha George; 23.08.2012
comment
Ответы, которые вы считаете достойными похвалы, вы можете проголосовать (щелкните треугольник вверх). Это награждает автора дополнительными очками репутации. Это применимо независимо от того, является ли это вашим вопросом или нет, а также относится к вопросам. - person WarrenT; 24.08.2012
comment
Спасибо, что указали на мою ошибку. Я удалил свой ответ. Оглядываясь назад, кажется, что комментарии Мустафы все время намекали на одно и то же, а я не понял намека. - person Andriy M; 24.08.2012