#sql-server #hierarchy #self-join
#sql-сервер #иерархия #самосоединение
Вопрос:
У меня есть таблица клиентов с самосоединением (родительско-дочерний элемент), мне нужно написать запрос, который возвращает дочерних клиентов, статус которых позволяет родителю или дочернему элементу разместить заказ. Столбец является битовым столбцом и имеет значение null.
Возвращаемые результаты будут основаны на следующей матрице:
parent_status child_status Child is allowed to Order
null null FALSE
null 0 FALSE
null 1 TRUE
1 null TRUE
1 1 TRUE
1 0 FALSE
0 null FALSE
0 1 FALSE
0 0 FALSE
в соответствии с запросом здесь приведена схема и сценарий для данных
CREATE TABLE [dbo].[Customer](
[Customer_id] [int] NOT NULL,
[ParentCustomer_id] [int] NULL,
[Name_desc] [nvarchar](50) NULL,
[OrderIsAllowed_status] [bit] NULL)
GO
insert Customer
([Customer_id],[ParentCustomer_id],[Name_desc] ,[OrderIsAllowed_status])
values(1,null,'Parent 1',1)
insert Customer
([Customer_id],[ParentCustomer_id],[Name_desc] ,[OrderIsAllowed_status])
values(2,1,'Parent 1 - Child 1',null)
insert Customer
([Customer_id],[ParentCustomer_id],[Name_desc] ,[OrderIsAllowed_status])
values(3,1,'Parent 1 - Child 2',0)
insert Customer
([Customer_id],[ParentCustomer_id],[Name_desc] ,[OrderIsAllowed_status])
values(4,1,'Parent 1 - Child 3',1)
insert Customer
([Customer_id],[ParentCustomer_id],[Name_desc] ,[OrderIsAllowed_status])
values(5,null,'Parent 2',null)
insert Customer
([Customer_id],[ParentCustomer_id],[Name_desc] ,[OrderIsAllowed_status])
values(6,5,'Parent 2 - Child 1',null)
insert Customer
([Customer_id],[ParentCustomer_id],[Name_desc] ,[OrderIsAllowed_status])
values(7,5,'Parent 2 - Child 2',1)
insert Customer
([Customer_id],[ParentCustomer_id],[Name_desc] ,[OrderIsAllowed_status])
values(8,5,'Parent 2 - Child 3',0)
insert Customer
([Customer_id],[ParentCustomer_id],[Name_desc] ,[OrderIsAllowed_status])
values(9,null,'Parent 3',0)
insert Customer
([Customer_id],[ParentCustomer_id],[Name_desc] ,[OrderIsAllowed_status])
values(10,9,'Parent 3 - Child 1',null)
insert Customer
([Customer_id],[ParentCustomer_id],[Name_desc] ,[OrderIsAllowed_status])
values(11,9,'Parent 3 - Child 2',1)
insert Customer
([Customer_id],[ParentCustomer_id],[Name_desc] ,[OrderIsAllowed_status])
values(12,9,'Parent 3 - Child 3',0)
Комментарии:
1. Пожалуйста, добавьте образец структуры данных, который у вас есть. Что вы пробовали до сих пор?
2. для оказания помощи предоставлено недостаточно информации. Пожалуйста, предоставьте дополнительные образцы данных / схемы
3. извинения добавлены схема и некоторые образцы данных — просто подумал, что это достаточно простая схема, ее предоставление казалось ненужным
4. Каков ваш ожидаемый результат?
Ответ №1:
На основе таблицы истинности, СЛУЧАЙ, КОГДА для него будет что-то вроде приведенного ниже.
В примере используется табличная переменная только для демонстрации.
declare @Customer table (Customer_id int NOT NULL, ParentCustomer_id int, OrderIsAllowed_status bit);
insert @Customer ([Customer_id], [ParentCustomer_id], [OrderIsAllowed_status]) values
(1,null,1),
(2,1,null),
(3,1,0),
(4,1,1),
(5,null,null),
(6,5,null),
(7,5,1),
(8,5,0),
(9,null,0),
(10,9,null),
(11,9,1),
(12,9,0);
select
child.Customer_id,
child.ParentCustomer_id,
(case
when child.ParentCustomer_id is null then 'Parent ' cast(child.Customer_id as varchar)
else concat('Parent ',parent.Customer_id,' - Child ',child.Customer_id)
end) as Name_desc,
parent.OrderIsAllowed_status as parent_status,
child.OrderIsAllowed_status as child_status,
cast(case
when child.OrderIsAllowed_status = 1 and parent.OrderIsAllowed_status = 1 then 1
when child.OrderIsAllowed_status = 1 and parent.OrderIsAllowed_status is null then 1
when child.OrderIsAllowed_status is null and parent.OrderIsAllowed_status = 1 then 1
else 0
end as bit) as [Child is allowed to Order]
from @Customer child
left join @Customer parent on (child.ParentCustomer_id = parent.Customer_id);
Комментарии:
1. Спасибо за ответ: я попытался ответить на свой собственный вопрос… что было бы неправильно со следующим утверждением ..выберите p.Customer_id , p.Name_desc ,p.OrderIsAllowed_status как Parent_IsAllowed , c.Customer_id , c.ParentCustomer_id ,c.Name_desc , c.OrderIsAllowed_status от клиента p внутреннее объединение клиента c на p.customer_id = c.ParentCustomer_id где (p.OrderIsAllowed_status равенnull и c.OrderIsAllowed_status =1) или (p.OrderIsAllowed_status = 1 и c.OrderIsAllowed_status равно null) или (p.OrderIsAllowed_status = 1 и c.OrderIsAllowed_status = 1)
2. Если цель этого SQL состоит в том, чтобы получить только те дочерние элементы, в которых [дочернему элементу разрешено заказывать] верно, то этот SQL не ошибочен. Хотя лично с отношением «многие к одному» я бы поместил «много» в from и присоединил к нему «один».
Ответ №2:
Вы можете использовать рекурсивный CTE:
;WITH rec AS (
SELECT Customer_id,
ParentCustomer_id,
Name_desc,
OrderIsAllowed_status,
CAST(NULL AS bit) as parent_status,
1 as [Level]
FROM #Customer c
WHERE ParentCustomer_id IS NULL
UNION ALL
SELECT c.Customer_id,
c.ParentCustomer_id,
c.Name_desc,
c.OrderIsAllowed_status,
r.OrderIsAllowed_status,
r.[Level] 1
FROM rec r
INNER JOIN #Customer c
ON c.ParentCustomer_id = r.Customer_id
)
SELECT r.Customer_id,
r.ParentCustomer_id,
r.Name_desc,
r.OrderIsAllowed_status,
rs.[Child is allowed to Order]
FROM rec r
INNER JOIN #rules rs
ON COALESCE(r.[OrderIsAllowed_status],2) = COALESCE(rs.child_status,2)
AND COALESCE(r.parent_status,2) = COALESCE(rs.parent_status,2)
WHERE r.ParentCustomer_id IS NOT NULL
Вывод:
Customer_id ParentCustomer_id Name_desc OrderIsAllowed_status Child is allowed to Order
10 9 Parent 3 - Child 1 NULL FALSE
11 9 Parent 3 - Child 2 1 FALSE
12 9 Parent 3 - Child 3 0 FALSE
6 5 Parent 2 - Child 1 NULL FALSE
7 5 Parent 2 - Child 2 1 TRUE
8 5 Parent 2 - Child 3 0 FALSE
2 1 Parent 1 - Child 1 NULL TRUE
3 1 Parent 1 - Child 2 0 FALSE
4 1 Parent 1 - Child 3 1 TRUE
Я использовал эти таблицы:
CREATE TABLE #Customer (
[Customer_id] [int] NOT NULL,
[ParentCustomer_id] [int] NULL,
[Name_desc] [nvarchar](50) NULL,
[OrderIsAllowed_status] [bit] NULL
)
INSERT INTO #Customer VALUES
(1,null,'Parent 1',1),
(2,1,'Parent 1 - Child 1',null),
(3,1,'Parent 1 - Child 2',0),
(4,1,'Parent 1 - Child 3',1),
(5,null,'Parent 2',null),
(6,5,'Parent 2 - Child 1',null),
(7,5,'Parent 2 - Child 2',1),
(8,5,'Parent 2 - Child 3',0),
(9,null,'Parent 3',0),
(10,9,'Parent 3 - Child 1',null),
(11,9,'Parent 3 - Child 2',1),
(12,9,'Parent 3 - Child 3',0)
CREATE TABLE #rules (
parent_status bit NULL,
child_status bit NULL,
[Child is allowed to Order] nvarchar(5) NULL
)
INSERT INTO #rules VALUES
(null, null, 'FALSE'),
(null, 0, 'FALSE'),
(null, 1, 'TRUE'),
(1, null, 'TRUE'),
(1, 1, 'TRUE'),
(1, 0, 'FALSE'),
(0, null, 'FALSE'),
(0, 1, 'FALSE'),
(0, 0, 'FALSE')
Ответ №3:
Я попытался ответить на свой собственный вопрос…что было бы неправильно в следующем…если бы я хотел вернуть только дочерних клиентов, которым было разрешено разместить заказ:
select p.Customer_id
,p.Name_desc
,p.OrderIsAllowed_status as Parent_IsAllowed
,c.Customer_id
,c.ParentCustomer_id
,c.Name_desc
,c.OrderIsAllowed_status
from Customer p
inner join Customer c
on p.customer_id = c.ParentCustomer_id
where
(p.OrderIsAllowed_status is null and c.OrderIsAllowed_status =1)
or(p.OrderIsAllowed_status = 1 and c.OrderIsAllowed_status is null)
or (p.OrderIsAllowed_status = 1 and c.OrderIsAllowed_status = 1)
Комментарии:
1. Это не сработает, если у вас есть
grandparent - parent - child.2. Спасибо — я пропустил этот сценарий .. историческое использование таблицы в моем случае имеет только один уровень иерархии — родительский — дочерний, но вы правы, мне нужно рассмотреть оригинальный дизайн
3. С удовольствием! 🙂