先上个sql语句
select count(b.INT_VAR_DET_ID)existBuildCount,sum(b.EXIST_FAT_COUNT)EXIST_FAT_COUNT,sum(b.EXIST_MOT_COUNT)EXIST_MOT_COUNT,sum(b.MOT_DEATH_COUNT)MOT_DEATH_COUNT, sum(b.FAT_DEATH_COUNT)FAT_DEATH_COUNT,avg(b.FAT__INGESTION)FAT__INGESTION, avg(b.MOT_INGESTION)MOT_INGESTION,sum(b.ELI_EGG)ELI_EGG, sum(b.DAM_EGG)DAM_EGG,sum(b.DAM_EGG)DAM_EGG,sum(b.DEF_EGG)DEF_EGG,sum(b.DOU_EGG)DOU_EGG,avg(b.EGG_WEIGHT)EGG_WEIGHT from(select a.INT_VAR_DET_ID ,a.EXIST_MOT_COUNT,a.EXIST_FAT_COUNT,a.MOT_DEATH_COUNT,a.FAT_DEATH_COUNT, a.FAT__INGESTION,a.MOT_INGESTION, a.ELI_EGG, a.DAM_EGG,a.DEF_EGG, a.DOU_EGG,a.EGG_WEIGHT from (select v.INT_VAR_DET_ID, v.OCCUR_DATE,v.EXIST_MOT_COUNT,v.EXIST_FAT_COUNT,v.MOT_DEATH_COUNT,v.FAT_DEATH_COUNT, v.FAT__INGESTION,v.MOT_INGESTION, v.ELI_EGG, v.DAM_EGG,v.DEF_EGG, v.DOU_EGG,v.EGG_WEIGHT from varbreedlog v where v.OCCUR_DATE <= '2017-05-05' and v.INT_VAR_DET_ID in(1,2,3) ORDER BY v.OCCUR_DATE desc)a GROUP BY a.INT_VAR_DET_ID)b ;
1.mysql分组之后统计分组个数，以及求分组里面的最大值，和等等
分组查询注意两点
第一 ：GROUP BY 之后的分组字段需要查询出来 例子里面的 INT_VAR_DET_ID
第二：分完组之后的其他操作要把整个分完组后的查询做为一个子部分,例子里面的a,b
第三：mysql和mybatis的映射问题，所有的统计的表的字段要取别名，不然无法和实体映射
比如sum(b.EXIST_FAT_COUNT)如果不取别名，那么mybatis实体属性对应的映射文件不会映射
映射文件
<resultMap id="HomePageMap" type="com.znyq.wfCloud.service.pageModel.SmartVarHomePage" > <result column="id" property="varDetId" jdbcType="VARCHAR"/> <result column="BATCH" property="batch" jdbcType="VARCHAR"/> <result column="intOccurDt" property="intOccurDt" jdbcType="VARCHAR"/> <result column="FAT_AMOUNT" property="fatAmount" jdbcType="INTEGER"/> <result column="MOT_AMOUNT" property="motAmount" jdbcType="INTEGER"/> <result column="EXIST_MOT_COUNT" property="existMotCount" jdbcType="INTEGER" /> <result column="EXIST_FAT_COUNT" property="existFatCount" jdbcType="INTEGER" /> <result column="MOT_DEATH_COUNT" property="motDeathCount" jdbcType="INTEGER" /> <result column="FAT_DEATH_COUNT" property="fatDeathCount" jdbcType="INTEGER" /> <result column="dayAge" property="dayAge" jdbcType="INTEGER"/> <result column="existBuildCount" property="existBuildCount" jdbcType="INTEGER"/> <result column="existSevenMotCount" property="existSevenMotCount" jdbcType="INTEGER"/> <result column="existSevenFatCount" property="existSevenFatCount" jdbcType="INTEGER"/> <result column="ELI_EGG" property="eliEgg" jdbcType="INTEGER" /> <result column="DAM_EGG" property="damEgg" jdbcType="INTEGER" /> <result column="DEF_EGG" property="defEgg" jdbcType="INTEGER" /> <result column="DOU_EGG" property="douEgg" jdbcType="INTEGER" /> <result column="MOT_INGESTION" property="motIngestion" jdbcType="REAL" /> <result column="FAT__INGESTION" property="fatIngestion" jdbcType="REAL" /> <result column="EGG_WEIGHT" property="eggWeight" jdbcType="REAL" /> </resultMap>
映射的实体就不上代码了
