Songbirds


春

三条sql语句记录

  • ~2.10K 字
  1. 1. 一、统计每个月的总会员数量
  2. 2. 二、查询男女性别数量
  3. 3. 三、查询会员年龄段人数

几条sql语句，觉得算是很常用的，遂记录下来

所用到的表的示例大致如下(数据纯属虚构)

一、统计每个月的总会员数量

这个需要在controller与业务层进行一定的逻辑，sql层只需要查询每个月的x年x月31日之前的数据即可。具体实现逻辑如下，假设查询的是近一年的每个月的会员数量：

  1. 在controller层通过日历对象遍历出近一年的数据放入集合中，格式为yyyy-MM;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
// 查询月份集合
List<String> months = new ArrayList<String>();

// 获取两个日历对象，存储开始、结束日期
Calendar startTime = Calendar.getInstance();
Calendar endTime = Calendar.getInstance();

// 设置日历的开始结束时间
startTime.setTime(sdf.parse(startTimeStr));
endTime.setTime(sdf.parse(endTimeStr));

// 遍历添加年月
while (startTime.before(endTime)) {
    months.add(sdf.format(startTime.getTime()));
    startTime.add(Calendar.MONTH, 1);
}
// 上面的循环不会加入最后一个月，手动加入
months.add(sdf.format(endTime.getTime()));
  1. 在service层遍历，并手动拼接-31日，查询该年月日之前的会员总数，在存入集合
1
2
3
4
5
6
7
8
9
// 集合放查询到的结果
List<Integer> list = new ArrayList<Integer>();
if(null != months){
    // 2020-02
    for (String month : months) {
        month+="-31";
        list.add(memberDao.findMemberCountBeforeDate(month));
    }
}
  1. Mysql查询某个日期之前的总人数：
1
select count(id) from t_member where regTime &lt;= #{value}     ( &lt;  "<"的转义)

二、查询男女性别数量

  1. 这个只需要在sql使用即可
1
SELECT case sex when '1' THEN '男' when '2' THEN '女' else '未知' end as name, COUNT(id) as 'value' FROM t_member GROUP BY sex;
  1. 查询结果

三、查询会员年龄段人数

展示0-18岁，18-30岁，30-45岁，45岁以上的人数和占比。

  1. 由于数据库的日期是date类型的，所以between是包括右边界的，18要归纳到18-30岁，所以这里0-18岁只能between 0 and 17；后面的同理。
    • 注：如果是datetime类型，是不过包括右边界的，所以可以用0 between 18；
1
2
3
4
5
6
7
8
9
10
select (case
	when a.age between 0 and 18 then "0-18岁"
	when a.age between 18 and 30 then "18-30岁"
	when a.age between 31 and 45 then "30-45岁"
	when a.age >= 45 then "45岁以上"
	end) as name,		-- 设置各个年龄段
	count(*) as value,	 -- 设置各个年龄段的人数 
	count(*)/(select count(id) from t_member) as proportion 	-- 设置各个年龄段的占比
from (select timestampdiff(year, birthday,date_format(now(), '%y-%m-%d')) as age from t_member) as a 
group by name;		-- 查询年龄
  1. 查询结果

本作品采用 知识共享署名-相同方式共享 4.0 国际许可协议 进行许可