使用列关键字的摘要更新表

Question

我有一张这样的桌子：

declare @rooms table
(
depa_key int,
room_key int,
d1 int,
d2 int
)

insert into @rooms(depa_key, room_key, d1, d2)
select 1, null, null, null
union all
select 1, 1, 1, 1
union all
select 1, 1, 1, 1
union all
select 2, null, null, null
union all
select 2, 1, 5, 3
union all
select 2, 1, 7, 2
union all
select 3, null, null, null
union all
select 3, 1, 6, 9
union all
select 3, 1, 5, 8

表中填充了：

​

​

我想要做的(但我不知道如何做)是填充汇总行(使用来自该depa_key的摘要填充romm_key is null的行)。

room_key为null的行是汇总行。只有部门没有room_key (因为部门意味着所有房间)。每个房间都有room_key和depa_key。

示例：

第一行(depa_key = 1，room_key = NULL)应填充：

D1   D2
2    2

第二行(depa_key = 2，room_key = NULL)应填充：

D1   D2
12   5

将来会有很多depa_keys (表是动态变化的)，所以查询也必须是动态的。我可以逐个输入depa_keys (更新查询)，但我不能这样做。如何使用depa_key中摘要动态更新列d1和d2？

Answer 1

试试这个。

declare @rooms table
(
depa_key int,
room_key int,
d1 int,
d2 int
)

insert into @rooms(depa_key, room_key, d1, d2)
select 1, null, null, null
union all
select 1, 1, 1, 1
union all
select 1, 1, 1, 1
union all
select 2, null, null, null
union all
select 2, 1, 5, 3
union all
select 2, 1, 7, 2
union all
select 3, null, null, null
union all
select 3, 1, 6, 9
union all
select 3, 1, 5, 8


UPDATE @rooms
SET d1 = a.d1, d2 = a.d2
FROM (
        SELECT depa_key, SUM(D1)d1, SUM(d2) d2
        FROM @rooms
        GROUP BY depa_key
    ) a
    JOIN @rooms r ON
        a.depa_key = r.depa_key
WHERE r.room_key IS NULL


SELECT *
FROM @rooms

Answer 2

使用CTE的解决方案看起来更具可读性。首先，我们获取depa_key的聚合信息，只获取room_key IS NOT NULL所在房间的记录。然后，我们通过该聚合数据更新room_key IS NULL所在的行。

;WITH DepaStats AS
(
    SELECT
        depa_key     AS depa_key,
        SUM(d1)      AS sum_d1,
        SUM(d2)      AS sum_d2
    FROM @rooms
    WHERE room_key IS NOT NULL
    GROUP BY depa_key
)
UPDATE R
SET
    d1 = S.sum_d1,
    d2 = S.sum_d2
FROM @rooms R
LEFT JOIN DepaStats S
    ON S.depa_key = R.depa_key
WHERE R.room_key IS NULL