@pomagiti234

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

схема БД
61346a762bb3b723980770.jpeg
в таблице 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

Она считает но не учитывает иерархическую структуру таблицы, то есть нормально считает только для тех строк, которые сами являются заказами, а не группами заказов. Я понял, что нужно сделать представление, которое будет содержать все вложенные заказы, в том случае если вводится индекс не заказа, а группы заказов.
  • Вопрос задан
  • 102 просмотра
Решения вопроса 2
rozhnev
@rozhnev
Fullstack programmer, DBA, медленно, дорого
Используем 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
Ответ написан
tsklab
@tsklab Куратор тега SQL Server
Здесь отвечаю на вопросы.
Во-первых у вас 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

В функцию переделаете сами.
Ответ написан
Пригласить эксперта
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы