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

Откуда: Москва
Сообщений: 8
Доброго времени суток : )

Есть заполненная данными таблица работ по категориям.

WorksNumber WorksType WorksName WorksQuantity WorksPrice WorksSum
1 Категория1Работа1 1 12 12
2 Категория1Работа2 1 17 852 17 852
3 Категория2Работа3 23 33 659 774 157
4 Категория2Работа4 12 1 995 23 940
5 Категория2Работа5 1 122 122


Требуется сделать запрос результатом которого будет таблица с итогами по категориям причем желательно без столбца категорий если это вообще возможно. Желаемый результат примерно такой:

WorksNumber WorksName WorksQuantity WorksPrice WorksSum
Категория1
1 Работа1 1 12 12
2 Работа2 1 17 852 17 852
Подитог: 17 864
Категория2
3 Работа3 23 33 659 774 157
4 Работа4 12 1 995 23 940
5 Работа5 1 122 122
Подитог: 798 219
Итого: 816 083


Заранее благодарен за советы

+ В процессе изучения этого вопроса, чтения форумов и тд. лучшее к чему я смог придти спрячем тут

Это мягко говоря не совсем то что надо, да и я не понимаю как вывести больше столбцов не пихая их в GROUP BY
SELECT      CASE GROUPING(WorksType)
                  WHEN 1 THEN 'Общий'
                  ELSE WorksType END AS 'Категория',
            CASE GROUPING(WorksName)
                  WHEN 1 THEN 'Подитог'
                  ELSE WorksName END AS 'Наименование',
            SUM(WorksSum)  AS 'Сумма'
FROM        tblWorks
WHERE		WorksOrderID = 20021
GROUP BY    WorksType, WorksName WITH ROLLUP


WorksType WorksName WorksSum
Категория1Работа112
Категория1Работа217 852
Категория1Подитог: 17 864
Категория2Работа3774 157
Категория2Работа423 940
Категория2Работа5122
Категория2Подитог: 798 219
Общий Подитог: 816 083

10 мар 16, 21:21    [18917483]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с промежуточными итогами по категориям  [new]
Glory
Member

Откуда:
Сообщений: 104751
kostazs
процессе изучения этого вопроса, чтения форумов и тд. лучшее к чему я смог придти спрячем тут

И чем вам не подходит полученный результат ?
Форматировать вывод должно клиентское приложение
11 мар 16, 09:17    [18918348]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с промежуточными итогами по категориям  [new]
kostazs
Member

Откуда: Москва
Сообщений: 8
Glory,

В предложенном варианте я не пойму как вывести остальные столбцы таблицы.
Про формат вывода клиентским приложением тоже не совсем понятно.. я работаю с программой "Учет клиентов" которая по указанному запросу может вставить полученную таблицу в файл word по шаблону.
11 мар 16, 09:47    [18918461]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с промежуточными итогами по категориям  [new]
Glory
Member

Откуда:
Сообщений: 104751
kostazs
В предложенном варианте я не пойму как вывести остальные столбцы таблицы.

Т.е. такой вопрос, как в одном поле WorksPrice, например, будет сразу хранится и число и строка Подитог:, вас вообще не волнует ?
11 мар 16, 09:56    [18918505]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с промежуточными итогами по категориям  [new]
Glory
Member

Откуда:
Сообщений: 104751
kostazs
Про формат вывода клиентским приложением тоже не совсем понятно.. я работаю с программой "Учет клиентов" которая по указанному запросу может вставить полученную таблицу в файл word по шаблон

Ну так вот пусть разработчки программы "Учет клиентов" напишет модуль, который выведет вам рузультат запроса в нужном виде
11 мар 16, 09:57    [18918515]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с промежуточными итогами по категориям  [new]
kostazs
Member

Откуда: Москва
Сообщений: 8
Glory,

Я думаю я понял, то что вы пытаетесь донести) видимо ошибочно полагал что такое представление можно получить через запрос.
11 мар 16, 13:08    [18919479]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с промежуточными итогами по категориям  [new]
Konst_One
Member

Откуда:
Сообщений: 11681
declare @t table (WorksNumber int,	WorksType varchar(50),	WorksName varchar(50),	WorksQuantity int, 	WorksPrice money,	WorksSum money)


insert into @t (WorksNumber, WorksType, WorksName, WorksQuantity, WorksPrice, WorksSum)
values
(1,	'Категория1',	'Работа1',	1,	12,	12),
(2,	'Категория1',	'Работа2',	1,	17852,	17852),
(3,	'Категория2',	'Работа3',	23,	33659,	774157),
(4,	'Категория2',	'Работа4',	12,	1995,	23940),
(5,	'Категория2',	'Работа5',	1,	122, 122)

select 
	T.WorksType,
	T.WorksNumber,
	T.WorksName,
	T.WorksQuantity,
	T.WorksPrice,
	T.WorksSum
from @t T
ORDER BY T.WorksType, T.WorksName
COMPUTE SUM(T.WorksSum) BY T.WorksType
COMPUTE SUM(T.WorksSum)


но всё равно потребуется в клиентском приложении красиво это показать
11 мар 16, 14:10    [18919905]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с промежуточными итогами по категориям  [new]
kostazs
Member

Откуда: Москва
Сообщений: 8
Konst_One,

Да, я понял, формат вывода уже за приложением. Спасибо
11 мар 16, 14:20    [18919980]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с промежуточными итогами по категориям  [new]
Wlr-l
Member

Откуда:
Сообщений: 641
kostazs,

Конечно, можно выбрать все записи, и всю обработку сделать на клиенте. Но зачем делать то, с чем сервер может легко справиться?
Вы были на правильном пути. Сделаем следующий шаг:

with A as (
  select *
    from (values (1, 'Категория1', 'Работа1',  1,    12,     12),
                 (2, 'Категория1', 'Работа2',  1, 17852,  17852),
                 (3, 'Категория2', 'Работа3', 23, 33659, 774157),
                 (4, 'Категория2', 'Работа4', 12,  1995,  23940),
                 (5, 'Категория2', 'Работа5',  1,   122,    122)
         ) as T(WorksNumber, WorksType, WorksName, WorksQuantity, WorksPrice, WorksSum)
)
--select * from A;
, B as (
  select WorksNumber,WorksType, WorksName, Max(WorksQuantity) wq, SUM(WorksPrice) wp, SUM(WorksSum) ws
         ,grouping(WorksType) b
         ,row_number() over (partition by WorksType order by grouping_id(WorksType, WorksName),grouping(WorksNumber) desc, WorksNumber) rn
    from A
  group by rollup(WorksType, WorksName, WorksNumber)
  having grouping(WorksNumber)=0 or grouping_id(WorksType, WorksName)>0
)

select WorksNumber
      ,case when rn=1 then WorksType else '' end as WorksType
      ,WorksName
      ,case when WorksName is not null then wq else null end as WorksQuantity
      ,wp
      ,ws
 from B
order by b

Результат:
WorksNumber	WorksType	WorksName	WorksQuantity	wp	ws
1 Категория1 Работа1 1 12 12
2 Работа2 1 17852 17852
NULL NULL NULL 17864 17864
3 Категория2 Работа3 23 33659 774157
4 Работа4 12 1995 23940
5 Работа5 1 122 122
NULL NULL NULL 35776 798219
NULL NULL NULL NULL 53640 816083


COMPUTE в запросах уже не работает!
11 мар 16, 15:46    [18920498]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с промежуточными итогами по категориям  [new]
Konst_One
Member

Откуда:
Сообщений: 11681
у нас пока 2008 r2 и compute прекрасно работает.
всё равно это не дело сервера, для этого есть кубы MSAS BI, системы отчётности SSRS и тд и тп
11 мар 16, 16:01    [18920592]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с промежуточными итогами по категориям  [new]
Wlr-l
Member

Откуда:
Сообщений: 641
Konst_One,

а у нас уже не работает.

Конечно, можно из-за пары простеньких отчетов установить кубы MSAS BI, системы отчётности SSRS и тд и тп...
А можно и несложным запросом...
11 мар 16, 16:08    [18920631]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с промежуточными итогами по категориям  [new]
kostazs
Member

Откуда: Москва
Сообщений: 8
Wlr-l,

Спасибо, этот результат уже выглядит вполне съедобно. Начал разбираться, но вот непонятно.. а что если надо вывести больше разных полей ? Скажем добавить ед. изм. для количества или некую характеристику работы.. мне почему-то казалось, что в подитогах ничего сложного не должно быть и ничего не стоит просто вывести подитог по категориям для любого рода таблицы, оказалось все куда сложнее, чем я мог представить :D
11 мар 16, 16:18    [18920682]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с промежуточными итогами по категориям  [new]
Konst_One
Member

Откуда:
Сообщений: 11681
там же CTE, добавляйте в A
11 мар 16, 16:19    [18920692]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с промежуточными итогами по категориям  [new]
Wlr-l
Member

Откуда:
Сообщений: 641
kostazs,
Когда-то мне тоже казалось все очень сложным, потом это проходит.
Можно сделать группировку с подитогами и итогами, а потом соединить результат с другими столбцами исходной таблицы.

Таблица А всего лишь имитирует ваши данные.
11 мар 16, 16:28    [18920742]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с промежуточными итогами по категориям  [new]
kostazs
Member

Откуда: Москва
Сообщений: 8
Wlr-l,

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

Единственное, что смог добавить столбец один, но пришлось повозиться и я не уверен что сделал правильно

+ код тут:
Пришлось добавить много куда, слишком много изменений ради одного столбца..
with A as (
  select *
    from (values (1, 'Категория1', 'Работа1',  1, 'шт', 12,    12),
                 (2, 'Категория1', 'Работа2',  1, 'шт', 17852, 17852),
                 (3, 'Категория2', 'Работа3', 23, 'шт', 33659, 774157),
                 (4, 'Категория2', 'Работа4', 12, 'шт', 1995,  23940),
                 (5, 'Категория2', 'Работа5',  1, 'шт', 122,   122)
         ) as T(WorksNumber, WorksType, WorksName, WorksQuantity, WorksShtuk, WorksPrice, WorksSum)
)
--select * from A;
, B as (
	select WorksNumber, WorksType, WorksName, Max(WorksQuantity) wq, WorksShtuk, SUM(WorksPrice) wp, SUM(WorksSum) ws
			 ,grouping(WorksType) b
			 ,row_number() over (partition by WorksType order by grouping_id(WorksType, WorksName),grouping(WorksNumber) desc, WorksNumber) rn
		from A
	  group by rollup(WorksType, WorksName, WorksShtuk, WorksNumber)
	  having grouping(WorksNumber)=0 or grouping_id(WorksType, WorksName)>0
)

select WorksNumber
      ,case when rn=1 then WorksType else null end as WorksType
      ,WorksName
      ,case when WorksName is not null then wq else null end as WorksQuantity
      ,case when WorksName is not null then WorksShtuk end as Type
      ,case when WorksName is not null then wp else null end as WorksPrice
      ,ws
 from B
order by b

12 мар 16, 16:33    [18923629]     Ответить | Цитировать Сообщить модератору
 Re: Запрос с промежуточными итогами по категориям  [new]
invm
Member

Откуда: Москва
Сообщений: 9915
+ Тупое решение "в лоб"
declare @t table (WorksNumber int,	WorksType varchar(50),	WorksName varchar(50),	WorksQuantity int, 	WorksPrice money,	WorksSum money);


insert into @t (WorksNumber, WorksType, WorksName, WorksQuantity, WorksPrice, WorksSum)
values
(1,	'Категория1',	'Работа1',	1,	12,	12),
(2,	'Категория1',	'Работа2',	1,	17852,	17852),
(3,	'Категория2',	'Работа3',	23,	33659,	774157),
(4,	'Категория2',	'Работа4',	12,	1995,	23940),
(5,	'Категория2',	'Работа5',	1,	122, 122);

with
 a as (select cast(0x7fffffff as int) as l1, null as l2, sum(WorksSum) as WorksSum from @t),
 b as (select row_number() over (order by WorksType) as l1, cast(0x7fffffff as int) as l2, sum(WorksSum) as WorksSum from @t group by WorksType),
 c as (select distinct dense_rank() over (order by WorksType) as l1, -1 as l2, WorksType from @t),
 d as (
  select
   dense_rank() over (order by WorksType) as l1,
   WorksNumber as l2,
   cast(WorksNumber as sql_variant) as WorksNumber,
   WorksName,
   cast(WorksQuantity as sql_variant) as WorksQuantity,
   cast(WorksPrice as sql_variant) as WorksPrice,
   cast(WorksSum as sql_variant) as WorksSum
  from
   @t

  union all

  select
   l1, l2, '', '', '', 'Итог:', cast(WorksSum as varchar(20))
  from
   a

  union all

  select
   l1, l2, '', '', '', 'Подитог:', cast(WorksSum as varchar(20))
  from
   b

  union all

  select
   l1, l2, '', WorksType, '', '', ''
  from
   c
 )
select
 WorksNumber, WorksName, WorksQuantity, WorksPrice, WorksSum
from
 d
order by
 l1, l2;
12 мар 16, 18:07    [18923829]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить