mysql表内自连接获取时间范围内的最小时间
需求 同个投资人对某个企业一个年/月有多次投资记录,获取某个时间范围内最小的投资时间作为新增时间
SELECT ep.id,ep.enterprise_name,stt.dividend_enterprise_name
FROM enterprise AS ep # 企业表
LEFT JOIN statement_investment AS stt ON ep.id = stt.eid # 投资表
LEFT JOIN (
SELECT id,eid,MIN(tzsj) AS tzsj
FROM statement_investment
WHERE finally_audit_status = 2 # 投资子表
AND is_in_group = 1 # 集团内部企业
GROUP BY eid
) AS tz ON (stt.eid = tz.eid AND stt.tzsj = tz.tzsj AND tz.tzsj >='2020-10-01' AND tz.tzsj <='2022-03-31' )
WHERE ep.is_del = 0 # 未逻辑删除
AND ep.fictitious = 0 # 虚拟公司 0否1是
AND ep.business_status NOT IN ('注销','转让','迁出') # 剔除的经营状态
AND tz.id IS NOT NULL # 有投资子表