sql关联查询根据表中状态进行统计

背景：

今天写接口时遇到关联查询并统计的需求，稍稍的被难为了一下。由于目前公司中大多数项目对于sql的要求并不是很高，基本上都是单表查询就可以完成了，很少涉及到多表联查。有时候是关联表查的也会分开进行单表查，所以写sql的技能有些被弱化了 。

概述：

查询涉及两个表，一个表是厂商表、一个表示项目表,表结构如下。

厂商表：

项目表：

 需求：我的需求是统计厂商各个产品的检测状态的数量，以及总数量。请看原型截图：

 

实现：下面是实现数据查询的sql语句

SELECT factory_name AS "厂商名称"  ,

COUNT(1) AS "送检产品总数量",
COUNT(CASE WHEN is_pass='0' THEN is_pass  END ) AS "测试中的产品",
COUNT(CASE WHEN is_pass='2' THEN is_pass END ) AS "测试通过的产品",
COUNT(CASE WHEN is_pass='1' THEN is_pass END ) AS "测试未通过的产品"

FROM (SELECT factory_name ,is_pass FROM tb_item i RIGHT JOIN tb_factory f ON i.factory_id=f.id)

a GROUP BY factory_name
 

 结果：

项目中应用：

mapper.xml中如下：

  <select id="selectFactoryVoList"  parameterType="FactoryVo" resultMap="FactoryVoResult">
         SELECT factory_name AS "factoryName"  ,
        COUNT(1) AS "productTotal",
        COUNT(CASE WHEN is_pass='0' THEN is_pass  END ) AS "testCount",
        COUNT(CASE WHEN is_pass='2' THEN is_pass END ) AS "passCount",
        COUNT(CASE WHEN is_pass='1' THEN is_pass END ) AS "noPassCount"
    FROM (SELECT f.factory_name ,i.is_pass ,f.create_time FROM tb_item i RIGHT JOIN tb_factory f ON i.factory_id=f.id
        <where>
            <if test="beginTime != null   and endTime != null "> and f.create_time between #{beginTime} and #{endTime} </if>

        </where>


    )
a      GROUP BY factory_name

    </select>

接口截图：