由于公司业务需要,需要查询出不同状态下的设备数量:用常规方法,则有多少种状态则需要写多少个SQL。现在用聚合函数,实现一条SQL查询出表中不同状态的设备数量。
<!--查询当前设备类型下设备统计--> <select id="getDeviceCountByType" parameterType="com.coship.common.dto.ds.DeviceQuantityParam" resultMap="deviceTypeResultMap"> SELECT t2.DEVICE_TYPE AS Device_Type, t2.DEVICE_TYPE_NAME AS Device_Type_Name,
IFNULL(SUM(CASE WHEN t1.STATUS='1' THEN 1 ELSE 0 END ),0) AS Alarm, IFNULL(SUM(CASE WHEN t1.STATUS='2' THEN 1 ELSE 0 END ),0) AS Normal, IFNULL(SUM(CASE WHEN t1.STATUS='3' THEN 1 ELSE 0 END ),0) AS Failure, IFNULL(SUM(CASE WHEN t1.STATUS IN ('1','2','3') THEN 1 ELSE 0 END ),0) AS Total
FROM t_device t1,t_device_type t2 WHERE t1.DEVICE_TYPE_ID = t2.ID <if test="spCode != null and spCode != ''"> AND t1.SUPPLIER_ID = #{spCode} </if> <if test="deviceType != null and deviceType != ''"> and t2.DEVICE_TYPE = #{deviceType} </if> group by t2.DEVICE_TYPE </select>
IFNULL()的用法
<select id="selectCartProductCount" resultType="int" parameterType="int"> select IFNULL(sum(quantity),0) as count from immall_cart where user_id = #{userId} </select>
如果 sum 为 null,是不可以传给基本类型 int 的;如果更改返回值类型 int 为 Integer 则又会在 service层报错。怎么办呢?
我们当时是不希望发生报错的啊,利用 IFNULL()函数,赋予一个默认值0,当 sum(quantity) 为 null 时,返回默认值。这样就解决了。