SQL查询主表数据和子表部分数据统计

1. 查询 子表中，不同的类型统计，
   主表 几千条，子表60万数据，下面查询花了 40秒。

   select
   su.* ,
   ( select count(*) from dtl e where e.check_stat=’EQ’ and e.batch_no=su.batch_No) EQ,
   ( select count(*) from dtl w where w.check_stat !=’EQ’ and w.batch_no=su.batch_No) notEQ
   from t_SUM su
   where 1=1
   经过 DBA 优化，先查询数据，分组统计之后，再关联，60万的子表速度只有0.34秒

select su.*, w.EQ, w.notEQ
from T_SUM su,
(select batch_no, sum(EQ) EQ, sum(notEQ) notEQ
from (select batch_no,
decode(check_stat, ‘EQ’, 1, 0) EQ,
decode(check_stat, ‘EQ’, 0, 1) notEQ
from dtl)
group by batch_no) w
where su.batch_No = w.batch_no;

1. mysql 统计不在子表出现的数据。 数据量6万花费了2秒钟，

select count(1) from applies ap where ap.transaction_status=’0’ and not exists(
select 1 from readrecords r on ap.id =r.applies_id )

按照1的思路，先出数据，再汇总，先查询总数据，再查询等值关联的数据，那么剩下来的就是不在 表里面的数据。

SELECT
(
SELECT
count(*) sum1
FROM
applies cc
WHERE
cc.transaction_status = ‘0’
) - (
SELECT
count(DISTINCT ap.id) sum2
FROM
applies ap
INNER JOIN readrecords r ON ap.id = r.applies_id
WHERE
ap.transaction_status = ‘0’
)