记录一些最近手写的一些易坑的SQL

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;