SQL中关联表并使用子表的COUNT和SUM函数作为扩展字段

场景

主表wms_receive_order，

表wms_receive_order_details是其详情表。

详情表的receive_id字段与主表 的id字段相关联。

现在要查出主表的部分信息以及详情表的某个字段的COUNT值以及SUM值作为扩展

实体类中的一些属性。

实现

找到对应的mapper中的方法：

<select id="pageTray"  parameterType="com.ws.bus.sys.entity.WmsReceiveOrder" resultType="com.ws.bus.sys.vo.WmsOrderVo.WmsReceiveOrderVO">
        SELECT
        ro.*,
        sc.code_name status_name,
        su.name creater_name,
        (SELECT COUNT(*) FROM wms_receive_order_details  wrod where wrod.receive_id=ro.id and wrod.deleted_flag = 0) totalTray,
        (SELECT SUM(num) FROM wms_receive_order_details  wrod where wrod.receive_id=ro.id and wrod.deleted_flag = 0 ) totalNum
        FROM
        wms_receive_order ro
        LEFT JOIN sys_code sc ON sc.code_value= ro.status
        AND sc.code_type= 'receiveOrderStatus'
        LEFT JOIN sys_user su ON su.id =ro.creator_id
        WHERE
        ro.deleted_flag= '0'
      <if test="wmsReceiveOrder != null and wmsReceiveOrder.purchaseCode!=null and wmsReceiveOrder.purchaseCode!=''" >and ro.purchase_code=#{wmsReceiveOrder.purchaseCode} </if>
      <if test="wmsReceiveOrder != null and wmsReceiveOrder.status!=null and wmsReceiveOrder.status!=''" >and ro.status=#{wmsReceiveOrder.status} </if>
      <if test="wmsReceiveOrder != null and wmsReceiveOrder.supplierName!=null and wmsReceiveOrder.supplierName!=''" >and ro.supplier_name=#{wmsReceiveOrder.supplierName} </if>
    </select>

添加resultMap:

 

<resultMap type="com.ws.bus.sys.vo.WmsOrderVo.WmsReceiveOrderVO" id="receiveOrderMap">
        <result column="status_name" property="statusName"/>
        <result column="totalTray" property="totalTray"/>
        <result column="totalNum" property="totalNum"/>
        <result column="creater_name" property="createrName"/>
    </resultMap>