用SQL连接另外两个表?

I have a table variable @table with data:

我有一个表变量@table，有数据:

Id Name   Count ParentId
4  Test4  2     1
5  Test5  3     1
6  Test6  2     2
7  Test7  4     2

and a table variable @table1 with data:

和一个表变量@table1的数据:

Id Name   ParentId
1  Test1  0
2  Test2  0
3  Test3  0
4  Test4  1
5  Test5  1
6  Test6  2
7  Test7  2
8  Test8  3

I want to join 2 tables on top to a new table variable as @table2 with Count's column of data is equal the sum of Count's @table with the same ParentId. And this is @table2 of data after join 2 tables:

我想要将两个表连接到一个新的表变量上，如@table2, Count的数据列等于Count的@table和相同的ParentId的总和。这是@table2在连接两个表之后的数据:

Id Name  Count ParentId
1  Test1 5     0
2  Test2 6     0
3  Test3 0     0
4  Test4 2     1
5  Test5 3     1
6  Test6 2     2
7  Test7 4     2
8  Test8 0     3

2 个解决方案

#1

Try this

试试这个

DECLARE @Table TABLE 
    ([Id] int, [Name] varchar(5), [Count] int, [ParentId] int)

INSERT INTO @Table([Id], [Name], [Count], [ParentId])
VALUES
    (4, 'Test4', 2, 1),
    (5, 'Test5', 3, 1),
    (6, 'Test6', 2, 2),
    (7, 'Test7', 4, 2)

DECLARE @Table1 TABLE ([Id] int, [Name] varchar(5), [ParentId] int)

INSERT INTO @Table1
    ([Id], [Name], [ParentId])
VALUES
    (1, 'Test1', 0),
    (2, 'Test2', 0),
    (3, 'Test3', 0),
    (4, 'Test4', 1),
    (5, 'Test5', 1),
    (6, 'Test6', 2),
    (7, 'Test7', 2),
    (8, 'Test8', 3)

SELECT [Id]
    ,[Name]
    ,SUM([Count]) AS [Count]
    ,[ParentId]
FROM
(
    SELECT T1.[Id]
    , T1.[Name]
    , T1.[ParentId]
    ,ISNULL(CASE WHEN T.[Count] IS NULL 
            THEN (SELECT [Count] FROM @Table TT WHERE TT.Id = T1.ID) 
            ELSE T.[Count] END,0) AS [Count]
    FROM @Table1 T1
    LEFT JOIN @Table T ON T1.[id] = T.[ParentId]
) T
GROUP BY [Id]
, [Name]
, [ParentId]

#2

Try this

试试这个

INSERT INTO @table2
SELECT A.Id,
       ISNULL(A.Name,B.Name) AS Name,
       (SELECT COUNT(*) 
        FROM @table A1 LEFT OUTER JOIN @table1 B1 ON A1.Id = B1.id  
        WHERE ISNULL(A1.ParentId,B1.ParentId)= ISNULL(A.ParentId,B.ParentId)) AS count,
        ISNULL(A.ParentId,B.ParentId) AS ParentID
FROM @table A LEFT OUTER JOIN @table1 B 
    ON A.Id = B.id