CASE函数的使用
在查询数据的时候 需要查询含有某种条件的数量,用一个count可能解决不了。那么就要试用一下Sum配合case函数了。eg:SUM( CASE WHEN n.state=0 THEN 1 ELSE 0 END ) AS normalcount查询符合state=0的数据总量也可在内部查询: SUM( CASE WHEN(SELECT TYPE FRO...
·
在查询数据的时候 需要查询含有某种条件的数量,用一个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数字相加
更多推荐
已为社区贡献5条内容
所有评论(0)