Oracle SQL中的替换功能 [英] Replace function in Oracle SQL

问题描述

我正在使用oracle SQL，并且有以下查询:

I'm using oracle SQL, and i have the following query:

select replace(replace('count(distinct <thiscol>) over (partition by <nextcol>) / count(*) over () as <thiscol>_<nextcol>,',
                       '<thiscol>', column_name
                      ), '<nextcol>', lead(column_name) over (order by column_id)
              )
from all_tab_columns atc
where table_name = 'mytable'

输出应该是如下查询:

select id,
       count(distinct name2) over (partition by name3) / count(*) over (),
       count(distinct name3) over (partition by name4) / count(*) over (),
       . . .
from mytable;

我希望得到代替:

count(distinct name2) over (partition by name3) / count(*) over ()

此查询:

count(distinct name3) over (partition by name2) / count(*) over ()

任何人都可以建议如何替换列值的顺序? (<thiscol>和<nextcol>).我尝试将<thiscol>替换为<nextcol>，但是它给了我相同的结果.我尝试了许多其他成功的事情.

Anyone can advise how to replace the order of the column values? (<thiscol> and <nextcol>). I tried to replace <thiscol> with <nextcol> but it gave me the same result. I tried many other things with of success.

有人吗?

推荐答案

真的很奇怪.相反，让我们以相反的顺序进行排序:

That is really strange. Instead, let's sort in the reverse order:

select replace(replace('count(distinct <thiscol>) over (partition by <nextcol>) / count(*) over () as <thiscol>_<nextcol>,',
                       '<thiscol>', column_name
                      ), '<nextcol>', lead(column_name) over (order by column_id desc)
              )
from all_tab_columns atc
where table_name = 'mytable';

请注意排序中的desc.

这篇关于Oracle SQL中的替换功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！