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

ryhaxcpt 于 2023-06-29 发布在 Oracle

关注(0)|答案(1)|浏览(109)

已关闭，此问题需要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”列的值进行分组
我什么都没试过

oracle

来源：https://stackoverflow.com/questions/76534189/how-add-a-column-with-a-value-depending-on-a-type-in-an-oracle-query

1条答案

按热度按时间

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>

赞(0）回复(0）举报 2023-06-29

我来回答

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

1条答案

相关问题

热门标签

最新问答