SELECT *
FROM
(
SELECT
t1.broker_id,t1.broker_name,t2.thscode,t2.sec_id,sec_type,
t2.is_listing,t2.listed_status_code,t2.listed_status,t2.td_mkt,t2.sec_short_name_cn
FROM ashare_broker_monthly_report t1 INNER JOIN sec_basic_info t2
ON t1.broker_id = t2.issue_org_id
where listed_status_code ='213001'
AND is_listing =1
AND td_mkt_encode in ('212001','212100')
AND sec_type = 'A股'
GROUP BY t1.broker_id,t1.broker_name,t2.thscode,t2.sec_id
) m
INNER JOIN
(
SELECT dt,sec_type,sec_id, pe_ttm_ed 'PE_TTM(截止日期)',pb_lastest_ed 'PB_最新(截止日期)'
FROM stock_ttm_valuation_index
WHERE sec_type = 'A股' AND dt >= '2020-08-10' AND dt < '2020-08-17'
GROUP BY dt,sec_type,sec_id
ORDER BY dt DESC
) n
ON m.sec_id = n.sec_id ;