SQL sql使用单个查询从多个表中计算记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10185546/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sql count records in from multiple tables using single query
提问by cjd143SD
I have a list of tables for example:
我有一个表格列表，例如：
mytableA
mytableB
mytableC
The tables all have same column (timestamp).
这些表都有相同的列（时间戳）。
I can do a count on each table individually:
我可以单独计算每张桌子：
select count(*) from mytableA where timestamp = to_char(sysdate-1, 'yyyymmdd') || '0000';
select count(*) from mytableB where timestamp = to_char(sysdate-1, 'yyyymmdd') || '0000';
select count(*) from mytableC where timestamp = to_char(sysdate-1, 'yyyymmdd') || '0000';
How can I combine this in one single query? Is there an easy way?
如何将其合并到一个查询中？有没有简单的方法？
Expected Results:
预期成绩：
MyTableName MyCnt
----------- -----
mytableA 121
mytableB 78
mytableC 2345
回答by COLD TOLD
SELECT (
SELECT COUNT(*)
FROM table1
) AS tot1,
(
SELECT COUNT(*)
FROM table2
) AS tottab2,
(
SELECT COUNT(*)
FROM table3
) AS tottab3
回答by Florin Ghita
You can't do it directly with a query like where table in (myTableA, myTableB, etc)But you can pretify the union allsolution:
您不能直接使用类似的查询执行此操作，where table in (myTableA, myTableB, etc)但您可以美化union all解决方案：
select MyTableName, count(*)
FROM(
select 'myTableA' MyTableName, timestamp from mytableA
union all
select 'myTableB', timestamp from mytableB
union all
select 'myTableA', timestamp from mytableC
)
WHERE timestamp = to_char(sysdate-1, 'yyyymmdd') || '0000'
GROUP BY MyTableName;
回答by arun.v1
I am not sure about Oracle, in SQLserver you can do this as,
我不确定 Oracle，在 SQLserver 中你可以这样做，
select (select count(*) from table1) + (select count(*) from table2)
Update:or like this,
更新：或者像这样，
select (select count(*) from table1) ,(select count(*) from table2)
OR,
或者，
(select count(*) from table1) union (select count(*) from table2)
回答by Mikael Eriksson
select 'myTableA' MyTableName, count(*) MyCnt from mytableA where timestamp = to_char(sysdate-1, 'yyyymmdd') || '0000'
union all
select 'myTableB', count(*) from mytableB where timestamp = to_char(sysdate-1, 'yyyymmdd') || '0000'
union all
select 'myTableC', count(*) from mytableC where timestamp = to_char(sysdate-1, 'yyyymmdd') || '0000';
回答by hkutluay
Try this
尝试这个
select 'mytableA' as tablename, count(*) from mytableA where timestamp = to_char(sysdate-1, 'yyyymmdd') || '0000'
union all
select 'mytableB' as tablename , count(*) from mytableB where timestamp = to_char(sysdate-1, 'yyyymmdd') || '0000'
union all
select'mytableB' as tablename , count(*) from mytableC where timestamp = to_char(sysdate-1, 'yyyymmdd') || '0000'
回答by leanne
Using Oracle 11gR2:
使用 Oracle 11gR2：
select
(select count(*) from mytableA where timestamp = to_char(sysdate-1, 'yyyymmdd') || '0000') tabA,
(select count(*) from mytableB where timestamp = to_char(sysdate-1, 'yyyymmdd') || '0000') tabB,
(select count(*) from mytableC where timestamp = to_char(sysdate-1, 'yyyymmdd') || '0000') tabC
from dual;
Results in:
结果是：
tabA| tabB| tabC
----|-----|-----
121| 78| 2345
回答by aquatorrent
How about this?
这个怎么样？
SELECT
a, b, c
FROM
(select count(*) from mytableA where timestamp = to_char(sysdate-1, 'yyyymmdd') || '0000') AS a,
(select count(*) from mytableB where timestamp = to_char(sysdate-1, 'yyyymmdd') || '0000') AS b,
(select count(*) from mytableC where timestamp = to_char(sysdate-1, 'yyyymmdd') || '0000') AS c
dunno whether this works or not though :/
不知道这是否有效：/
