使用Oracle SQL按表中所有列的值计数 [英] Getting a count by value of all columns in a table using Oracle SQL

问题描述

由于各种原因，我工作的组织将数据存储在Oracle和MS SQL服务器数据库中.我们正在移动一些静态历史数据，并且我必须检查数据是否已正确移动. 下面的查询检查SQL Server中的数据，并生成一个表，该表列出了该表的每一列中所有值的计数. 由于Oracle中格式的差异，我将需要按其他两列Year和Iteration_count进行分组.我无法遍历在Oracle中工作的表中的所有列，因为我的经验几乎仅限于SQL Server

For various reasons the organisation I work for has data stored on both Oracle and MS SQL server databases. We are moving some static historical data over and I have to check that the data has been moved properly. The Query below checks the data in SQL server and produces a table listing counts of all the values in each column of the table. Due to formatting differences in Oracle I will need to group by two other columns Year and Iteration_count . I have not been able to get a loop through of all columns in a table working in Oracle as my experience is pretty much limited to SQL Server

DECLARE @SQL NVARCHAR(MAX) = ''
SELECT @SQL = STUFF((SELECT ' UNION SELECT ''' + name 
                            + ''' AS [Column], ' 
                            + 'CAST(' + QUOTENAME(Name)
                            + ' AS NVARCHAR(MAX)) AS [ColumnValue], COUNT(*) AS [Count] FROM ' 
                            +'dbo.HES_APC_ACP_9798' 
                            +' where (NUMACP IS NOT NULL AND NOT (NUMACP = 0) ) '
                            +' GROUP BY ' + QUOTENAME(Name)
                            --+'Order By [Column],[ColumnValue]'
                    FROM   sys.columns 
                    WHERE  object_id = Object_id('dbo.HES_APC_ACP_9798' )

                    FOR XML PATH ('')), 1, 7, '');

EXECUTE sp_executesql @SQL;

推荐答案

user_tab_columns上的此循环应有帮助:

This loop on user_tab_columns should help:

declare
  v_table varchar2(30) := 'TEST';
  v_sql varchar2(32767);
begin
  for r in (select column_name name from user_tab_cols 
              where table_name=v_table order by column_id)
  loop
    v_sql := v_sql||' union all select '''||r.name||''' col_name, to_char('
                  ||r.name||') col_value, count(1) cnt from '||v_table
                  ||' group by '||r.name||chr(13);
  end loop;
  v_sql := ltrim(v_sql, ' union all ');
  dbms_output.put_line(v_sql);
end;

测试表:

create table test (col1 varchar2(10), col2 number(5), col3 date);
insert into test values ('ABC', 1, null);
insert into test values ('DEF', 1, date '2015-06-18');

执行第一个PLSQL块输出:

Executing first PLSQL block outputs:

select 'COL1' col_name, to_char(COL1) col_value, count(1) cnt from TEST group by COL1
 union all select 'COL2' col_name, to_char(COL2) col_value, count(1) cnt from TEST group by COL2
 union all select 'COL3' col_name, to_char(COL3) col_value, count(1) cnt from TEST group by COL3

此查询的输出:

COL_NAME COL_VALUE           CNT
-------- ------------ ----------
COL1     DEF                   1
COL1     ABC                   1
COL2     1                     2
COL3                           1
COL3     15/06/18              1

如果从其他架构读取数据，请使用all_tab_cols并为owner添加过滤器.您也可以使用execute immediate语句运行生成的查询.

Use all_tab_cols and add filter for owner if you read data from other schema. You can also run generated query using execute immediate statement.

这篇关于使用Oracle SQL按表中所有列的值计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！