背景
查询广州每个景点的总流量，和每个景点每日流量
数据表 t_广州
| 唯一标识id | 地点place | 流量counts | 日期date |
|---|---|---|---|
| 1 | 动物园 | 100 | '2018-11-11' |
| 2 | 动物园 | 200 | '2018-11-12' |
| 3 | 植物园 | 100 | '2018-11-11' |
需求
统计每个地点的总流量，和每天的流量
结果示意结构如下：
{
place: '动物园',
allCounts: 300,
perDay : [
{
date: '2018-11-11',
counts: 100
},
{
date: '2018-11-12',
counts: 200
}
]
},
{
place: '植物园',
allCounts: 100,
perDay : [
{
date: '2018-11-11',
counts: 100
}
]
},
xml文件如下：
<select id="pageResultMapSelect" parameterType="java.util.Map" resultMap="pageResultMap">
select date,counts from t_广州 where place=#{place} group by date
</select>
<resultMap id="pageResultMap" resultType="java.util.Map">
<result column="place" property="place" />
<result column="allCounts" property="allCounts" />
<collection property="perDay" javaType="java.util.List" ofType="java.util.Map" column="(place=place)" select="pageResultMapSelect"></collection>
</resultMap>
<select id="page" parameterType="java.util.Map" resultMap="pageResultMap">
select place,sum(counts) as allCounts from t_广州 group by place
</select>
