Oracle查询调整：标注表字段是否为主键，避免表名与总行数重复

方案说明 #

你需要实现两个核心需求：单字段主键标识、同表重复信息合并隐藏，以下是完整实现步骤：

方式一：新增单字段主键判断函数实现 #

1. 新建is_pk_col判断函数 #

传入表名和字段名，直接返回该字段是否为主键的YES/NO标识：

create or replace function is_pk_col (par_table_name in varchar2, par_column_name in varchar2)
  return varchar2
is
  l_cnt number;
begin
  select count(*)
    into l_cnt
    from user_constraints a 
    join user_cons_columns b on b.constraint_name = a.constraint_name
    where upper(a.table_name) = dbms_assert.sql_object_name(upper(par_table_name))
      and a.constraint_type = 'P'
      and upper(b.column_name) = upper(par_column_name);
  return case when l_cnt > 0 then 'YES' else 'NO' end;
end;
/

2. 最终查询SQL #

使用窗口函数按表分组排序，仅第一行展示表名和总行数：

select 
  case when rn = 1 then table_name else null end as TABLE_NAME,
  case when rn = 1 then total_rows else null end as TOTAL_ROWS,
  column_name as COLUMN_NAME,
  data_type as DATATYPE,
  is_pk as PRIMKEY_COLS
from (
  select 
    a.table_name,
    count_rows(a.table_name) total_rows,
    a.column_name,
    a.data_type,
    is_pk_col(a.table_name, a.column_name) is_pk,
    row_number() over(partition by a.table_name order by a.column_id) rn
  from user_tab_columns a
) t
order by t.table_name, t.rn;

方式二：无需新增函数的纯SQL实现 #

如果你不想新增自定义函数，也可以直接通过左连接系统视图实现主键判断，性能更优：

select 
  case when rn = 1 then table_name else null end as TABLE_NAME,
  case when rn = 1 then total_rows else null end as TOTAL_ROWS,
  column_name as COLUMN_NAME,
  data_type as DATATYPE,
  is_pk as PRIMKEY_COLS
from (
  select 
    a.table_name,
    count_rows(a.table_name) total_rows,
    a.column_name,
    a.data_type,
    case when b.column_name is not null then 'YES' else 'NO' end is_pk,
    row_number() over(partition by a.table_name order by a.column_id) rn
  from user_tab_columns a
  left join (
    select b.table_name, b.column_name
    from user_constraints a 
    join user_cons_columns b on b.constraint_name = a.constraint_name
    where a.constraint_type = 'P'
  ) b on a.table_name = b.table_name and a.column_name = b.column_name
) t
order by t.table_name, t.rn;

逻辑说明 #

• 用row_number() over(partition by table_name order by column_id)对每个表的字段按建表顺序编号，同一个表的第一个字段编号为1
• 外层查询通过case判断，仅编号为1的行显示表名和总行数，其余行留空，实现重复信息隐藏
• 主键判断通过关联主键约束系统视图实现，存在匹配则为YES，否则为NO

内容的提问来源于stack exchange，提问作者GBA_Switch