按月统计在职人员数量

原创

于 2021-10-21 10:37:48 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java #动态统计

博客探讨了如何从数据库中按月统计在职人员数量的问题。文章指出，使用SQL实现时可能涉及复杂的JOIN子查询，而推荐使用SPL来简化操作，能够更方便地完成3到7月份在职人员的统计。

【问题】

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)

运行结果：