Как написать функции?

Question

[pomagiti234]

схема БД

в таблице Orders данные хранятся в иерархическом порядке, parent_id содержит row_id предка. Всего в иерархии три уровня.
Нужно написать две функции:
1) Написать функцию select_orders_by_item_name. Она получает один аргумент - наименование позиции (строка),
и должна найти все заказы, в которых имеется позиция с данным наименованием. Кроме того, она должна
подсчитать количество позиций с указанным наименованием в каждом отдельном заказе. Результатом вызова
функции должна быть таблица:

CREATE FUNCTION select_orders_by_item_name (@string nvarchar(max))
returns Table
AS  
RETURN   
(  
    select distinct Orders.row_id,Customers.name from orders,orderitems,Customers 
	where OrderItems.name=@string and OrderItems.order_id=Orders.row_id
	and Customers.row_id=Orders.customer_id  
);

Она заказы находит, но не считает их.

2) Написать функцию calculate_total_price_for_orders_group. Она получает row_id группы (либо заказа),
и возвращает суммарную стоимость всех позиций всех заказов в этой группе (заказе), причем
суммирование должно выполняться по всему поддереву заказов, начинающемуся с данной группы. Функция должна возвращать число.

CREATE FUNCTION calculate_total_price_for_orders_group(@f int)
RETURNS FLOAT
AS
BEGIN
  DECLARE @s int 
 SET @s=(SELECT Sum(OrderItems.price)
      FROM orderitems INNER JOIN orders
      ON orders.row_id = OrderItems.order_id
      GROUP BY Orders.row_id
      HAVING Orders.row_id=@f)
  RETURN (@s)
END

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

Comments

[pomagiti234]

БД:

-- Заказчики
create table Customers
(
   row_id int identity not null,
   name nvarchar(max) not null,           -- наименование заказчика

   constraint PK_Customers
      primary key nonclustered(row_id)
);
go

-- Заказы
create table Orders
(
   row_id int identity not null,
   parent_id int,                         -- row_id родительской группы
   group_name nvarchar(max),              -- наименование группы заказов
   customer_id int,                       -- row_id заказчика
   registered_at date                     -- дата регистрации заказа

   constraint PK_Orders
      primary key nonclustered (row_id),
   constraint FK_Orders_Folder 
      foreign key (parent_id) 
      references Orders(row_id)
      on delete no action
      on update no action,
   constraint FK_Customers
      foreign key (customer_id)
      references Customers(row_id)
      on delete cascade
      on update cascade
);
go

-- Позиции заказов
create table OrderItems
(
   row_id int identity not null,
   order_id int not null,                 -- row_id заказа
   name nvarchar(max) not null,           -- наименование позиции
   price int not null,                    -- стоимость позиции в рублях

   constraint PK_OrderItems
      primary key nonclustered (row_id),
   constraint FK_OrderItems_Orders
      foreign key (order_id) 
      references Orders(row_id)
      on delete cascade
      on update cascade
);
go

Набор данных:

insert into Customers                                                               -- 1
values(N'Иванов');
insert into Customers                                                               -- 2
values(N'Петров');
insert into Customers                                                               -- 3
values(N'Сидоров');
insert into Customers                                                               -- 4
values(N'ИП Федоров');


insert into Orders(parent_id, group_name, customer_id, registered_at)               -- 1
values (null, N'Все заказы', null, null);

   insert into Orders(parent_id, group_name, customer_id, registered_at)            -- 2
   values (1, N'Частные лица', null, null);

      insert into Orders(parent_id, group_name, customer_id, registered_at)         -- 3
      values (2, N'Оргтехника', null, null);

         insert into Orders(parent_id, group_name, customer_id, registered_at)      -- 4
         values (3, null, 1, '2019/10/02');

         insert into Orders(parent_id, group_name, customer_id, registered_at)      -- 5
         values (3, null, 1, '2020/05/17');

       insert into Orders(parent_id, group_name, customer_id, registered_at)        -- 6
         values (3, null, 1, '2020/04/28');

       insert into Orders(parent_id, group_name, customer_id, registered_at)        -- 7
         values (3, null, 2, '2019/08/05');

       insert into Orders(parent_id, group_name, customer_id, registered_at)        -- 8
         values (3, null, 2, '2020/05/17');

       insert into Orders(parent_id, group_name, customer_id, registered_at)        -- 9
         values (3, null, 2, '2020/02/11');

      insert into Orders(parent_id, group_name, customer_id, registered_at)         -- 10
      values (2, N'Канцелярия', null, null);

         insert into Orders(parent_id, group_name, customer_id, registered_at)      -- 11
         values (10, null, 3, '2020/04/09');

   insert into Orders(parent_id, group_name, customer_id, registered_at)            -- 12
   values (1, N'Юридические лица', null, null);

      insert into Orders(parent_id, group_name, customer_id, registered_at)         -- 13
      values (12, null, 4, '2020/06/25');


insert into OrderItems(order_id, name, price)
values (4, N'Принтер', 30);
insert into OrderItems(order_id, name, price)
values (4, N'Факс', 20);


insert into OrderItems(order_id, name, price)
values (5, N'Принтер', 50);
insert into OrderItems(order_id, name, price)
values (5, N'Кассовый аппарат', 40);
insert into OrderItems(order_id, name, price)
values (5, N'Факс', 30);


insert into OrderItems(order_id, name, price)
values (6, N'Кассовый аппарат', 30);
insert into OrderItems(order_id, name, price)
values (6, N'Кассовый аппарат', 40);


insert into OrderItems(order_id, name, price)
values (7, N'Копировальный аппарат', 50);
insert into OrderItems(order_id, name, price)
values (7, N'Калькулятор', 10);
insert into OrderItems(order_id, name, price)
values (7, N'Кассовый аппарат', 60);


insert into OrderItems(order_id, name, price)
values (8, N'Принтер', 50);
insert into OrderItems(order_id, name, price)
values (8, N'Калькулятор', 10);


insert into OrderItems(order_id, name, price)
values (9, N'Телефонный аппарат', 50);
insert into OrderItems(order_id, name, price)
values (9, N'Кассовый аппарат', 40);


insert into OrderItems(order_id, name, price)
values (11, N'Бумага', 2);
insert into OrderItems(order_id, name, price)
values (11, N'Ручки', 1);


insert into OrderItems(order_id, name, price)
values (13, N'Кулер', 100);
insert into OrderItems(order_id, name, price)
values (13, N'Стулья', 70);
insert into OrderItems(order_id, name, price)
values (13, N'Факс', 20);
go

[pomagiti234]

Селект ордер я победил сам (та что первая), а для второй необходима ваша поддержка, товарищи.

[pomagiti234]

Написал функцию так:

CREATE FUNCTION calculate_total_price_for_orders_group(@f int)
RETURNS FLOAT
AS
BEGIN
  DECLARE @s int, @idd int
  if (select Orders.group_name from Orders where Orders.row_id=@f)is null
  begin
	 SET @s=@s+((SELECT Sum(OrderItems.price)
      FROM orderitems INNER JOIN orders
      ON orders.row_id = OrderItems.order_id
      GROUP BY Orders.row_id
      HAVING Orders.row_id=@f))
  end
	else
	begin
	   set @idd=(select Orders.parent_id from Orders where Orders.row_id=@f);
		set @s=@s + ( [dbo].[calculate_total_price_for_orders_group](@idd))
	end
  RETURN (@s)
END

При вызове нул возвращает

[Константин Цветков]

pomagiti234, Потому что DECLARE @s int и @s равно null, а значит set @s=@s + … тоже равно null.

[pomagiti234]

Инициализировал 0, при вводе индекса листочка работает при вводе узла нет. Полагаю, надо обход узла както доработать

Answer 1

[Slava Rozhnev]

Используем CTE:

with cte_orders(row_id, parent_id, group_name) as (
	select row_id, parent_id, group_name from Orders where row_id = @f
  	union all
  	select O.row_id, O.parent_id, O.group_name 
  	from Orders O 
  	join cte_orders on cte_orders.row_id = O.parent_id
) 
select sum(price) total_pice from cte_orders 
left join OrderItems on cte_orders.row_id = OrderItems.order_id ;

T-SQL fiddle

Comments

[pomagiti234]

Спасибо)

Answer 2

[Константин Цветков]

Во-первых у вас 4 уровня, а не 3.
Во-вторых, конечно, нужна рекурсия.

Но по колхозному тоже работает:

DECLARE @S FLOAT, @ORD INT = 1
SELECT @S = ISNULL(SUM(price),0) FROM OrderItems WHERE (order_id = @ORD)
SET @S = @S + 
  ( SELECT ISNULL(SUM(OrderItems.price), 0) 
    FROM OrderItems 
      INNER JOIN Orders AS P1 ON OrderItems.order_id = P1.row_id
      WHERE (P1.parent_id = @ORD))
SET @S = @S + 
  ( SELECT ISNULL(SUM(OrderItems.price), 0) 
    FROM OrderItems 
      INNER JOIN Orders AS P1 ON OrderItems.order_id = P1.row_id
      INNER JOIN Orders AS P2 ON P1.parent_id = P2.row_id
      WHERE (P2.parent_id = @ORD))
SET @S = @S + 
  ( SELECT ISNULL(SUM(OrderItems.price), 0) 
    FROM OrderItems 
      INNER JOIN Orders AS P1 ON OrderItems.order_id = P1.row_id
      INNER JOIN Orders AS P2 ON P1.parent_id = P2.row_id
      INNER JOIN Orders AS P3 ON P2.parent_id = P3.row_id
      WHERE (P3.parent_id = @ORD))
SELECT @S

В функцию переделаете сами.

Comments

[pomagiti234]

Большое спасибо!