SQL中SUM与COUNT的使用
数字上标很多时候都有用到,本人前辈教的方法是创建一个实体然后单独写一个接口,将各List集合数分别作为一个字段返回给前端。
- 创建返回数实体:
package com.make.mes.domain.pur.vo;
import com.make.common.core.domain.BaseEntity;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import java.io.Serializable;
/**
* 统计采购审核数
*/
@Data
public class MobilePurchaseTotalsVo extends BaseEntity {
/**
* 采购待审核总数
*/
@ApiModelProperty("采购待审核总数")//根据当前审核人取数
private int waitFor;
/**
* 采购审核中总数
*/
@ApiModelProperty("采购审核中总数")
private int doFor;
/**
*采购已审核总数
*/
@ApiModelProperty("采购已审核总数")
private int endFor;
/**
*采购已驳回总数
*/
@ApiModelProperty("采购已审核总数")
private int rejectFor;
}
- 编写sql(xml内):
<select id="selectPurchaseTotal" resultType="com.make.mes.domain.pur.vo.MobilePurchaseTotalsVo">
SELECT SUM(t.waitFor) waitFor,SUM(t.doFor) doFor,SUM(t.endFor) endFor,SUM(t.rejectFor) rejectFor FROM(
(SELECT COUNT(a.id) waitFor,0 doFor,0 endFor,0 rejectFor
FROM mes_purchase