Как составить запрос?

Question

[pomagiti234]

схема БД

в таблице Orders данные хранятся в иерархическом порядке, parent_id содержит row_id предка. Всего в иерархии три уровня.
Нужно составить запрос, возвращающий наименования всех покупателей, у которых каждый заказ в 2020 году содержит
как минимум одну позицию с наименованием "Кассовый аппарат".

select distinct Orders.row_id, OrderItems.name, Customers.name, Orders.registered_at 
  from orders, orderitems, Customers
  where OrderItems.name=N'Кассовый аппарат' 
    and OrderItems.order_id=Orders.row_id 
    and Customers.row_id=Orders.customer_id 
    and Orders.registered_at between '01/01/2020' and '31/12/2020'

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

Comments

[Slava Rozhnev]

Приведите тестовый набор данных под вашу задачу. Схему так же желательно в виде SQL DDL команд

[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

Answer 1

[Slava Rozhnev]

Попробуйте следующий запрос:

select 
	customer_id, 
	Customers.name,
	count(distinct order_id) all_orders,
	count(distinct case when OrderItems.name = N'Кассовый аппарат' then order_id end) cash_machine_orders 
from Orders
join OrderItems on Orders.row_id = OrderItems.order_id
join Customers on Customers.row_id = customer_id
where Orders.registered_at between '2020/01/01' and '2020/12/31'
group by Customers.name, customer_id
having count(distinct order_id) = count(distinct case when OrderItems.name = N'Кассовый аппарат' then order_id end)
;

Test T-SQL online

Comments

[pomagiti234]

Спасибо, то что надо

[Slava Rozhnev]

pomagiti234, В следующий раз, сразу публикуйте структуру таблиц и данные

[pomagiti234]

Понял, если не сложно гляньте еще один вопрос, та же БД https://qna.habr.com/q/1043536

[Slava Rozhnev]

pomagiti234, Если Вы проявите некоторую смекалку, то легко адаптируете данный запрос под ваши нужды.

[pomagiti234]

Slava Rozhnev, там проблема в функции для подсчета суммы. Из-за того что одна из таблиц данные хранит в иерархическом виде, я решил использовать рекурсию. Тесть когда вводится индекс заказа то считается сумма, если введен индекс группы, то функция снова вызывается, но уже в качестве индекса передаются индекс предка. И во общем я запутался...

[Slava Rozhnev]

pomagiti234, Я извиняюсь, но Ваша иерархия - полная чушь. Вот например я как частное лицо заказал канцтовары - к какой категории Вы отнесете заказ?

При нормализации базы в таблице Customers создаем поле customer_type ('Частные лица'/'Юридические лица') в таблице OrderItems - поле item_type ('Оргтехника'/'Канцелярия'/...)

[pomagiti234]

Полностью согласен, но иерархия как и вся бд дана мне как задание.