mysql带条件取count记录数
参考方法三:
统计sub_type='REFUND_FEE’ 的记录数:
方法一.select count(sub_type) from t where t.sub_type='REFUND_FEE’;
方法二.select sum(if( B.sub_type='REFUND_FEE’ ,1,0)) from t;
方法三.select count(B.sub_type=‘REFUND_FEE’ or null) from t;
解释:
当select B.sub_type=‘REFUND_FEE’ 时:
如果sub_type为REFUND_FEE则返回1,
不空且不为REFUND_FEE否则返回0,
空时返回null。
所以B.sub_type=‘REFUND_FEE’ or null 只返回sub_type=‘REFUND_FEE’ 的,其余的都返回null ,而count(列名)时是不会统计null的个数的
注:count(*)会把null的个数也统计在内
项目sql
SELECT subjectName,doctorName,count(1) AS sumNum,
count(OVERTIMES>0 or null) as overNum //只统计OVERTIMES>0的数
from ht_personstream
WHERE 1=1
<if test="subjectId!='' and subjectId!='null'">
and subjectId = #{subjectId}
</if>
<if test="startTime!='' and startTime!='null'">
<![CDATA[ and date_format(ENDTIME, '%Y-%m-%d')>= #{startTime} ]]> //mysql日期格式化
</if>
<if test="endTime!='' and endTime!='null'">
<![CDATA[ and date_format(ENDTIME, '%Y-%m-%d')<= #{endTime} ]]>
</if>
GROUP BY doctorId,subjectId
项目sql百分数保留两位小数
SELECT
subjectName as name,
concat(convert(((SUM(OVERTIMES)/(count(1)+SUM(OVERTIMES)))*100 ),decimal(10,2)),'%') as VALUE
FROM HT_PERSONSTREAM
where isDeleted = 0
GROUP BY subjectId,doctorId
当能力支撑不了野心时,就该静下心来学习!