Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Запрос с объединением результата  [new]
pinkrose
Member

Откуда:
Сообщений: 56
Здравствуйте!
Опять я пристаю с просьбой о помощи с запросами к MS SQL-серверу.
Я чувствую, что пошла по неправильному пути: получаю одним запросом очень много данных, записываю их в таблицу Excel и потом делаю разбор данных средствами макросов на VBA.
Можно ли как-то средствами SQL сразу получить результаты обработки?

Имеются таблицы "tbl_clients" (база клиентов) и "tbl_sales" (база продаж).
Таблица №1 - tbl_clients
dogovor	client	data_d
95 Смирнов 2014-05-17
101 Иванов 2015-03-02
202 Петров 2015-02-12
303 Сидоров 2015-03-10
здесь поля:
dogovor - номер договора с клиентом (целое число)
client - имя или название клиента (строка)
data_d - дата заключения договора (формат YYYY-MM-DD)

Таблица №2 - tbl_sales
cl_dogovor	tovar	artikul	kolvo	cost	sumplat
101 Колбаса 1050 1 25 25
101 Мыло 2340 3 4 12
101 Веревка 2566 2 7 14
101 Сыр 1111 5 60 300
202 Яблоки 1255 7 12 84
202 Мыло 2340 2 4 8
202 Краска 2988 4 7 28
202 Колбаса 1050 2 25 50
303 Вино 1001 1 50 50
303 Колбаса 1050 3 25 75
303 Мыло 2340 1 4 4
303 Веревка 2566 6 7 42
здесь поля:
cl_dogovor - номер договора, соответствует полю dogovor из таблицы tbl_clients, по нему и находим нужные записи (целое число)
tovar - словесное наименование товара (строка)
artikul - артикул товара по нему ищем нужный товар (целое число)
kolvo - количество купленного товара с таким артикулом (целое число)
cost - цена за одну единицу товара (дробное число)
sumplat - сумма уплаченная за все количество товара с данным артикулом (дробное число)

Нужно отобрать клиентов с действующими договорами заключенными между 1 января и сегодняшним числом, которые покупали товары с артикулами по нескольким условиям, например:
AND artikul > 'x'
AND artikul < 'y'
AND artikul <> 'z'

На самом деле я сильно упростила задачу, там еще куча условий, но это пока не важно. Главное - чтобы заработал основной запрос.

Затем надо получить такой результат:
dogovor	client	data_d		res1			summa1	M_kolvo	M_price	V_kolvo	V_price	M_and_V
101 Иванов 2015-03-02 1050, 2340, 2566, 1111 351 3 3*4=12 2 2*7=14 26
202 Петров 2015-02-12 1255, 2340, 2988, 1050 170 1 1*4=4 6 6*7=42 46
303 Сидоров 2015-03-10 1001, 1050, 2340, 2566 171 NULL NULL NULL NULL NULL

здесь:
res1 - список артикулов купленных товаров (через запятую)
summa1 - сколько всего заплатил клиент client с договором №dogovor
M_kolvo - сколько всего купил мыла
M_price - сколько всего заплатил за мыло
V_kolvo - сколько всего купил веревок
V_price - сколько всего заплатил за веревки
M_and_V - всего заплатил за мыло и веревку

Сейчас мой запрос с обработкой через VBA-макрос примерно такой:
SELECT
	*		-- тут на самом деле перечислены имена только нужных полей
FROM
	tbl_clients AS d,	-- база клиентов
	tbl_sales AS s		-- база продаж
WHERE
	d.dogovor IS NOT NULL		-- договор заключен
	AND d.data_d BETWEEN '2015-01-01' AND '2015-03-15'	-- дата договора
	AND d.dogovor = s.cl_dogovor	-- найти продажи клиентам с найденными договорами
	AND s.artikul > '10'		-- кроме служебных и т.д.
	AND ...				-- разные другие условия
ORDER BY
	d.data_d ASC

Результат запроса
dogovor	client	data_d		tovar	artikul	kolvo	cost	sumplat
101 Иванов 2015-03-02 Колбаса 1050 1 25 25
101 Иванов 2015-03-02 Мыло 2340 3 4 12
101 Иванов 2015-03-02 Веревка 2566 2 7 14
101 Иванов 2015-03-02 Сыр 1111 5 60 300
202 Петров 2015-02-12 Яблоки 1255 7 12 84
202 Петров 2015-02-12 Мыло 2340 2 4 8
202 Петров 2015-02-12 Краска 2988 4 7 28
202 Петров 2015-02-12 Колбаса 1050 2 25 50
303 Сидоров 2015-03-10 Вино 1001 1 50 50
303 Сидоров 2015-03-10 Колбаса 1050 3 25 75
303 Сидоров 2015-03-10 Мыло 2340 1 4 4
303 Сидоров 2015-03-10 Веревка 2566 6 7 42

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

Хотелось бы сделать только один запрос, который возвращал бы сразу объединения полей с артикулами, переменные с результатом подсчета закупок по определенным артикулам и суммы по этим определенным артикулам.

Еще придумала только делать два запроса: первый - по всем артикулам, второй - только по определенным, но думаю, что это неправильно.

Можете помочь?
16 мар 15, 19:34    [17391403]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с объединением результата  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20600
Таблицу продаж группируете по договору, а потом в секции HAVING считаете количество совпавших условий. Если оно равно общему количеству условий - этот договор оставляем. Т.е. типа
select dogovor
from sales
group by dogovor
having sum(articul in (x, y, z)) = 3

Если артикулы могут дублироваться - можете, например, перейти к суммированию по маске.
К этому подзапросу привязываете обе таблицы и получаете всё, что требуется.
16 мар 15, 19:52    [17391441]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с объединением результата  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
pinkrose,
PIVOT однако
select *
from
  (SELECT
	*		-- тут на самом деле перечислены имена только нужных полей
  FROM
	tbl_clients AS d,	-- база клиентов
	tbl_sales AS s		-- база продаж
  WHERE
	d.dogovor IS NOT NULL		-- договор заключен
	AND d.data_d BETWEEN '2015-01-01' AND '2015-03-15'	-- дата договора
	AND d.dogovor = s.cl_dogovor	-- найти продажи клиентам с найденными договорами
	AND s.artikul > '10'		-- кроме служебных и т.д.
	AND ...				-- разные другие условия
  ) a
pivot (
    sum(ваши расчеты) for tovar in ([Колбаса], [Мыло], [Веревка], [Сыр], [Яблоки] и тд.)
) pvt


ну конечно все немного сложнее будет если список товаров заранее не известен, тогда dynamicsql + pivot
16 мар 15, 19:54    [17391446]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с объединением результата  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
WarAnt
pinkrose,

ну конечно все немного сложнее будет если список товаров заранее не известен, тогда dynamicsql + pivot


Для таких целей существуют OLAP-кубы и MDX. Они умеют развертывать как по горизонтали, так и по вертикали.
16 мар 15, 20:47    [17391578]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с объединением результата  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
a_voronin
WarAnt
pinkrose,

ну конечно все немного сложнее будет если список товаров заранее не известен, тогда dynamicsql + pivot


Для таких целей существуют OLAP-кубы и MDX. Они умеют развертывать как по горизонтали, так и по вертикали.


А если данных немного, то сводная таблица в Excel вам в руки. Pivot
16 мар 15, 20:48    [17391583]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с объединением результата  [new]
pinkrose
Member

Откуда:
Сообщений: 56
Akina
Таблицу продаж группируете по договору, а потом в секции HAVING считаете количество совпавших условий....

Увы, но что-то не выходит у меня с группировкой. Пишет ошибку
Сообщение 8120, уровень 16, состояние 1, строка 2
Column 'db1.dbo.tbl_clients.dogovor' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

WarAnt
PIVOT однако

Для меня это что-то новенькое! Где бы почитать про PIVOT (для чайников и с примерами)?

WarAnt
ну конечно все немного сложнее будет если список товаров заранее не известен, тогда dynamicsql + pivot

Все товары и их артикулы известны и хранятся в отдельной таблице.

a_voronin
А если данных немного, то сводная таблица в Excel вам в руки. Pivot

Так я и хочу, уменьшить обработки в Excel, чтобы возложить работу на SQL-сервер.
16 мар 15, 23:10    [17392110]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с объединением результата  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20600
pinkrose
Увы, но что-то не выходит у меня с группировкой. Пишет ошибку

Потому что, получив ответ, не надо выключать мозги и заниматься тупым копипастом! Неужели трудно заметить, что у тебя имя таблицы и поля немного отличаются от того, что у меня нарисовано в шаблоне? а заодно увидеть, что конструкция, использованная в HAVING, требует для MS SQL доработки (конвертации в CASE)...
17 мар 15, 09:02    [17392744]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с объединением результата  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20600
http://sqlfiddle.com/#!6/7f734/4/0
17 мар 15, 09:03    [17392752]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с объединением результата  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4804
pinkrose
a_voronin
А если данных немного, то сводная таблица в Excel вам в руки. Pivot

Так я и хочу, уменьшить обработки в Excel, чтобы возложить работу на SQL-сервер.


Тогда непонятно за чем дело встало http://www.excel-office.ru/diapasontabliziexcel/svodnayatbvexcel
http://advanceduser.ru/microsoft-excel/svodnuju-tablicu-excel.html
17 мар 15, 15:39    [17395253]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с объединением результата  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
a_voronin
WarAnt
pinkrose,

ну конечно все немного сложнее будет если список товаров заранее не известен, тогда dynamicsql + pivot


Для таких целей существуют OLAP-кубы и MDX. Они умеют развертывать как по горизонтали, так и по вертикали.


Разворачивать всю инфраструктуру olap для одного запроса думаю кране не целесообразно, разве что ради спортивного интереса.
17 мар 15, 17:41    [17395930]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с объединением результата  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
pinkrose
Для меня это что-то новенькое! Где бы почитать про PIVOT (для чайников и с примерами)?


как обычно в Боле, там и примеры есть.
pinkrose
Все товары и их артикулы известны и хранятся в отдельной таблице.


Вопрос не в том есть или нет в базе а в том какие из них нужны в конкретном запросе, если все и список не меняется, то делаете один запрос как я показал выше, если список меняется (в зависимости от наличия продаж, новый ассортимент или еще чего) то сначала формируете список одним запросом, а потом динамикой объединяете с пивотом и выполняете через exec.
17 мар 15, 17:45    [17395971]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с объединением результата  [new]
o-o
Guest
WarAnt
a_voronin
Для таких целей существуют OLAP-кубы и MDX. Они умеют развертывать как по горизонтали, так и по вертикали.

Разворачивать всю инфраструктуру olap для одного запроса думаю кране не целесообразно, разве что ради спортивного интереса.

там не просто нецелесообразно, там и спортивно тоже не выйдет,
у ТС Экспресс 2008 R2

pinkrose,
доставьте себе отсюда Microsoft SQL Server 2008 Express with Advanced Services BI Development Studio
и разворачивайте там все что угодно, потом в Эксель сохраните.
не на сервер только ставьте, а на свое ХР.

кстати, у вас же сервер написал, что with Advanced Services, может, уже все и так стоит?
по умолчанию это C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe
17 мар 15, 18:38    [17396183]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить