sql统计某个字段的某个值出现的次数

直接上案例

需求：PayLog缴费表有个缴费方式的字段Method（1-营业厅、2-手机APP、3-微信小程序、4-微信公众号、5-支付宝），需要统计这缴费方式分别有几次

1、表：

2、mysql写法：

SELECT
	SUM(Method = 1) AS Business_Hall,
	SUM(Method = 2) AS Business_Hall,
	SUM(Method = 3) AS WeChat_Applet,
	SUM(Method = 4) AS WeChat_Official_Account,
	SUM(Method = 5) AS Alipay
FROM
	bus_paylog bp
WHERE
	1 = 1
AND bp.CustomerID = 1

3、运行结果：

4、mybatis的xml文件（可以不看，我自己用的）

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.ws.wssp.charging.mapper.PayMethodMapper">
	<resultMap id="BaseResultMap" type="com.ws.wssp.charging.model.PayMethod">
		<result column="CustomerID" property="customerId" jdbcType="INTEGER" />
		<result column="Method" property="method" jdbcType="INTEGER" />
		<result column="Intime" property="intime" jdbcType="TIMESTAMP" />
		<result column="OrganizationId" property="organizationId" jdbcType="INTEGER" />
			<result property="BusinessHall" column="BusinessHall" jdbcType="DECIMAL" />
			<result property="MobileAPP" column="MobileAPP" jdbcType="DECIMAL" />
			<result property="WeChatApplet" column="WeChatApplet" jdbcType="DECIMAL" />
			<result property="WeChatOfficialAccount" column="WeChatOfficialAccount" jdbcType="DECIMAL" />
			<result property="Alipay" column="Alipay" jdbcType="DECIMAL" />
	</resultMap>
	
		<select id="getMonthPayMethod" resultMap="BaseResultMap">
		select SUM(Method = 1)as BusinessHall,
		SUM(Method=2) as MobileAPP,
		SUM(Method=3) as WeChatApplet,
		SUM(Method=4) as WeChatOfficialAccount,
		SUM(Method=5) as Alipay 
		from view_paymethod vp where 1 = 1
		<if test="customerId != null">
			and vp.CustomerID=#{customerId,jdbcType=DECIMAL}
		</if>
		<if test="month != null and month  != '' ">
		and date_format(vp.Intime,'%Y-%m')= #{month,jdbcType=VARCHAR}
	    </if>
	    <if test="organizations != null and organizations != ''">
            and FIND_IN_SET(vp.OrganizationId, #{organizations,jdbcType=VARCHAR})
        </if>
	</select>
</mapper>

5、对应的实体类

package com.ws.wssp.charging.model;

import java.util.Date;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.format.annotation.DateTimeFormat;

import com.fasterxml.jackson.annotation.JsonFormat;

/**
 * @author Eric
 * @params 1-营业厅、2-手机APP、3-微信小程序、4-微信公众号、5-支付宝
 */
public class PayMethod {

	private Integer customerId;

	private Integer method;

	private Integer organizationId;

	@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") // 前端时间字符串转java时间戳
	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8") // 后台时间戳转前端时间字符串(json对象)
	private Date intime;

	/**
	* 补充@Value()的用法：
	*	1、设置从配置文件读取distributeUrl的值：
	*	@Value("${distributeUrl}")
	*	private String distributeUrl;
	*	
	*	2、设置int型默认值为0,如下
	*	
	*   3、设置String类型默认值：
	* 	@Value("${myconfig:abcd}")	
	*	private String myconfig;
	*/
	@Value("${BusinessHall:0}")
	private int BusinessHall;

	@Value("${MobileAPP:0}")
	private int MobileAPP;

	@Value("${WeChatApplet:0}")
	private int WeChatApplet;

	@Value("${WeChatOfficialAccount:0}")
	private int WeChatOfficialAccount;

	@Value("${Alipay:0}")
	private int Alipay;

	public Integer getCustomerId() {
		return customerId;
	}

	public void setCustomerId(Integer customerId) {
		this.customerId = customerId;
	}

	public Integer getMethod() {
		return method;
	}

	public void setMethod(Integer method) {
		this.method = method;
	}

	public Integer getOrganizationId() {
		return organizationId;
	}

	public void setOrganizationId(Integer organizationId) {
		this.organizationId = organizationId;
	}

	public Date getIntime() {
		return intime;
	}

	public void setIntime(Date intime) {
		this.intime = intime;
	}

	public int getBusinessHall() {
		return BusinessHall;
	}

	public void setBusinessHall(int businessHall) {
		BusinessHall = businessHall;
	}

	public int getMobileAPP() {
		return MobileAPP;
	}

	public void setMobileAPP(int mobileAPP) {
		MobileAPP = mobileAPP;
	}

	public int getWeChatApplet() {
		return WeChatApplet;
	}

	public void setWeChatApplet(int weChatApplet) {
		WeChatApplet = weChatApplet;
	}

	public int getWeChatOfficialAccount() {
		return WeChatOfficialAccount;
	}

	public void setWeChatOfficialAccount(int weChatOfficialAccount) {
		WeChatOfficialAccount = weChatOfficialAccount;
	}

	public int getAlipay() {
		return Alipay;
	}

	public void setAlipay(int alipay) {
		Alipay = alipay;
	}
}