Question

SQL语句中多表count

select
count(a.id) as col1, count(b.id) as col2
from
( select id from table1) a,
(select id from table 2) b
这样返回的值貌似有问题
单独执行a的时候返回897条数据
单独执行b的时候返回8条数据
如果一切正常的话，上面的sql语句会同时返回897和8，但是目前的返回值都是6272，是怎么回事？

Answer 1

你这个相当于是下列语句：
select count(*) from (select id from table1) a,(select id from table2) b
where 条件 group by a.id,b.id
所以会是6272条
你可以改成如下：
select a.num,b.num from
(select count(id) num from table1 group by id)a
(select count(id) num from table2 group by id)b

Answer 2

select
a. col1, b.col2
from
(select count(id) as col1 from table1) as a,
(select count(id) as col2 from table2) as b
-------------这样写。

Answer 3

目前情况是迪笛卡尔积 结果是897乘以8，a,b 表的关联条件。

Answer 4

select a.AAA,b.BBB from
(select count(*) as AAA from 要查的表A where THETIME<=to_date('2018-03-07 13:58:36','yyyy-mm-dd hh24:mi:ss')) a,
(select count(*) as BBB from 要查的表B where THETIME<=to_date('2018-03-07 13:58:36','yyyy-mm-dd hh24:mi:ss')) b;

Answer 5

两张表的关联字段是什麽？没有关联字段才会出现6272
把关联字段条件加上再看看