sql - 通过输入 sum(column) 值来更新记录
问题描述
我有这样的table1:
+-----+-----+------+
| cat | val | type |
+-----+-----+------+
| A | 100 | c1 |
| H | 25 | c2 |
| H | 50 | c3 |
| H | 30 | c2 |
| A | 15 | c3 |
| H | 10 | c1 |
| H | 15 | c1 |
| B | 10 | c4 |
| H | 20 | c4 |
| H | 15 | c3 |
+-----+-----+------+
我需要按类型将 sum(val) 组添加到仅属于每种类型的一个 H
所以我在按类型分组后说 table2 :
+-----+-----+
| cat | val |
+-----+-----+
| c1 | 125 |
| c2 | 55 |
| c3 | 80 |
| c4 | 30 |
+-----+-----+
我需要将 125 添加到 c1 类型的任何一个 H 值,将 55 添加到 c2 的任何一个 H 值,依此类推..如果 c1 没有 H,那么它应该创建该记录。
所以最后我们得到:
+-----+-----+------+
| cat | val | type |
+-----+-----+------+
| A | 100 | c1 |
| H | 25 | c2 |
| H | 130 | c3 |
| H | 85 | c2 |
| A | 15 | c3 |
| H | 135 | c1 |
| H | 15 | c1 |
| B | 10 | c4 |
| H | 50 | c4 |
| H | 15 | c3 |
+-----+-----+------+
如果不按类型分组,我该怎么做?另外我没有更新权限,不能使用存储过程。我还必须记住,table1 是涉及多个内部联接的查询的结果,我不想一遍又一遍地用于选择语句。
解决方案
看看这是否有意义。我添加了 ID 列只是为了以与输入相同的顺序显示最终结果(以便于阅读)。
SQL> -- T1 is what you currently have; it can/could be your current query
SQL> with t1 (id, cat, val, type) as
2 (select 1, 'A', 100, 'C1' from dual union all
3 select 2, 'H', 25 , 'C2' from dual union all
4 select 3, 'H', 50 , 'C3' from dual union all
5 select 4, 'H', 30 , 'C2' from dual union all
6 select 5, 'A', 15 , 'C3' from dual union all
7 select 6, 'H', 10 , 'C1' from dual union all
8 select 7, 'H', 15 , 'C1' from dual union all
9 select 8, 'B', 10 , 'C4' from dual union all
10 select 9, 'H', 20 , 'C4' from dual union all
11 select 10,'H', 15 , 'C3' from dual
12 ),
13 -- sum VAL per type
14 t1_sum as
15 (select type, sum(val) sum_val
16 from t1
17 group by type
18 ),
19 -- find row number; let any H be number 1
20 t1_rn as
21 (select id, cat, val, type,
22 row_number() over (partition by type
23 order by case when cat = 'H' then 1 end) rn
24 from t1
25 )
26 -- the final result; add SUM_VAL to the first H row per type
27 select r.cat, r.val + case when r.rn = 1 then s.sum_val else 0 end val,
28 r.type
29 From t1_rn r join t1_sum s on s.type = r.type
30 order by r.id;
CAT VAL TYPE
--- ---------- ----
A 100 C1
H 80 C2
H 130 C3
H 30 C2
A 15 C3
H 135 C1
H 15 C1
B 10 C4
H 50 C4
H 15 C3
10 rows selected.
SQL>
[编辑:试图澄清如何使用你的大查询]
假设这是您的非常大且复杂的查询:
select a.cat,
case when a.cat = 'A' then b.val
when a.cat = 'Z' then c.val
else 'H'
end val,
c.type
from a join b on a.id = b.id and a.x = b.y
join c on c.id = b.idx
where a.date_column < sysdate
and c.type = 'X';
正如我所说,创建一个基于它的视图
create or replace view v_view as
select a.cat,
case when a.cat = 'A' then b.val
when a.cat = 'Z' then c.val
else 'H'
end val,
c.type
from a join b on a.id = b.id and a.x = b.y
join c on c.id = b.idx
where a.date_column < sysdate
and c.type = 'X';
并将其用作“我的”查询的来源(从第 14 行开始):
with t1_sum as
(select type, sum(val) sum_val
from v_view --> here's the view
group by type
), etc.
或者,使用“巨大”查询本身作为初始 CTE:
with t1 as
-- this is your "huge" query
(select a.cat,
case when a.cat = 'A' then b.val
when a.cat = 'Z' then c.val
else 'H'
end val,
c.type
from a join b on a.id = b.id and a.x = b.y
join c on c.id = b.idx
where a.date_column < sysdate
and c.type = 'X'
),
-- sum VAL per type
t1_sum as
(select type, sum(val) sum_val
from t1
group by type
), etc.
推荐阅读
- php - 如何修复“SQLSTATE[HY000] [14] 无法打开数据库文件”?
- java - JMeter:是否有命令或 API 来检查 JMeter 中从机的连接?
- stata - 在 Stata 中处理三维面板数据
- reactjs - 打字稿错误:类型中缺少属性“类”
- r - 将 R 中的热图导出为 EMF
- spring - 为什么 Spring boot 2.4.3 有与 entityManagerFactory bean 相关的迁移问题
- typo3 - Typo3 后端 HTML 元素 - 格式值错误
- batch-file - 如何批量淡入淡出多个视频ffmpeg?
- python - python pandas将逗号分隔的值放入带有“title”的列中
- google-api - 验证 Google Drive 请求