SQL
一个打卡表,带姓名name、带评论content、带类型type、带创建时间create_time,带团队IDteam_id。
name = models.CharField(verbose_name="姓名", max_length=32, db_index=True)
type = models.CharField(verbose_name="类型", choices=TYPE_CHOICES, max_length=128)
content = models.TextField(verbose_name="打卡内容", null=True, blank=True)
team_id = models.CharField(verbose_name="团队ID", max_length=32, null=True, blank=True, db_index=True)
amount = models.FloatField(verbose_name="数量", null=True, blank=True, db_index=True)
create_time = models.DateTimeField(verbose_name="创建时间", auto_now_add=True)
1、查询出所有信息并按时间排序(注意这里项目中指定显示的是Asia/Shanghai时间,但存储入库的时间是零时区的时间,因此这里加8小时)
SELECT
name "姓名",
type "类型",
CONVERT(FROM_BASE64(content) USING utf8mb4) as "自我评论",
date_format(DATE_ADD(create_time, INTERVAL 8 HOUR), '%Y-%m-%d') "打卡时间"
FROM punch
ORDER BY DATE_ADD(create_time, INTERVAL 8 HOUR) DESC;
2、统计每天各天的打卡天数(按天分组并按天排序)
SELECT DATE_FORMAT(DATE_ADD(create_time, INTERVAL 8 HOUR), '%Y-%m-%d') "日期", count(*) "打卡人数"
from punch
group by DATE_FORMAT(DATE_ADD(create_time, INTERVAL 8 HOUR), '%Y-%m-%d')
order by DATE_FORMAT(DATE_ADD(create_time, INTERVAL 8 HOUR), '%Y-%m-%d') DESC
3、统计某个时间内打卡天数大于等于6的人数及他的打卡天数(这里注意between是左闭右开区间的计数,这里其实计数的是[5.1-5.7]这一周的数据)
SELECT name, COUNT(name) AS num_punch_day
FROM punch
WHERE DATE_FORMAT(DATE_ADD(create_time, INTERVAL 8 HOUR), '%Y-%m-%d') BETWEEN '2023-05-01' AND '2023-05-08'
GROUP BY name
HAVING num_punch_day >= 6
ORDER BY num_punch_day DESC;
4、统计每日数目,并按不同日期不同数目分组,并按先数目降序排序再日期降序排序。
select amount, count(amount) "总数目", date_format(DATE_ADD(create_time, INTERVAL 8 HOUR),"%Y-%m-%d") "日期"
from punch
GROUP BY date_format(DATE_ADD(create_time, INTERVAL 8 HOUR),"%Y-%m-%d"), amount
ORDER BY amount DESC, date_format(DATE_ADD(create_time, INTERVAL 8 HOUR),"%Y-%m-%d") DESC
团队
team_id = models.CharField(verbose_name="团队ID", max_length=32, unique=True, db_index=True)
state = models.IntegerField(verbose_name="组队状态", default=1)
create_time = models.DateTimeField(verbose_name="创建时间", auto_now_add=True)
1、显示信息
select
team_id as `团队ID`,
if(state='2', '已确认', '待确认') as `组队状态`,
date_format(DATE_ADD(create_time, INTERVAL 8 HOUR), '%Y-%m-%d %H:%m:%s') as `创建时间`
from team
limit 10000
2、团队5人一组,一天中5天都打卡算一次团队打卡,统计本周团队打卡天数超过6天的队伍并按天数降序(假设该周是2023-05-01到2023-05-07)
SELECT team_id, COUNT(*) AS num_punch_day
FROM (
SELECT team_id, DATE(DATE_ADD(create_time, INTERVAL 8 HOUR)) AS checkin_date
FROM punch
WHERE DATE_ADD(create_time, INTERVAL 8 HOUR) BETWEEN '2023-05-01' AND '2023-05-08' and type = 'TEAM'
GROUP BY team_id, DATE(DATE_ADD(create_time, INTERVAL 8 HOUR))
HAVING COUNT(*) >= 5
) AS punch_day
GROUP BY team_id
HAVING COUNT(*) >= 6
ORDER BY num_punch_day DESC;