mysql把两个表中统计一起展示

最新推荐文章于 2024-03-19 18:59:02 发布

原创最新推荐文章于 2024-03-19 18:59:02 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #database

sql 专栏收录该内容

2 篇文章

订阅专栏

  SELECT * from (
        SELECT bwp.pattern_id as patternId ,tp.id as tpId,p.`name` as pname,sum( IF(pi.type = 1, 1, 0) ) as whiteApp
        ,rt.name as teName,
        tp.b_time as bTime,tp.e_time as eTime
        FROM ro_template_pattern as tp
        INNER JOIN ro_pattern as p on p.id=tp.pattern_id
        INNER JOIN ro_template AS rt ON tp.template_id = rt.id
        INNER JOIN ro_black_white_pattern as bwp on bwp.pattern_id =p.id
        INNER JOIN ro_public_information as pi on pi.id =bwp.public_information_id
        where 1=1
        <if test="templateId !=null and templateId !='' ">
            and tp.template_id=#{templateId}
        </if>
        <if test="weekDay !=null">
            and tp.week_day=#{weekDay}
        </if>
        <if test="customerId != '' and customerId != null">
            AND p.customer_id = #{customerId,jdbcType=VARCHAR}
        </if>
        <if test="state != '' and state !=null">
            AND rt.state = #{state,jdbcType=VARCHAR}
        </if>
        <if test="type != '' and type!=null">
            AND p.type = #{type,jdbcType=VARCHAR}
        </if>
        <if test="inventoryCoe != '' and inventoryCoe != null">
            AND rt.inventory_code = #{inventoryCoe,jdbcType=VARCHAR}
        </if>
        and tp.is_delete=0
        and p.is_delete=0
        and bwp.is_delete=0

        GROUP BY bwp.pattern_id ,tp.id,rt.name ORDER BY tp.create_time asc
        ) as  b
        LEFT JOIN (SELECT bwr.pattern_id as patternId, sum( IF(bwr.type = 2, 1, 0) ) as whiteWeb FROM ro_black_white_rule  as bwr GROUP BY bwr.pattern_id ) as c

        on b.patternId =c.patternId