SQL语句记录,查询今日某个活动签到是否正常

最新推荐文章于 2023-01-06 20:05:57 发布

allWords

最新推荐文章于 2023-01-06 20:05:57 发布

阅读量876

点赞数

分类专栏： SQL 文章标签： SQL语句查询当日 sql语句查询今日

本文链接：https://blog.csdn.net/allWords/article/details/78468376

版权

SQL 专栏收录该内容

4 篇文章 1 订阅

订阅专栏

做个笔记：

SELECT

   IF (DATE(signend) != date_format(now(), '%y-%m-%d'),NULL,signend) AS signend,
   stu.NAME,
   stu.signstart
FROM(
   SELECT
       DISTINCT MAX(IF ( DATE(sign_start) != date_format(now(), '%y-%m-%d'),NULL,sign_start)) AS signstart,
       t.id,
       t.`name`,
       MAX(s.sign_end) AS signend
   FROM t_user t
       LEFT JOIN t_sign_up tu ON t.id = tu.uid
       LEFT JOIN t_activity ta ON tu.aid = ta.id
       LEFT JOIN t_sign s ON s.suid = tu.id
       LEFT JOIN t_activity_time tt ON tt.aid = ta.id
   WHERE
       DATE_SUB(CURDATE(), INTERVAL 1 DAY) < tt.`start`
       AND DATE_SUB(CURDATE(), INTERVAL - 1 DAY) > tt.`end`
       AND ta.id = 20
       GROUP BY id ) AS stu
WHERE
   TO_DAYS(NOW()) - TO_DAYS(stu.signend) = 0
   AND TO_DAYS(NOW()) - TO_DAYS(stu.signstart) = 0
   OR (
       stu.signend IS NULL
       AND stu.signstart IS NULL
       )
   OR (
       stu.signend != ''
       AND stu.signstart IS NULL
       )
   OR (
       stu.signstart != ''
       AND stu.signend IS NULL
       )
ORDER BY stu.signstart