用列键汇总更新表。

I have a table like this:

我有一张这样的桌子:

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

Table is populated with:

表填充:

What I would like to do (but I don't know how) is populate summary rows (rows where romm_key is null with summary from that depa_key.

我想做的(但我不知道怎么做)是填充摘要行(其中romm_key为空的行使用来自depa_key的摘要。

Row where room_key is null is summary row. Only departments does not have room_key (because department means all rooms). Each room has room_key and depa_key.

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

Example:

例子:

First row (depa_key = 1, room_key = NULL) should be populated with:

第一行(depa_key = 1, room_key = NULL)应该填充为:

D1   D2
2    2

Second row (depa_key = 2, room_key = NULL) should be populated with:

第二行(depa_key = 2, room_key = NULL)应该填充为:

D1   D2
12   5

There will be a lot of depa_keys in the future (table is changing dynamic), so query must also be dynamic. I could type depa_keys one by one (update queries), but I can't do this. How to update columns d1 and d2 with summaries from that depa_key dynamically?

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

2 个解决方案

#1

Try this out.

试试这个。

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

#2

The solution with CTE looks more readable. At first we take aggregated information for depa_key taking only records for rooms where room_key IS NOT NULL. Then we update rows where room_key IS NULLby this aggregated data.

CTE的解决方案看起来更具有可读性。首先，我们获取depa_key的聚合信息，只获取room_key不为空的房间的记录。然后更新room_key为NULLby的行。

;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