如何在Oracle查询中添加具有依赖于类型的值的列[已关闭]

ryhaxcpt  于 2023-06-29  发布在  Oracle
关注(0)|答案(1)|浏览(108)

已关闭,此问题需要details or clarity。目前不接受答复。
**想改善这个问题吗?**通过editing this post添加详细信息并澄清问题。

6天前关闭
Improve this question
我需要根据查询的结果显示列X:}
I need like that

select 1 id_t, 'Book 1' val, 'C1' categ, 'S' res from dual union
select 2 id_t, 'Book 2' val, 'C1' categ, null res from dual union
select 3 id_t, 'Book 3' val, 'C1' categ, null res from dual union

select 4 id_t, 'Book 4' val, 'C2' categ, 'S' res from dual union

select 5 id_t, 'Book 5' val, 'C3' categ, 'S' res from dual union
select 6 id_t, 'Book 6' val, 'C3' categ, null res from dual

我需要根据类别对“res”列的值进行分组
我什么都没试过

qcuzuvrc

qcuzuvrc1#

样品表:

SQL> select * From test;

      ID_T VAL    CATEG RES
---------- ------ ----- ---
         1 Book 1 C1
         2 Book 2 C1
         3 Book 3 C1
         4 Book 4 C2
         5 Book 5 C3
         6 Book 6 C3

6 rows selected.

SQL>

一种选择是使用row_number解析函数按ID对每个类别的行进行 * 排序 *,然后为排名最高的行设置res

SQL> with temp as
  2    (select id_t, val, categ,
  3       row_number() over (partition by categ order by id_t) rn
  4    from test
  5    )
  6  select id_t, val, categ,
  7    case when rn = 1 then 'S' end res
  8  from temp;

      ID_T VAL    CATEG RES
---------- ------ ----- ---
         1 Book 1 C1    S
         2 Book 2 C1
         3 Book 3 C1
         4 Book 4 C2    S
         5 Book 5 C3    S
         6 Book 6 C3

6 rows selected.

SQL>

相关问题