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

Откуда:
Сообщений: 9
Введение
"Требуемый код будет рассматриваться как окончательный вариант,
поэтому просьба полностью понять задание. Все возникающие вопросы лучше
задавать сразу. Считайте что Ваш код будет читать человек не знающий
решения задачи. Рекомендуется использовать PL/SQL, указать версию БД,
под которую создавалось решение".
Решения лучше оформить в отдельном файле текстового формата (не Word).

задание.

"Eсть таблица категорий товара.
create table category (catid number primary key enable, -- ключ
 par_catid number references category, -- ссылка на родительскую категорию
 name varchar2(300), -- наименование категории
 descr varchar2(2000), -- описание
 cdate date -- дата создания
 );
Есть таблица товаров.
create table goods (gid number primary key enable, -- ключ
 cat number references category, -- ссылка на категорию
 name varchar2(300), -- наименование товара
 descr varchar2(2000), -- описание
 unit number references cargo, -- единица измерения
 loc number references place, -- место хранения
 resp number references corp, -- ответственный за сопровождение
 );
Есть таблица движения товаров.
create table moves (gd number references goods, -- товар
 mdate date, -- дата операции
 inout varchar2(1) default '+', -- приход/расход
 amount number, -- количество
 price number -- цена
 );

Требуется оформить вывод в таком виде
_______________
Категория уровня1||Суммарная стоимость прихода||Суммарная стоимость расхода
...
Категория уровняN||Суммарная стоимость прихода||Суммарная стоимость расхода
Товар 1 категории уровняN|Суммарное количество прихода|Суммарная стоимость прихода|Суммарное количество расхода|Суммарная стоимость расхода| Остаток товара
...
Товар m категории уровняN|Суммарное количество прихода|Суммарная стоимость прихода|Суммарное количество расхода|Суммарная стоимость расхода| Остаток товара

Все суммарные значения подсчитываются для каждой категории с учетом иерархии.
26 сен 16, 16:34    [19709511]     Ответить | Цитировать Сообщить модератору
 Re: Сумма в дереве (3 таблицы)  [new]
EEEgor
Member

Откуда:
Сообщений: 9
На данный момент сделал

DECLARE	@category TABLE
( 
 catid INT NOT NULL  , 
 par_catid INT NOT NULL, 
 name VARCHAR(300) NOT NULL,
 --descr VARCHAR(2000), 
 --cdate DATE,
 PRIMARY KEY CLUSTERED (catid)
 );

DECLARE @goods TABLE  (
 gid INT NOT NULL ,
 cat INT NOT NULL ,
 name VARCHAR(300) NOT NULL, 
 PRIMARY KEY CLUSTERED (gid)
 );

DECLARE @moves TABLE  (
 gd INT not null  , 
 inout VARCHAR(1) DEFAULT '+', 
 amount INT, -- количество
 price DECIMAL(12,2) -- цена
 );

 insert into @category
select 1, 0, 'Автомобили'
	union all
select 2, 1, 'Седаны'
	union all
select 3, 2, 'BMW' 
    union all
select 4, 0, 'Велосипеды'
	union all
select 5, 1, 'Американские'
	

insert into @goods
select 1, 3, 'Q4'
	union all
select 2, 3, 'Q5'
	union all
select 3, 5, 'Восток'

insert into @moves
select 1, '-', 5, 32000
	union all
select 2, '+', 10, 15000
	

DECLARE @summ TABLE
(
 catid int NOT NULL,
 par_catid int NOT NULL,
 name varchar(20) NOT NULL,
 lvl tinyint NOT NULL,
 price decimal(15,2) NULL,
 primary key clustered(catid)
);

WITH category(catid, par_catid, name, lvl, price)
AS 
  (
   SELECT categ.catid, ISNULL(categ.par_catid, 0), categ.name, 0, null as price
   FROM @category AS categ
   WHERE categ.par_catid = 0
UNION ALL
	SELECT categ.catid,ISNULL(categ.par_catid, 0), categ.name ,lvl+1, null 
	FROM @category AS categ
	JOIN category AS Q ON categ.par_catid=Q.catid
   )
INSERT INTO @summ
SELECT  catid,par_catid, name,lvl, price
FROM category


DECLARE @sum_cat TABLE
 (
  cat INT NOT NULL,
  price DECIMAL(12,2)
 )
INSERT INTO @sum_cat
SELECT Q2.cat, SUM(price)
FROM @moves AS QQ
INNER JOIN @goods AS Q2
ON QQ.gd=Q2.gid
GROUP BY Q2.cat;

UPDATE @summ
SET price = sum_cat.price
FROM @summ AS summ
INNER JOIN @sum_cat AS sum_cat ON summ.catid=sum_cat.cat

DECLARE @max_level TINYINT
SET @max_level = (select MAX(lvl) FROM @summ)

WHILE @max_level > 0
BEGIN
	  DELETE @sum_cat
	  INSERt INTO @sum_cat
	  SELECT summ.par_catid, SUM(ISNULL(price,0))
	  FROM @summ as summ
	  WHERE lvl=@max_level
	  GROUP BY summ.par_catid
	UPDATE @summ
	SET price = sum_cat.price
	FROM @summ as summ
	INNER JOIN @sum_cat as sum_cat
	ON summ.catid = sum_cat.cat
   SET @max_level -= 1
END

/*SELECT *
FROM @summ*/

SELECT SPACE((lvl)*2) + name as Категории, lvl,price
FROM @summ  
ORDER BY catid
26 сен 16, 16:36    [19709537]     Ответить | Цитировать Сообщить модератору
 Re: Сумма в дереве (3 таблицы)  [new]
EEEgor
Member

Откуда:
Сообщений: 9
Исходные данные
insert into @category
select 1, 0, 'Автомобили'
	union all
select 2, 1, 'Седаны'
	union all
select 3, 2, 'BMW' 
    union all
select 4, 0, 'Велосипеды'
	union all
select 5, 1, 'Американские'
	

insert into @goods
select 1, 3, 'Q4'
	union all
select 2, 3, 'Q5'
	union all
select 3, 5, 'Восток'
26 сен 16, 16:37    [19709550]     Ответить | Цитировать Сообщить модератору
 Re: Сумма в дереве (3 таблицы)  [new]
EEEgor
Member

Откуда:
Сообщений: 9


К сообщению приложен файл. Размер - 3Kb
26 сен 16, 16:40    [19709575]     Ответить | Цитировать Сообщить модератору
 Re: Сумма в дереве (3 таблицы)  [new]
EEEgor
Member

Откуда:
Сообщений: 9
Нужно как то из второй таблицы добавлять продукты к последнему листу дерева, и правильно все рассчитать
26 сен 16, 16:41    [19709584]     Ответить | Цитировать Сообщить модератору
 Re: Сумма в дереве (3 таблицы)  [new]
EEEgor
Member

Откуда:
Сообщений: 9
Есть идеи народ?
26 сен 16, 17:16    [19709901]     Ответить | Цитировать Сообщить модератору
 Re: Сумма в дереве (3 таблицы)  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
EEEgor,

похоже на https://technet.microsoft.com/ru-ru/library/bb522495(v=sql.105).aspx
но вообще, делать "красивый" не задача для сервера, есть более подходящие средства: bi, SSRS
26 сен 16, 17:22    [19709928]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить