SELECT		b.AUDIT_HOUR,		b.OPER_STATUS,		b.RELE_ID,		b.STAR_REGION_ID,		b.END_REGION_ID,		b.START_REGION__NAME,		b.END_REGION_NAME,		b.CREATE_USER_CODE||'('||e1.EMPLOYEE_NAME||')' CREATE_USER_CODE,		b.MODIFY_USER_CODE,		b.CREATE_TIME,		b.MODIFY_TIME,		b.DEL_FLAG,		b.REMARK,		b.COST_NAME,		b.START_REGION_TYPE,		b.END_REGION_TYPE,		b.CALC_TYPE,		b.BIZ_TYPE,		b.TIME_CODE,		b.PRODUCT_CODE,		b.DIS_TYPE,		b.START_TIME,		b.END_TIME,		b.PRICE_ITEM_CODE,				c.audit_time,		c.audit_user_code||'('||e2.EMPLOYEE_NAME||')' audit_user_code,		case WHEN  		c.AUDIT_STATUS = 0 and 		 c.START_TIME]]>		then 2		WHEN  		c.AUDIT_STATUS = 0 and 				then 0 		WHEN		c.AUDIT_STATUS = 1 then 1		WHEN		c.AUDIT_STATUS = -1 then -1		ELSE 4 		END AUDIT_STATUS	FROM		T_COST_REGION_PRICE b				left join T_BASE_EMPLOYEE e1		on b.CREATE_USER_CODE = e1.EMPLOYEE_CODE		left join 		(			SELECT				A .audit_time,				A .audit_user_code,				A .rele_id,				A .AUDIT_status,		    A.START_TIME,		    A.audit_hour			FROM				(					SELECT						ROW_NUMBER () OVER (							PARTITION BY T .rele_id							ORDER BY								T .audit_time ASC						) AS rn,						T .*					FROM						t_cost_price_ad T						where 1=1										) A			WHERE				A .rn = 1		) c		  on b.rele_id=c.rele_id	left join T_BASE_EMPLOYEE e2		  on c.audit_user_code =e2.EMPLOYEE_CODE	WHERE 1=1		      		and c.AUDIT_STATUS = '0' and       		        		      		and c.AUDIT_STATUS = #{auditStatus,jdbcType=NUMERIC}        		      		and c.AUDIT_STATUS = #{auditStatus,jdbcType=NUMERIC}        		      		and c.AUDIT_STATUS = '0' and       		 c.START_TIME]]>        		 
                   		AND b.COST_NAME like concat(concat('%',#{costName,jdbcType=VARCHAR}),'%')      		 
                   		AND b.PRODUCT_CODE = #{productCode,jdbcType=NUMERIC}      		 
                   		AND b.DIS_TYPE = #{disType,jdbcType=NUMERIC}      		 
                   		AND b.STAR_REGION_ID = #{starRegionId,jdbcType=VARCHAR}      		 
                   		AND b.END_REGION_ID = #{endRegionId,jdbcType=VARCHAR}      		 
                   		AND b.BIZ_TYPE = #{bizType,jdbcType=NUMERIC}      		 
                   		AND b.DEL_FLAG = #{delFlag,jdbcType=NUMERIC}      		 
                   		AND b.TIME_CODE = #{timeCode,jdbcType=NUMERIC}      		 
                   		AND b.CALC_TYPE = #{calcType,jdbcType=NUMERIC}                      and = #{startTimeStart,jdbcType=TIMESTAMP}]]>          and                       and = #{endTimeStart,jdbcType=TIMESTAMP}]]>          and                
                   		AND b.PRICE_ITEM_CODE = #{priceItemCode,jdbcType=NUMERIC}            order by b.CREATE_TIME desc  

　　

SELECT b.AUDIT_HOUR, b.OPER_STATUS, b.RELE_ID, b.STAR_REGION_ID, b.END_REGION_ID, b.START_REGION__NAME, b.END_REGION_NAME, b.CREATE_USER_CODE||'('||e1.EMPLOYEE_NAME||')' CREATE_USER_CODE, b.MODIFY_USER_CODE, b.CREATE_TIME, b.MODIFY_TIME, b.DEL_FLAG, b.REMARK, b.COST_NAME, b.START_REGION_TYPE, b.END_REGION_TYPE, b.CALC_TYPE, b.BIZ_TYPE, b.TIME_CODE, b.PRODUCT_CODE, b.DIS_TYPE, b.START_TIME, b.END_TIME, b.PRICE_ITEM_CODE, c.audit_time, c.audit_user_code||'('||e2.EMPLOYEE_NAME||')' audit_user_code FROM T_COST_REGION_PRICE b left join T_BASE_EMPLOYEE e1 on b.CREATE_USER_CODE = e1.EMPLOYEE_CODE left join ( SELECT A .audit_time, A .audit_user_code, A .rele_id, A .AUDIT_status, A.START_TIME, A.audit_hour FROM ( SELECT ROW_NUMBER () OVER ( PARTITION BY T .rele_id ORDER BY T .audit_time ASC ) AS rn, T .* FROM t_cost_price_ad T where 1=1 ) A WHERE A .rn = 1 ) c on b.rele_id=c.rele_id left join T_BASE_EMPLOYEE e2 on c.audit_user_code =e2.EMPLOYEE_CODE WHERE 1=1

　　

SELECT                A .audit_time,                A .audit_user_code,                A .rele_id,                A .AUDIT_status,            A.START_TIME,            A.audit_hour            FROM                (                    SELECT                        ROW_NUMBER () OVER (                            PARTITION BY T .rele_id                            ORDER BY                                T .audit_time ASC                        ) AS rn,                        T .*                    FROM                        t_cost_price_ad T                        where 1=1                                        ) A            WHERE                A .rn = 1

 

 

一对多，（多）条件符合，取（多）时间最近的信息