Запрос SQL-сервера для вычисления суммы дочерних значений в родительской дочерней таблице. Исключить дочернее значение из суммы, если родителю присвоено значение

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

Сценарий таблицы и данные

CREATE TABLE [dbo].[Pools](
    [id] [int] NULL,
    [Pool] [varchar](50) NULL,
    [ParentPool] [varchar](50) NULL,
    [Amount] [int] NULL
)

INSERT [dbo].[Pools] ([id], [Pool], [ParentPool], [Amount]) VALUES (1, N'ROOT', NULL, NULL)
GO
INSERT [dbo].[Pools] ([id], [Pool], [ParentPool], [Amount]) VALUES (2, N'Pool 1', N'ROOT', 10000)
GO
INSERT [dbo].[Pools] ([id], [Pool], [ParentPool], [Amount]) VALUES (3, N'Pool 1.1', N'Pool 1', NULL)
GO
INSERT [dbo].[Pools] ([id], [Pool], [ParentPool], [Amount]) VALUES (4, N'Pool 1.2', N'Pool 1', 2000)
GO
INSERT [dbo].[Pools] ([id], [Pool], [ParentPool], [Amount]) VALUES (5, N'Pool 1.3', N'Pool 1', NULL)
GO
INSERT [dbo].[Pools] ([id], [Pool], [ParentPool], [Amount]) VALUES (6, N'Pool 1.3.1', N'Pool 1.3', NULL)
GO
INSERT [dbo].[Pools] ([id], [Pool], [ParentPool], [Amount]) VALUES (7, N'Pool 1.3.2', N'Pool 1.3', 500)
GO
INSERT [dbo].[Pools] ([id], [Pool], [ParentPool], [Amount]) VALUES (8, N'Pool 1.3.3', N'Pool 1.3', 5000)
GO
INSERT [dbo].[Pools] ([id], [Pool], [ParentPool], [Amount]) VALUES (9, N'Pool 2', N'ROOT', 35000)
GO
INSERT [dbo].[Pools] ([id], [Pool], [ParentPool], [Amount]) VALUES (10, N'Pool 2.1', N'Pool 2', 28000)
GO
INSERT [dbo].[Pools] ([id], [Pool], [ParentPool], [Amount]) VALUES (11, N'Pool 3', N'ROOT', NULL)
GO
INSERT [dbo].[Pools] ([id], [Pool], [ParentPool], [Amount]) VALUES (12, N'Pool 3.1', N'Pool 3', 4500)
GO

Как я пытался

WITH CTE AS (
    SELECT pool, ParentPool, isnull(Amount,0.0)Amount
    FROM Pools
    WHERE ParentPool IS NULL 
    UNION ALL
    SELECT t.Pool, t.ParentPool, isnull(t.Amount,0.0)Amount
    FROM Pools t
    INNER JOIN CTE r ON t.ParentPool = r.pool
    WHERE NOT EXISTS (
        SELECT 1
        FROM Pools t2
        WHERE t2.Pool = t.ParentPool
        AND isnull(t2.Amount,0.0) > isnull(r.Amount,0.0) 

    )
)

SELECT Pool, ParentPool, isnull(Amount,0.0)Amount, (
        SELECT SUM(isnull(Amount,0.0))
        FROM CTE
        WHERE Pool IN (
            SELECT Pool
            FROM CTE
            WHERE ParentPool = t.Pool 
        )
    ) AS CalculatedAmount
FROM CTE t

Ожидаемый результат

Имя пула Количество Расчетная сумма КОРЕНЬ 49500 Пул 1 10000 5000 Пул 1.1 0 Пул 1.2 2000 г. 0 Пул 1.3 3000 5500 Пул 1.3.1 0 Пул 1.3.2 500 0 Пул 1.3.3 5000 0 Бассейн 2 35000 28000 Пул 2.1 28000 0 Бассейн 3 4500 Пул 3.1 4500 0

Иллюстрация изображения

заранее спасибо

with c as(select *,Pool as cPool,Amount as cAmount, case when Amount is not null then 1 else 0 end as stopc,0 as pAmount,1 as plvl from dbo.Pools union all select c.id,c.Pool,c.ParentPool,c.Amount,p.Pool,case when c.stopc=1 then 0 else p.Amount end,case when c.stopc=1 or p.Amount is not null then 1 else 0 end ,case when c.plvl=1 then p.Amount end, c.plvl+case when p.Amount is null then 0 else 1 end from c join dbo.Pools as p on c.cPool=p.ParentPool) select id,Pool,ParentPool,Amount,case when Amount is null then sum(cAmount) else sum(pAmount) end from c group by id,Pool,ParentPool,Amount;

— 21.04.2023 04:38

sql sql-server

20.04.2023 13:24

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

131

Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Ответ от @lptr:

Используйте рекурсивный CTE

with c as (
    select *,
      Pool as cPool,
      Amount as cAmount,
      case when Amount is not null then 1 else 0 end as stopc,
      0 as pAmount,
      1 as plvl
    from dbo.Pools

    union all

    select
      c.id,
      c.Pool,
      c.ParentPool,
      c.Amount,
      p.Pool,
      case when c.stopc = 1 then 0 else p.Amount end,
      case when c.stopc = 1 or p.Amount is not null then 1 else 0 end,
      case when c.plvl = 1 then p.Amount end,
      c.plvl + case when p.Amount is null then 0 else 1 end
    from c
    join dbo.Pools as p on c.cPool = p.ParentPool
)
select
  id,
  Pool,
  ParentPool,
  Amount,
  case when Amount is null then sum(cAmount) else sum(pAmount) end
from c
group by
  id,
  Pool,
  ParentPool,
  Amount;

24.04.2023 11:29

Другие вопросы по теме

Удалить повторяющуюся запись на сервере sql

Необязательные отношения один к одному в entitiy framework не приводят к правильному внешнему первичному ключу в моей базе данных sql

Обновление существующих полей данными из другого поля в той же строке и данными из другой таблицы — SQL Server 2019

SQL-запрос для извлечения чисел перед определенным словом, включая числа с плавающей запятой

Как создать глобальную временную таблицу в одной хранимой процедуре и использовать ее в другой хранимой процедуре

Как сделать каскад на колонке?

Ошибка «Значение типа «Целое число» не может быть преобразовано в Output0Buffer» в DataFlowTask в Visual Studio Professional 2017

Установите уникальное значение для первого дубликата и значение по умолчанию для последующих дубликатов того же номера

Как настроить «репликацию таблиц» между двумя базами данных SQL Azure без каких-либо внешних компонентов или служб?

Как сделать запрос продолжения, который правильно разбивается на страницы с условиями включения?

Запрос SQL-сервера для вычисления суммы дочерних значений в родительской дочерней таблице. Исключить дочернее значение из суммы, если родителю присвоено значение

Ответы 1

Другие вопросы по теме

Похожие вопросы