记录条数是分开显示? select count(*) from table1 union all select count(*) from table2 union all select count(*) from table3
没有关联,而且这个表集合可能有很多表,不能用union all 来实现
select (select count(*) from employees) c1, (select count(*) from emp) c2 from dual;
比如说,我一个表中一列字段注册了表名,另一列字段是对应表的记录数,如何用sql来update呢?
用存储过程吧,语句得是动态的--取表名 cursor t is select TABLE_NAME from user_tables where TABLE_NAME like 'TB%';--取记录数 sql:='select count(1) from '||游标.TABLE_NAME ;逻辑自己写吧
可以写个过程,通过动态语句来update 或者先对相关表进行分析,然后通过数据字典来取
create table table_count (table_name varchar2(38), record_count number); insert into table_count values ('T1', (select count(*) from t1)); insert into table_count values ('T2', (select count(*) from t2)); insert into table_count values ('T3', (select count(*) from t3)); commit;declare cursor cur_table_count is select table_name from table_count; c_record_count number; sql_text varchar2(1000); begin for c1 in cur_table_count loop sql_text := 'select count(*) from ' || c1.table_name; execute immediate sql_text into c_record_count; sql_text := 'update table_count set record_count = ' || c_record_count || ' where table_name = ''' || c1.table_name || ''''; execute immediate sql_text; end loop; end;
select count(*) as count from 表1 union all select count(*) as count from 表2 union all select count(*) as count from 表3
--1.批量生成count语句(in条件里的表名要大写) SELECT 'select count(*) from ' || table_name || ';' FROM user_tables t WHERE t.table_name IN ('表1', '表2', '表3'); --2.批量执行上面取出的语句
select sum(count(*)) from (select count(*) from 表1 union select count(*) from 表2 union select count(*) from 表3)
select table_name tabName,num_rows cnt from user_tables where table_name in(表集合)
既然你说有很多表,如果搞成一行肯定不好看,union是最好的.-- 如果是手工执行,执行下面的语句得到一个语句,把最后一个UNION ALL 替换成分号就可以了. -- 如果要放到程序里执行办法很多了.SELECT 'SELECT ''' || owner || '.' || table_name || ''' t_name, ' || '(SELECT COUNT(*) FROM ' || table_name || ') ct FROM DUAL UNION ALL ' FROM ALL_TABLES WHERE table_name LIKE 'TP%' ;
可以实现的 1、union all(union all为子查询) 2、通过where条件算count值 3、再写orader by
用嵌入式子查询不可以么? select (seect '表1',count(*) from 表1), (seect '表2',count(*) from 表2), (seect '表3',count(*) from 表3), ...from dual;
上面写的有问题,用union all来实现吧。 select '表1',count(*) from 表1 union all select '表2',count(*) from 表2 union all select '表3',count(*) from 表3
select count(*)
from table1
union all
select count(*)
from table2
union all
select count(*)
from table3
select (select count(*) from employees) c1,
(select count(*) from emp) c2
from dual;
cursor t is
select TABLE_NAME from user_tables
where TABLE_NAME like 'TB%';--取记录数
sql:='select count(1) from '||游标.TABLE_NAME ;逻辑自己写吧
或者先对相关表进行分析,然后通过数据字典来取
create table table_count (table_name varchar2(38), record_count number);
insert into table_count values ('T1', (select count(*) from t1));
insert into table_count values ('T2', (select count(*) from t2));
insert into table_count values ('T3', (select count(*) from t3));
commit;declare
cursor cur_table_count is select table_name from table_count;
c_record_count number;
sql_text varchar2(1000);
begin
for c1 in cur_table_count loop
sql_text := 'select count(*) from ' || c1.table_name;
execute immediate sql_text into c_record_count;
sql_text := 'update table_count set record_count = ' || c_record_count || ' where table_name = ''' || c1.table_name || '''';
execute immediate sql_text;
end loop;
end;
union all
select count(*) as count from 表2
union all
select count(*) as count from 表3
SELECT 'select count(*) from ' || table_name || ';'
FROM user_tables t
WHERE t.table_name IN ('表1', '表2', '表3');
--2.批量执行上面取出的语句
select table_name tabName,num_rows cnt from user_tables where table_name in(表集合)
-- 如果要放到程序里执行办法很多了.SELECT 'SELECT ''' || owner || '.' || table_name || ''' t_name, ' || '(SELECT COUNT(*) FROM ' || table_name || ') ct FROM DUAL UNION ALL '
FROM ALL_TABLES
WHERE table_name LIKE 'TP%'
;
1、union all(union all为子查询)
2、通过where条件算count值
3、再写orader by
select
(seect '表1',count(*) from 表1),
(seect '表2',count(*) from 表2),
(seect '表3',count(*) from 表3),
...from dual;
select '表1',count(*) from 表1
union all
select '表2',count(*) from 表2
union all
select '表3',count(*) from 表3