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

最新推荐文章于 2022-08-09 10:52:06 发布

原创最新推荐文章于 2022-08-09 10:52:06 发布 · 994 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#SQL语句查询当日 #sql语句查询今日

SQL 专栏收录该内容

4 篇文章

订阅专栏

本文介绍了一个复杂的SQL查询案例，该查询用于筛选特定日期签到的用户，并从多个表中关联获取用户信息。通过使用多种SQL高级特性，如子查询、连接、条件表达式等，实现对用户签到数据的有效检索。

做个笔记：

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