在查询数据的时候 需要查询含有某种条件的数量，用一个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数字相加