mysql 观看记录查询

万能的小帅

已于 2023-05-08 17:26:56 修改

阅读量86

点赞数

文章标签： mysql 数据库 sql

于 2023-03-07 15:06:58 首次发布

本文链接：https://blog.csdn.net/lskapdkpw/article/details/129382835

版权

文章提供了三个SQL查询，分别用于获取用户的今日观看记录，本周的学习记录，以及用户最近的连续登录天数。这些查询涉及到表连接，日期函数如DATE_FORMAT,TO_DAYS,YEARWEEK，以及变量在查询中的使用来计算连续天数。

摘要由CSDN通过智能技术生成

获取今日观看记录

  SELECT
            date_format(r.create_time, '%Y-%m-%d %H:%i:%s') as createTime,
            r.user_id as userId,
            r.course_id as courseId,
            r.course_name as courseName,
            r.course_directory_id as courseDirectoryId,
            r.course_directory_name as courseDirectoryName,
            r.end_time as endTime,
            d.duration
        FROM
            mdc_learning_records r
                LEFT JOIN mdc_course_directory d ON d.id = r.course_directory_id
        WHERE
                r.create_time IN (
                SELECT
                    MAX( t.create_time )
                FROM
                    mdc_learning_records t
                WHERE
                    t.user_id = #{userId}
                  and TO_DAYS(r.create_time) =TO_DAYS(NOW())
                GROUP BY
                    t.course_directory_id
            )
          AND r.user_id = #{userId}

这周观看记录

 SELECT
            date_format(r.create_time, '%Y-%m-%d %H:%i:%s') as createTime,
            r.user_id as userId,
            r.course_id as courseId,
            r.course_name as courseName,
            r.course_directory_id as courseDirectoryId,
            r.course_directory_name as courseDirectoryName,
            r.end_time as endTime,
            d.duration
        FROM
            mdc_learning_records r
                LEFT JOIN mdc_course_directory d ON d.id = r.course_directory_id
        WHERE
                r.create_time IN (
                SELECT
                    MAX( t.create_time )
                FROM
                    mdc_learning_records t
                WHERE
                    t.user_id = #{userId}
                  and YEARWEEK(DATE_FORMAT(create_time,'%Y-%m-%d'))=YEARWEEK(NOW())
                GROUP BY
                    t.course_directory_id
            )
          AND r.user_id = #{userId}

获取最近连续天数

SELECT
	uid,
	max(days) continuousDays,
	min(login_day) startDate,
	max(login_day) endDate 
	
FROM
	(
	SELECT
		uid,
		@cont_day := (
		CASE
				
				WHEN ( @last_uid = uid AND DATEDIFF( login_dt, @last_dt )= 1 ) THEN
				( @cont_day + 1 ) 
				WHEN ( @last_uid = uid AND DATEDIFF( login_dt, @last_dt ) < 1 ) THEN
				( @cont_day + 0 ) ELSE 1 
			END 
			) AS days,
			(
				@cont_ix := (
					@cont_ix +
				IF
				( @cont_day = 1, 1, 0 ))) AS cont_ix,
			@last_uid := uid,
			@last_dt := login_dt login_day 
		FROM
			( SELECT user_id AS uid, create_time AS login_dt FROM mdc_learning_records WHERE user_id = 'saa' ORDER BY create_time ) AS t,
			( SELECT @last_uid := '', @last_dt := '', @cont_ix := 0, @cont_day := 0 ) AS t1 
		) AS t2 
	GROUP BY
		uid,
		cont_ix