CASE函数的使用

末蓝、 2021-10-09 10:10 370阅读 0赞

在查询数据的时候 需要查询含有某种条件的数量，用一个count可能解决不了。那么就要试用一下Sum配合case函数了。

eg：SUM( CASE  WHEN   n.state=0   THEN 1    ELSE 0   END  ) AS normalcount

查询符合state=0的数据总量

也可在内部查询： SUM(   CASE  WHEN

(SELECT   TYPE FROM  t\_dev\_alarm\_state  WHERE id = t1.\`alarmstateid\`) = 2

THEN 1      ELSE 0    END     ) AS failurecount,

case的基本语法：

case when dev.jg_loadbear = 1 then '普通型'
    			     when dev.jg_loadbear = 2 then '重型'
    			     when dev.jg_loadbear = 3 then '超重型'
    			     ELSE '未分配类型'
    end as jg_loadbear ,

sum-case连接：

SELECT *  ,r.realname as mname,r.telephone as mtelephone ,COUNT(p.deviceid) AS Dnum, 
    	 SUM( CASE  WHEN   n.state=0   THEN 1    ELSE 0   END  ) AS normalcount,
         SUM( CASE  WHEN   n.state=1   THEN 1    ELSE 0   END  ) AS alarmcount,
         SUM( CASE  WHEN   n.state=2   THEN 1    ELSE 0   END  ) AS failurecount,
         SUM( CASE  WHEN   n.state=3   THEN 1    ELSE 0   END  ) AS othercount
    FROM    t_device_group  g
         LEFT JOIN  t_user_dgroup_relation  u ON  u.devicegroupid=g.id
         LEFT JOIN  t_user  r ON  g.managerid=r.id  
         LEFT JOIN  t_device_group_relation  p ON   p.groupid=u.devicegroupid and p.moduleid=#{moduleid}
         LEFT JOIN   
         		<if test="moduleid == 1">t_device_dc</if>
    			<if test="moduleid == 2">t_device_yg</if>
    			<if test="moduleid == 3">t_device_jg</if>
    			<if test="moduleid == 8">t_device_hw</if>
    			<if test="moduleid == 9">t_device_ljt</if>
    			<if test="moduleid == 10">t_device_sj</if>
    			<if test="moduleid == 11">t_device_mc</if>
    			<if test="moduleid == 12">t_device_krq</if>
    			<if test="moduleid == 13">t_device_ywj</if>  
    			 n  ON   n.id=p.deviceid 
         WHERE  u.userid=#{userid} AND g.moduleid=#{moduleid}  and  g.isdelete = 0 and g.isenable = 1 and n.isdelete=0  
         
            <if test="code != null and code != ''">
    		AND (g.groupcode LIKE CONCAT('%', #{code}, '%')
    		or
    		g.groupname LIKE CONCAT('%', #{code}, '%'))
    		</if>
                  GROUP BY g.id

count-case查询

值得品味的SQL:when的多条件实现  其中又有group by的多条件

SELECT dg.groupname, dg.id AS groupid, dg.x AS lng, dg.y AS lat,
    COUNT(dev.id) as devcount,
    COUNT(CASE WHEN dev.state = 0   THEN dev.id END ) as  devcount0,
    COUNT(CASE WHEN dev.state = 1 AND tda.deviceid is not null THEN dev.id END ) as  devcount1,
    COUNT(CASE WHEN dev.state = 2 AND tda.deviceid is not null THEN dev.id END ) as   devcount2	
    from t_device_group dg 
    LEFT JOIN t_device_group_relation dgr ON dgr.groupid = dg.id AND dgr.isdelete!=1 and dgr.isenable=1
    left JOIN t_user tu ON tu.id=dg.managerid and tu.isenable = 1	
    LEFT JOIN t_device_all  dev on dev.id = dgr.deviceid AND dev.isdelete!=1 AND dev.isenable=1
     LEFT JOIN  t_user_dgroup_relation ugr ON  ugr.devicegroupid=dg.id 
    LEFT JOIN( select  tda.deviceid,tda.moduleid from t_dev_alarm tda  WHERE
     tda.handlestate!=2 GROUP BY tda.moduleid,tda.deviceid ) tda ON tda.deviceid=dev.id AND tda.moduleid=2
    WHERE dg.isdelete!=1 AND dg.moduleid=2  AND dev.moduleid=2  AND ugr.userid=1  AND dgr.moduleid=2
    		GROUP BY dg.id
    		ORDER BY dg.addtime desc

区别：count统计行       sum数字相加