db2 在SQL中覆盖行

piztneat  于 2022-11-07  发布在  DB2
关注(0)|答案(1)|浏览(224)

因此,我想在数据库中存储多组键-值对,然后基于一系列的键-值对集合检索组合的键-值对集合。
假设我们有一个如下表:

create table MYTABLE (ROLE VARCHAR(62), KEY VARCHAR(62), VALUE VARCHAR(62));
insert into MYTABLE values('R1', 'K1', 'R1K1');
insert into MYTABLE values('R1', 'K2', 'R1K2');
insert into MYTABLE values('R1', 'K3', 'R1K3');
insert into MYTABLE values('R2', 'K1', 'R2K1');
insert into MYTABLE values('R2', 'K2', 'R2K2');
insert into MYTABLE values('R2', 'K4', 'R2K4');
insert into MYTABLE values('R3', 'K1', 'R3K1');
insert into MYTABLE values('R3', 'K4', 'R3K4');
insert into MYTABLE values('R3', 'K5', 'R3K5');
insert into MYTABLE values('R4', 'K1', 'R4K1');
insert into MYTABLE values('R5', 'K6', 'R5K6');

当给定序列R1、R2、R3(或R3、R2、R1,取决于您的观点)时,我希望得到以下结果:

+-----+-------+
| KEY | VALUE |
+-----+-------+
| K1  | R3K1  |
| K2  | R2K2  |
| K3  | R1K3  |
| K4  | R3K4  |
| K5  | R3K5  |
+-----+-------+

最初,我打算在SQL之外执行此操作,方法是检索每个集合并将结果转储到散列中,然后让后续项覆盖前面的项:

select KEY,VALUE from MYTABLE where ROLE = 'R1';
select KEY,VALUE from MYTABLE where ROLE = 'R2';
select KEY,VALUE from MYTABLE where ROLE = 'R3';

但是,如果要混合许多角色,我不想为每个角色返回数据库。
最后,我发现下面这些说法是可行的:

Oracle/DB2:
select distinct KEY, first_value(VALUE) over (partition by KEY order by decode(ROLE, 'R1', 1, 'R2', 2, 'R3', 3) desc) value from MYTABLE where ROLE in ('R1', 'R2', 'R3') order by KEY;
MySQL (guess):
select distinct KEY, first_value(VALUE) over (partition by KEY order by field(ROLE, 'R1, 'R2', 'R3') desc) value from MYTABLE where ROLE in ('R1', 'R2', 'R3') order by KEY;

(The对于我的需要,结尾处的“order by KEY”并不是绝对必要的。)
有没有更好的方法?

jobtbby3

jobtbby31#

您可以使用row_number()来表示该逻辑:

select key, value
from (
    select t.*,
        row_number() over(partition by key order by role) as rn
    from mytable t
    where role in ('R1', 'R2', 'R3')
) t
where rn = 1

由于'R1' < 'R2' < 'R3',这里实际上不需要条件排序。但是如果你想对其他序列进行条件排序,你通常会使用标准的case表达式:

select key, value
from (
    select t.*,
        row_number() over(
            partition by key 
            order by case role when 'R1' then 1 when 'R2' then 2 when 'R3' then 3 end
        ) as rn
    from mytable t
    where role in ('R1', 'R2', 'R3')
) t
where rn = 1

这是一种相当可移植的语法,可以在许多数据库中使用(只要它们支持窗口函数)。

相关问题