在 SQL Server 中显示相关子记录之后的父记录-解网

问：

declare @t1 table
(Id int,
Name nvarchar(200)
)

declare @t2 table
(Id int,
t1Id int fk,
Name nvarchar(200),
Amount decimal(18,2)
)

declare @t3 table
(
t2Id int,
t1Id int,
Name nvarchar(200),
total decimal(18,2)
)

insert into @t1 values (1, 'Technology')
insert into @t1 values (2, 'Database')
insert into @t1 values (3, 'ABC')

insert into @t2 values (1, 1, '.Net', 10)
insert into @t2 values (2, 1, 'PHP', 10)
insert into @t2 values (3, 1, 'Python', 10)
insert into @t2 values (4, 2, 'SQL', 20)
insert into @t2 values (5, 2, 'MySQL', 30)
insert into @t2 values (6, 3, 'JKL', 10)
insert into @t2 values (7, 3, 'PQR', 40)

insert into @t3 values (1, 1, '.Net', 10)
insert into @t3 values (2, 1, 'PHP', 10)
insert into @t3 values (3, 1, 'Python', 10)
insert into @t3 values (null, 1, 'Technology', 30)
insert into @t3 values (4, 2, 'SQL', 20)
insert into @t3 values (5, 2, 'MySQL', 30)
insert into @t3 values (null, 2, 'Database', 50)
insert into @t3 values (6, 3, 'JKL', 10)
insert into @t3 values (7, 3, 'PQR', 40)
insert into @t3 values (null, 3, 'ABC', 50)

我在 SQL 中有两个表，分别是 @t1 和 @t2，我希望@t3表作为输出。如何实现这一点？

所需输出为表 @t3。

sql-server sql-server-2008

-- Create table @t1
declare @t1 table
(
    Id int,
    Name nvarchar(200)
)

-- Create table @t2
declare @t2 table
(
    Id int,
    t1Id int,
    Name nvarchar(200),
    Amount decimal(18,2)
)

-- Create table @t3
declare @t3 table
(
    t2Id int,
    t1Id int,
    Name nvarchar(200),
    total decimal(18,2)
)

-- Insert data into @t1
insert into @t1 values (1, 'Technology')
insert into @t1 values (2, 'Database')
insert into @t1 values (3, 'ABC')

-- Insert data into @t2
insert into @t2 values (1, 1, '.Net', 10)
insert into @t2 values (2, 1, 'PHP', 10)
insert into @t2 values (3, 1, 'Python', 10)
insert into @t2 values (4, 2, 'SQL', 20)
insert into @t2 values (5, 2, 'MySQL', 30)
insert into @t2 values (6, 3, 'JKL', 10)
insert into @t2 values (7, 3, 'PQR', 40)


select * from @t3  
-- Insert data into @t3 and calculate the totals
insert into @t3
select
    t2.Id as t2Id,
    t1.Id as t1Id,
    t2.Name,
    t2.Amount as total
from @t1 t1
inner join @t2 t2 on t1.Id = t2.t1Id

-- Calculate the totals for each group in @t2 and update @t3
update t3
set t3.total = t2.Amount
from @t3 t3
inner join (
    select t1Id, sum(Amount) as Amount
    from @t2
    group by t1Id
) t2 on t3.t1Id = t2.t1Id
where t3.t2Id is null
select * from @t3

t2Id	t1Id	名字	总

t2Id	t1Id	名字	总
1	1	。网	10.00
2	1	PHP的	10.00
3	1	蟒	10.00
4	2	SQL算法	20.00
5	2	MySQL数据库	30.00
6	3	JKL公司	10.00
7	3	QQ3型	40.00

小提琴

WITH CTE AS (
SELECT 0 As IsT1,
       Id As t2Id,
       t1Id,
       Name, 
      Amount As total
FROM @t2

UNION ALL

SELECT 1 As IsT1,
       NULL,
       T1.Id,
       T1.NAME,
       SUM(Amount)
FROM @t1 As T1
JOIN @t2 AS T2
   ON T1.Id = T2.T1Id
GROUP BY T1.Id, T1.Name
)
  
SELECT t2Id, t1Id, Name, total
FROM CTE
ORDER BY t1Id, IsT1, t2Id

结果：

t2Id    t1Id    Name        total
1       1       .Net        10.00
2       1       PHP         10.00
3       1       Python      10.00
null    1       Technology  30.00
4       2       SQL         20.00
5       2       MySQL       30.00
null    2       Database    50.00
6       3       JKL         10.00
7       3       PQR         40.00
null    3       ABC         50.00

在 SQL Server 中显示相关子记录之后的父记录

Display Parent Records After Related Child Records in SQL Server

评论

评论

评论