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

Откуда:
Сообщений: 4
Всем здравствуйте.
Пишу программу для подсчета итогов, разобрался с sum() и двойной сортировкой, но сделать нужную сортировку всё равно не получается.

Есть таблица вида:
"PRODUCT""AGE""COMPANY""SALARY"
"Appple"32"Ahmedabad"2000
"Banana"25"Delhi"1500
"Banana"23"Kota"2000
"Orange"25"Mumbai"6500
"Grapes"27"Bhopal"2500
"Orange"22"MP"4500
"Orange"24"Indore"10000


Нужно получить таблицу вида:
"PRODUCT""AGE""COMPANY""SALARY"
"Orange"21000
"Orange"24"Indore"10000
"Orange"25"Mumbai"6500
"Orange"22"MP"4500
"Banana"3500
"Banana"23"Kota"2000
"Banana"25"Delhi"1500
"Grapes"2500
"Grapes"27"Bhopal"2500
"Appple"2000
"Appple"32"Ahmedabad"2000


Т. е. считаем итог по каждому товару, максимальная итоговая сумма идет первой, после нее список записей с таким же товаром сортированные по убыванию, потом следующий итог с записями и т.д. Если делать двойную сортировку то получается список товаров по алфавиту внутри которого сортировка по итогу, а нужны группы товаров с сортировкой по итогу. Итог есть отсортированный в отдельной таблице. Пробовал делать цикл for по товарам, на каждый товар insert oreder by, но так очень долго. Реально это вообще сделать средствами SQL?
База CSV-файл (выгрузка из 1с), общение идет через "Microsoft.ACE.OLEDB.12.0".
11 май 18, 17:35    [21403830]     Ответить | Цитировать Сообщить модератору
 Re: Сложная сортировка для подсчёта итогов  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Goanfed, ROLLUP
11 май 18, 17:48    [21403860]     Ответить | Цитировать Сообщить модератору
 Re: Сложная сортировка для подсчёта итогов  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5112
declare @t table (PRODUCT varchar(10), AGE tinyint, COMPANY varchar(10), SALARY smallint)

insert @t
(PRODUCT, AGE, COMPANY, SALARY)
values
	('Appple',32,'Ahmedabad',2000),
	('Banana',25,'Delhi',1500),
	('Banana',23,'Kota',2000),
	('Orange',25,'Mumbai',6500),
	('Grapes',27,'Bhopal',2500),
	('Orange',22,'MP',4500),
	('Orange',24,'Indore',10000)

select
	PRODUCT, AGE, COMPANY, SALARY
from
	(
	select
		PRODUCT, null as AGE, null as COMPANY, sum(SALARY) as SALARY, row_number() over(order by sum(SALARY) desc) as rn
	from
		@t
	group by
		PRODUCT
	union all
	select
		PRODUCT, AGE, COMPANY, SALARY, null as rn
	from
		@t
	)q
order by
	max(rn) over(partition by PRODUCT), SALARY desc
11 май 18, 18:28    [21403928]     Ответить | Цитировать Сообщить модератору
 Re: Сложная сортировка для подсчёта итогов  [new]
nullin
Member

Откуда: pullin
Сообщений: 140
Goanfed,

SELECT PRODUCT, AGE, COMPANY, SUM(SALARY)
FROM YOUR_TABLE
GROUP BY grouping sets((PRODUCT), (PRODUCT, AGE, COMPANY))
--ORDER BY PRODUCT DESC, SUM(SALARY) DESC
12 май 18, 14:27    [21404947]     Ответить | Цитировать Сообщить модератору
 Re: Сложная сортировка для подсчёта итогов  [new]
nullin
Member

Откуда: pullin
Сообщений: 140
Goanfed, Если вдруг еще и тотал понадобится:
GROUP BY grouping sets((PRODUCT, AGE, COMPANY), (PRODUCT), ())
12 май 18, 14:31    [21404950]     Ответить | Цитировать Сообщить модератору
 Re: Сложная сортировка для подсчёта итогов  [new]
nullin
Member

Откуда: pullin
Сообщений: 140
nullin, в придачуЭквиваленты GROUPING SETS
12 май 18, 14:33    [21404952]     Ответить | Цитировать Сообщить модератору
 Re: Сложная сортировка для подсчёта итогов  [new]
Goanfed
Member

Откуда:
Сообщений: 4
Спасибо, смысл понятен, но сделать не получилось - ошибка "Missing operator" на
order by max(rn) over(partition by PRODUCT)...
и
GROUP BY grouping sets((PRODUCT, AGE, COMPANY)...

SQL Compact вообще не поддерживает команды "over", "grouping sets" как я понял. Придется делать переброс в нормальную БД и работать уже в ней :(
12 май 18, 22:56    [21405789]     Ответить | Цитировать Сообщить модератору
 Re: Сложная сортировка для подсчёта итогов  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5112
Goanfed
Придется делать переброс в нормальную БД и работать уже в ней :(
достаточно знать предмет с которым работаете...

Goanfed
Итог есть отсортированный в отдельной таблице.
declare @t table (PRODUCT varchar(10), AGE tinyint, COMPANY varchar(10), SALARY smallint)
declare @Q table (PRODUCT varchar(10), AGE tinyint, COMPANY varchar(10), S_SALARY smallint)

insert @t
(PRODUCT, AGE, COMPANY, SALARY)
values
	('Appple',32,'Ahmedabad',2000),
	('Banana',25,'Delhi',1500),
	('Banana',23,'Kota',2000),
	('Orange',25,'Mumbai',6500),
	('Grapes',27,'Bhopal',2500),
	('Orange',22,'MP',4500),
	('Orange',24,'Indore',10000)

insert @Q
(PRODUCT, AGE, COMPANY, S_SALARY)
select
	PRODUCT, null as AGE, null as COMPANY, sum(SALARY) as S_SALARY
from
	@t
group by
	PRODUCT

select
	PRODUCT, AGE, COMPANY, SALARY
from
	(
	select
		PRODUCT, null as AGE, null as COMPANY, S_SALARY as SALARY, S_SALARY
	from
		@Q
	union all
	select
		t1.PRODUCT, t1.AGE, t1.COMPANY, t1.SALARY, t2.S_SALARY
	from
		@t t1
		inner join @Q t2 on t2.PRODUCT = t1.PRODUCT
	)q
order by
	S_SALARY desc, SALARY desc

табличная переменная @Q это ваша таблица с итогами
12 май 18, 23:24    [21405832]     Ответить | Цитировать Сообщить модератору
 Re: Сложная сортировка для подсчёта итогов  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5112
что-то я какую-то лажу написал :)
как вариант можно так:
declare @t table (PRODUCT varchar(10), AGE tinyint, COMPANY varchar(10), SALARY bigint)
declare @Q table (ID int identity, PRODUCT varchar(10), AGE tinyint, COMPANY varchar(10), S_SALARY bigint)

insert @t
(PRODUCT, AGE, COMPANY, SALARY)
values
	('Appple',32,'Ahmedabad',2000),
	('Banana',25,'Delhi',1500),
	('Banana',23,'Kota',2000),
	('Orange',25,'Mumbai',6500),
	('Grapes',27,'Bhopal',2500),
	('Orange',22,'MP',4500),
	('Orange',24,'Indore',10000),
	('Banana2',25,'Delhi',1500),
	('Banana2',23,'Kota',2000)

insert @Q
(PRODUCT, AGE, COMPANY, S_SALARY)
select
	PRODUCT, null as AGE, null as COMPANY, sum(SALARY) as S_SALARY
from
	@t
group by
	PRODUCT

select
	PRODUCT, AGE, COMPANY, SALARY
from
	(
	select
		PRODUCT, null as AGE, null as COMPANY, S_SALARY as SALARY, S_SALARY * power(10,6) + ID as ID
	from
		@Q
	union all
	select
		t1.PRODUCT, t1.AGE, t1.COMPANY, t1.SALARY, t2.S_SALARY * power(10,6) + ID
	from
		@t t1
		inner join @Q t2 on t2.PRODUCT = t1.PRODUCT
	)q
order by
	ID desc, SALARY desc, AGE

табличная переменная @Q это ваша таблица с итогами
13 май 18, 00:46    [21405944]     Ответить | Цитировать Сообщить модератору
 Re: Сложная сортировка для подсчёта итогов  [new]
Goanfed
Member

Откуда:
Сообщений: 4
Дедушка, тоже не работает - Изображение получившейся таблицы.
Код такой получился:
select [Cdmain],[obozn],[msh],[docdate],[Дата создания],[nameSelf],[Код документа],[Номер документа],[Артикул],[Код поставщика],[Поставщик],[Группа снабженца],[Группа закупки],[howmany],[price],[summa],[№ PO],[Дебет],[Срок поставки],[Семейство],[Семейство - полн наименование],[Рынок] 
from 
    (select null as [Cdmain],[obozn],null as [msh],null as [docdate],null as [Дата создания],null as [nameSelf],null as [Код документа],null as [Номер документа],null as [Артикул],null as [Код поставщика],null as [Поставщик],null as [Группа снабженца],null as [Группа закупки],null as [howmany],null as [price],[s_summa] as [summa], [s_summa],null as [№ PO],null as [Дебет],null as [Срок поставки],null as [Семейство],null as [Семейство - полн наименование],null as [Рынок] 
        from res#csv
   union all
    select t1.[Cdmain],t1.[obozn],t1.[msh],t1.[docdate],t1.[Дата создания],t1.[nameSelf],t1.[Код документа],t1.[Номер документа],t1.[Артикул],t1.[Код поставщика],t1.[Поставщик],t1.[Группа снабженца],t1.[Группа закупки],t1.[howmany],t1.[price],t1.[summa],t1.[№ PO],t1.[Дебет],t1.[Срок поставки],t1.[Семейство],t1.[Семейство - полн наименование],t1.[Рынок],t2.[s_summa] 
        from results#csv t1 inner join res#csv t2 on t2.obozn = t1.obozn)q 
order by s_summa desc, summa desc


Это исходная таблица
results#csv

Здесь посчитанные итоги с сортировкой по сумме
res#csv
14 май 18, 17:38    [21408956]     Ответить | Цитировать Сообщить модератору
 Re: Сложная сортировка для подсчёта итогов  [new]
Goanfed
Member

Откуда:
Сообщений: 4
Дедушка, Сорри, уже после сообщения увидел второй вариант.
14 май 18, 17:41    [21408975]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить