SQL查询，如何添加count（*）列

I have this query:

我有这个问题:

select segment_name,owner,blocks*8192/1024/1024 as MB,tablespace_name
from dba_segments
where segment_name like 'AUD_2%' and owner like 'AUDITOR'
order by 1 desc;

SEGMENT_NAME     OWNER              MB TABLESPACE_NAME
---------------- ---------- ---------- ----------------
AUD_201304       AUDITOR             7 WSS     
AUD_201303       AUDITOR            12 WSS     
AUD_201302       AUDITOR            11 WSS

how to add count(*) column ?

如何添加count(*)列?

I guess a correlated subquery would do, but how exactly ?

我猜一个相关的子查询会做,但究竟是怎么回事?

Thanks !

sorry found code on *, should better search next time. thanks

抱歉发现*上的代码,下次应该更好搜索。谢谢

sorry, here the link to the solution: How to count(*) of multiple tables, size and tablespace in one query

抱歉,这里是解决方案的链接:如何在一个查询中计算(*)多个表,大小和表空间

and here the code:

这里的代码:

SELECT ut.table_name,
           to_number(extractvalue(xmltype (dbms_xmlgen.getxml ('select count(*) c from '         ||ut.table_name)),'/ROWSET/ROW/C')) row_count,
       db.blocks*8192/1024/1024 as MB,
       db.tablespace_name
FROM user_tables ut
  join dba_segments db on db.segment_name = ut.table_name
WHERE ut.table_name LIKE 'AUD_2%' and owner like 'AUDITOR'
ORDER BY ut.table_name DESC;

and here the output:

在这里输出:

TABLE_NAME                      ROW_COUNT         MB TABLES
------------------------------ ---------- ---------- ------
AUD_201304                          21067          7 WSS
AUD_201303                          43198         12 WSS
AUD_201302                          39046         11 WSS
AUD_201301                          44523         17 WSS
AUD_201212                          50580         15 WSS
AUD_201211                          49589         14 WSS

4 个解决方案

#1

Try:

select 
        segment_name,
        owner,
        blocks*8192/1024/1024 as MB,
        tablespace_name,
        (select num_rows from dba_tables where table_name=segment_name) TOTAL_ROWS
from dba_segments
where segment_name like 'AUD_2%' and owner like 'AUDITOR'
order by 1 desc;

#2

You could retrieve @@ROW_COUNT at the end of your query

您可以在查询结束时检索@@ ROW_COUNT

#3

It is not clear what you want to count. But if you want to count the number of rows being returned, then use an analytic function:

目前尚不清楚你想要计算什么。但是,如果要计算返回的行数,请使用分析函数:

select segment_name, owner, blocks*8192/1024/1024 as MB, tablespace_name,
       count(*) over () as cnt
from dba_segments
where segment_name like 'AUD_2%' and owner like 'AUDITOR'
order by 1 desc;

#4

"gives column with number of tables, not the number of records in every table "

“为列提供表格数量,而不是每个表格中的记录数量”

Youy are mixing two differnet concepts. data and metadata.

你们正在混合两个不同的概念。数据和元数据。

The query you have is queryiung the data dictionary to get some information about your tables as objects in the database. This is metadata: data about the data.

您拥有的查询是查询数据字典,以获取有关您的表的一些信息作为数据库中的对象。这是元数据:有关数据的数据。

Whereas a count of how many rows each table holds is just data.

而每个表保存多少行的计数只是数据。

You have two options. The first is to join the DBA_TABLES view to your query and se;ect NUM_ROWS. If your statistics are reasonably fresh and you only want an indicative figure this would probably suffice.

你有两个选择。第一种是将DBA_TABLES视图加入到您的查询中并选择NUM_ROWS。如果您的统计数据相当新鲜,并且您只想要一个指示性数字,这可能就足够了。

If you don't use statistics onthese tables or you want a highly accurate count you will need to use PL/SQL.

如果您不使用这些表的统计数据或者您想要高度准确的计数,则需要使用PL / SQL。

create or replace function tab_row_cnt ( tname in user_tables.table_Nmae%type)
return pls_integer
is
  n pls_integer;
begin
  execute immediate 'select count(*) from '||tname into n;
  return n;
end;

You can include this function in your query's projection.

您可以在查询的投影中包含此功能。

select segment_name,owner,blocks*8192/1024/1024 as MB,tablespace_name
       , tab_row_cnt (segment_name) as row_count
from dba_segments
where segment_name like 'AUD_2%' and owner like 'AUDITOR'
and segment_type = 'TABLE'
order by 1 desc;

Note that I added a test on SEGMENT_TYPE. You will need to amend the function's logic if your tables are partitioned, or if you want to include index segments in the query.

请注意,我在SEGMENT_TYPE上添加了一个测试。如果表是分区的,或者如果要在查询中包含索引段,则需要修改函数的逻辑。

Be aware that if your tables are large counting could take a long time and dramatically slow down your query. Speed is another advantage of using the approximation offered by USER_TABLES.NUM_ROWS.

请注意,如果您的表计数很大,可能需要很长时间才能大大减慢查询速度。速度是使用USER_TABLES.NUM_ROWS提供的近似值的另一个优点。

#1