我如何从两个不同的表(叫他们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
当前回答
作为附加信息，要在SQL Server中完成同样的事情，您只需要删除查询的“FROM dual”部分。
其他回答
作为附加信息，要在SQL Server中完成同样的事情，您只需要删除查询的“FROM dual”部分。
因为我找不到其他答案了。
如果你不喜欢子查询并且在每个表中都有主键，你可以这样做:
select count(distinct tab1.id) as count_t1,
count(distinct tab2.id) as count_t2
from tab1, tab2
但是就性能而言，我认为Quassnoi的解决方案更好，也是我会使用的解决方案。
选择 (select count() from tab1 where field like 'value') + (select count() from tab2 where field like 'value') 数
为了完整起见，这个查询将创建一个查询，为您提供给定所有者的所有表的计数。
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 @count = sum(data) from
(
select count(*) as data from #tempregion
union
select count(*) as data from #tempmetro
union
select count(*) as data from #tempcity
union
select count(*) as data from #tempzips
) a
