按月统计在职人员数量
【问题】
I have a table
PEOPLE, DATE, DELETED
Amanda,2015-03-01,Null
Ray,2015-03-01,Null
Moe,2015-04-01,Null
Yan,2015-05-01,Null
Bee,2015-05-05,2015-06-12
now I need to group it and sum it with months like this:
March:2 people
April:3
May:5
June:5
July:4
so new people should not be counted in previous month but they should be in next months for my range (January - June). And if man is DELETED, he should be counted together with another people last time in month when he has been deleted.
How to write query for this?
【回答】
要补齐空缺的月份，用 SQL 需要 JOIN 子查询才能实现，代码比较复杂，这种情况下建议用 SPL 实现：
| A | |
|---|---|
| 1 | =connect(“demo”) |
| 2 | =A1.query(“SELECT * FROM tb1”) |
| 3 | =to(3,7).new(~:month,A2.count(month(DATE)<=month)-A2.count(DELETED && month(DELETED)<month):count) |
运行结果：
A1：连接数据库
A2: 查找数据
A3：统计 3 到 7 月份在职人员数量
