SQL连表查询并统计数量

SELECT count(1) AS MAT_COUNT,

CS.CS_DBID,

CS.CS_SHOP_NAME FROM
PROJECT_PLAN_REQ_DETAIL AS PPRD
INNER JOIN GOODS_INFO AS GI ON
PPRD.PLAN_MAT_CODE = GI.GOODS_MAT_CODE
INNER JOIN COMP_SHOP AS CS ON GI.CS_DBID = CS.CS_DBID
WHERE PPRD.PLAN_PURCHASE_ID = '7cd13f026f794d4ba954b7c73901782c'
GROUP BY CS.CS_DBID,CS.CS_SHOP_NAME

 

xml中的书写：

<!--三个表连表查询-->
<select id="selectCompRankList" parameterType="java.lang.String" resultMap="compRankListMap">
    SELECT count(1) AS MAT_COUNT,
    CS.CS_DBID,
    CS.CS_SHOP_NAME
    FROM
    PROJECT_PLAN_REQ_DETAIL AS PPRD
    INNER JOIN GOODS_INFO AS GI
    ON PPRD.PLAN_MAT_CODE = GI.GOODS_MAT_CODE
    INNER JOIN COMP_SHOP AS CS
    ON GI.CS_DBID = CS.CS_DBID
    WHERE 1=1
    <if test="planPurchaseId != null and planPurchaseId !=''">
        AND PPRD.PLAN_PURCHASE_ID = #{planPurchaseId}
    </if>
    GROUP BY CS.CS_DBID, CS.CS_SHOP_NAME
</select>