mysql多表对比查询

select 'est',count(AC_NO) as generated_ac_no from t_advice_index where BACKET_PATH = 'e-statement.20230930'
union all
select 't24',count(distinct ACC_NO) as T24_ac_no from t_statement where FILE_NAME='e-statement.20230930'

 

SELECT * FROM (
select count(AC_NO) as generated_ac_no from t_advice_index where BACKET_PATH = 'e-statement.20230930' ) A
JOIN (
select count(distinct ACC_NO) as T24_ac_no from t_statement where FILE_NAME='e-statement.20230930' ) B

 

select substr(BACKET_PATH,-8),count(AC_NO) from t_advice_index where BACKET_PATH like 'e-statement.%'
GROUP BY substr(BACKET_PATH,-8)
order by substr(BACKET_PATH,-8) desc

 

select *,T24_AC_NO - EST_AC_NO from (
select substr(BACKET_PATH,-8) as mon_est,count(AC_NO) as EST_AC_NO from t_advice_index where BACKET_PATH like 'e-statement.%'
group by substr(BACKET_PATH,-8)
order by substr(BACKET_PATH,-8) desc
) A
left join (
select count(distinct ACC_NO) as T24_AC_NO,SUBSTR(FILE_NAME,-8) as mon_t24 from t_statement where FILE_NAME like 'e-statement.%'
group by SUBSTR(FILE_NAME,-8)
order by SUBSTR(FILE_NAME,-8) desc
) B
on a.mon_est = b.mon_t24

 

select *,T24_AC_NO - EST_AC_NO from (
select BACKET_PATH as mon_est,count(AC_NO) as EST_AC_NO from t_advice_index where BACKET_PATH like 'e-statement.%'
group by BACKET_PATH
order by BACKET_PATH desc
) A
left join (
select count(distinct ACC_NO) as T24_AC_NO,FILE_NAME as mon_t24 from t_statement where FILE_NAME like 'e-statement.%'
group by FILE_NAME
order by FILE_NAME desc
) B
on a.mon_est = b.mon_t24

 

select *,T24_AC_NO - EST_AC_NO from
(select BACKET_PATH as mon_est,count(DISTINCT AC_NO) as EST_AC_NO from t_advice_index where BACKET_PATH like 'e-statement.%' group by BACKET_PATH order by BACKET_PATH desc) A
left join
(select count(distinct ACC_NO) as T24_AC_NO,FILE_NAME as mon_t24 from t_statement where FILE_NAME like 'e-statement.%' group by FILE_NAME order by FILE_NAME desc) B
on a.mon_est = b.mon_t24

 

select * from
(SELECT * from
(select distinct AC_NO as EST_AC_NO from t_advice_index where BACKET_PATH like 'e-statement.20230731' ORDER BY AC_NO) A
right join
(select DISTINCT ACC_NO as T24_AC_NO,FILE_NAME as mon_t24 from t_statement where FILE_NAME like 'e-statement.20230731' ORDER BY ACC_NO) B
on A.EST_AC_NO = B.T24_AC_NO) C WHERE EST_AC_NO is null

 

 

select *,T24_AC_NO - EST_AC_NO as Diff from
(select BACKET_PATH as mon_est,count(DISTINCT AC_NO) as EST_AC_NO from t_advice_index where BACKET_PATH like 'e-statement.%' group by BACKET_PATH order by BACKET_PATH desc) A
left join
(select count(distinct ACC_NO) as T24_AC_NO,FILE_NAME as mon_t24 from t_statement where FILE_NAME like 'e-statement.%' group by FILE_NAME order by FILE_NAME desc) B
on a.mon_est = b.mon_t24

select * from
(SELECT * from
(select distinct AC_NO as EST_AC_NO from t_advice_index where BACKET_PATH like 'e-statement.20230930' ORDER BY AC_NO) A
right join
(select DISTINCT ACC_NO as T24_AC_NO,FILE_NAME as mon_t24 from t_statement where FILE_NAME like 'e-statement.20230930' ORDER BY ACC_NO) B
on A.EST_AC_NO = B.T24_AC_NO) C WHERE EST_AC_NO is null

 

select *,T24_AC_NO - EST_AC_NO as Diff from
(select BACKET_PATH as mon_est,count(DISTINCT AC_NO) as EST_AC_NO from t_advice_index where BACKET_PATH like 'e-statement.%' group by BACKET_PATH order by BACKET_PATH desc) A
left join
(select count(distinct ACC_NO) as T24_AC_NO,FILE_NAME as mon_t24 from t_statement where FILE_NAME like 'e-statement.%' group by FILE_NAME order by FILE_NAME desc) B
on a.mon_est = b.mon_t24

select * from
(SELECT * from
(select distinct AC_NO as EST_AC_NO from t_advice_index where BACKET_PATH like 'e-statement.%' ORDER BY AC_NO) A
right join
(select DISTINCT ACC_NO as T24_AC_NO,FILE_NAME as mon_t24 from t_statement where FILE_NAME like 'e-statement.%' ORDER BY ACC_NO) B
on A.EST_AC_NO = B.T24_AC_NO) C WHERE EST_AC_NO is null order by mon_t24 desc