一对多sql
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 | <!-- 分页查询派货成本 --> <select id= "queryCostRegionPriceBycondtion" parameterMap= "CostRegionPriceQueryEntity" resultMap= "CostRegionPriceResultEntity" > 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 <![CDATA[SYSDATE > c.START_TIME]]> then 2 WHEN c.AUDIT_STATUS = 0 and <![CDATA[SYSDATE < c.START_TIME]]> 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 < if test= "auditStatus == 0" > and c.AUDIT_STATUS = '0' and <![CDATA[SYSDATE < c.START_TIME]]> </ if > < if test= "auditStatus == 1" > and c.AUDIT_STATUS = #{auditStatus,jdbcType=NUMERIC} </ if > < if test= "auditStatus == -1" > and c.AUDIT_STATUS = #{auditStatus,jdbcType=NUMERIC} </ if > < if test= "auditStatus == 2" > and c.AUDIT_STATUS = '0' and <![CDATA[SYSDATE > c.START_TIME]]> </ if > <!-- 报价名称 --> < if test= "costName != null and costName !=''" > AND b.COST_NAME like concat(concat( '%' ,#{costName,jdbcType=VARCHAR}), '%' ) </ if > <!-- 产品类型 --> < if test= "productCode != null and productCode !=''" > AND b.PRODUCT_CODE = #{productCode,jdbcType=NUMERIC} </ if > <!-- 派送方式 --> < if test= "disType != null and disType !='' or disType == 0" > AND b.DIS_TYPE = #{disType,jdbcType=NUMERIC} </ if > <!-- 开始派货区间 --> < if test= "starRegionId != null and starRegionId !='' and starRegionId !='empty'" > AND b.STAR_REGION_ID = #{starRegionId,jdbcType=VARCHAR} </ if > <!-- 结束派货区间 --> < if test= "endRegionId != null and endRegionId !='' and endRegionId !='empty'" > AND b.END_REGION_ID = #{endRegionId,jdbcType=VARCHAR} </ if > <!-- 业务类型 --> < if test= "bizType != null and bizType !=''" > AND b.BIZ_TYPE = #{bizType,jdbcType=NUMERIC} </ if > <!-- 启用状态--> < if test= "delFlag != null and delFlag !=''" > AND b.DEL_FLAG = #{delFlag,jdbcType=NUMERIC} </ if > <!-- 产品时效--> < if test= "timeCode != null and timeCode !=''" > AND b.TIME_CODE = #{timeCode,jdbcType=NUMERIC} </ if > <!-- 计费类型--> < if test= "calcType != null and calcType !=''" > AND b.CALC_TYPE = #{calcType,jdbcType=NUMERIC} </ if > < if test= "startTimeStart != null and startTimeEnd != null" > and <![CDATA[ b.START_TIME >= #{startTimeStart,jdbcType=TIMESTAMP}]]> and <![CDATA[ b.START_TIME <= #{startTimeEnd,jdbcType=TIMESTAMP}]]> </ if > < if test= "endTimeStart != null and endTimeEnd != null" > and <![CDATA[ b.END_TIME >= #{endTimeStart,jdbcType=TIMESTAMP}]]> and <![CDATA[ b.END_TIME <= #{endTimeEnd,jdbcType=TIMESTAMP}]]> </ if > <!-- 计费类型--> < if test= "priceItemCode != null and priceItemCode !=''" > AND b.PRICE_ITEM_CODE = #{priceItemCode,jdbcType=NUMERIC} </ if > order by b.CREATE_TIME desc </select> 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
一对多,(多)条件符合,取(多)时间最近的信息
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步