我如何从两个不同的表(叫他们tab1和tab2)选择计数(*)有作为结果:
Count_1 Count_2
123 456
我试过了:
select count(*) Count_1 from schema.tab1 union all select count(*) Count_2 from schema.tab2
但我所拥有的只有:
Count_1
123
456
我如何从两个不同的表(叫他们tab1和tab2)选择计数(*)有作为结果:
Count_1 Count_2
123 456
我试过了:
select count(*) Count_1 from schema.tab1 union all select count(*) Count_2 from schema.tab2
但我所拥有的只有:
Count_1
123
456
当前回答
为了完整起见,这个查询将创建一个查询,为您提供给定所有者的所有表的计数。
select
DECODE(rownum, 1, '', ' UNION ALL ') ||
'SELECT ''' || table_name || ''' AS TABLE_NAME, COUNT(*) ' ||
' FROM ' || table_name as query_string
from all_tables
where owner = :owner;
输出是这样的
SELECT 'TAB1' AS TABLE_NAME, COUNT(*) FROM TAB1
UNION ALL SELECT 'TAB2' AS TABLE_NAME, COUNT(*) FROM TAB2
UNION ALL SELECT 'TAB3' AS TABLE_NAME, COUNT(*) FROM TAB3
UNION ALL SELECT 'TAB4' AS TABLE_NAME, COUNT(*) FROM TAB4
然后你可以运行它来得到你的计数。有时它只是一个方便的脚本。
其他回答
select (select count(*) from tab1) count_1, (select count(*) from tab2) count_2 from dual;
--============= FIRST WAY (Shows as Multiple Row) ===============
SELECT 'tblProducts' [TableName], COUNT(P.Id) [RowCount] FROM tblProducts P
UNION ALL
SELECT 'tblProductSales' [TableName], COUNT(S.Id) [RowCount] FROM tblProductSales S
--============== SECOND WAY (Shows in a Single Row) =============
SELECT
(SELECT COUNT(Id) FROM tblProducts) AS ProductCount,
(SELECT COUNT(Id) FROM tblProductSales) AS SalesCount
SELECT (
SELECT COUNT(*)
FROM tab1
) AS count1,
(
SELECT COUNT(*)
FROM tab2
) AS count2
FROM dual
这是我的分享
选项1 -计数从相同的域从不同的表
select distinct(select count(*) from domain1.table1) "count1", (select count(*) from domain1.table2) "count2"
from domain1.table1, domain1.table2;
选项2 -同一表从不同的域计数
select distinct(select count(*) from domain1.table1) "count1", (select count(*) from domain2.table1) "count2"
from domain1.table1, domain2.table1;
选项3 -计数从不同的领域为同一表与“联合所有”有行计数
select 'domain 1'"domain", count(*)
from domain1.table1
union all
select 'domain 2', count(*)
from domain2.table1;
享受SQL,我总是这样做:)
Declare @all int
SET @all = (select COUNT(*) from tab1) + (select count(*) from tab2)
Print @all
or
SELECT (select COUNT(*) from tab1) + (select count(*) from tab2)