SQLize Online / PHPize Online  /  SQLtest Online

-- Заказчики create table Customers ( row_id int identity not null, name nvarchar(max) not null, -- наименование заказчика constraint PK_Customers primary key nonclustered(row_id) ); -- Заказы 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 ); -- Позиции заказов 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 ); 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); select * from Orders join OrderItems on Orders.row_id = OrderItems.order_id where Orders.registered_at between '2020/01/01' and '2020/12/31' -- where name = N'Кассовый аппарат' ; 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) ; select * from Orders; with cte_orders(row_id, parent_id, group_name) as ( select row_id, parent_id, group_name from Orders where row_id = 10 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) from cte_orders left join OrderItems on cte_orders.row_id = OrderItems.order_id ;