首页 > 代码库 > 课时统计开发过程sql使用记录
课时统计开发过程sql使用记录
课时统计开发过程sql使用记录
/*
//消费时长统计
select sum(duration) as totalDuration, sum(count) as number, date
from statistics_duration_cu
where school_id="test" and date >= "2014-9-28" and date <= "2014-10-17"
group by date
order by date ASC
*/
/*
SELECT sd.user_id AS userId,sd.school_id AS schoolId,sd.course_id AS courseId, sd.lecture_id AS lecture_id, sum(sd.duration) AS totalDuration, sum(sd.count) AS number,sd.date AS date
from statistics_duration_cu as sd
where sd.school_id="swiftv"
GROUP BY sd.date order by sd.date DESC;
*/
/*
SELECT sd.user_id AS userId,sd.school_id AS schoolId,sd.course_id AS courseId, sd.lecture_id AS lecture_id, sum(sd.duration) AS totalDuration, sum(sd.count) AS number,sd.date AS date, cu.title AS courseTitle
from statistics_duration_cu as sd
LEFT JOIN course as cu on cu.course_id=sd.course_id
where sd.user_id="httirtd2" and sd.school_id="test"
GROUP BY sd.course_id order by totalDuration DESC;
*/
/*
//查询该网校的所有学员的学习情况 COUNT
SELECT count(*) from
(SELECT sd.user_id
from statistics_duration_cu as sd
where sd.school_id="swiftv"
GROUP BY sd.user_id
)as temp;
*/
/*
//查询该网校的所有学员的学习情况
SELECT sd.user_id AS userId,sd.school_id AS schoolId,sd.course_id AS courseId, sum(sd.duration) AS totalDuration, sum(sd.count) AS number,sd.date AS date, u.nickname AS nickname
from statistics_duration_cu as sd
LEFT JOIN user as u on u.user_id=sd.user_id
where sd.school_id="swiftv"
GROUP BY sd.user_id order by totalDuration DESC;
*/
/*
SELECT sd.user_id AS userId,sd.school_id AS schoolId,sd.course_id AS courseId,sd.lecture_id As lectureId, section_title AS sectionTitle,lecture_title As lectureTitle,sum(sd.duration) AS totalDuration, sum(sd.count) AS number,sd.date
from statistics_duration_cu as sd
where sd.course_id="hxbazkq4" and sd.school_id="swiftv" and sd.date BETWEEN '2014-9-28' AND '2014-10-13'
GROUP BY sd.date order by sd.date DESC
*/
/*
//
SELECT count(*) from
(SELECT sd.lecture_id
from statistics_duration_cu as sd
where sd.course_id="hyjgz2np" and sd.school_id="test" and user_id="httirtd2"
GROUP BY lecture_id
) as temp;
*/
/*
//学生针对一门课程的时长 (count) 用于分页
SELECT count(*) from
(SELECT sd.user_id
from statistics_duration_cu as sd
where sd.course_id="hyjgz2np" and sd.school_id="test"
GROUP BY sd.user_id
) as temp;
*/
/*
//所有课程分组后的记录数
SELECT count(*) from
(SELECT sd.course_id
from statistics_duration_cu as sd
where sd.school_id="swiftv"
GROUP BY sd.course_id
)as temp;
*/
/*
//管理后台-详情 by 时间
SELECT sd.school_id,sd.course_id AS courseId,sum(sd.duration) AS duration, sum(sd.count) AS number,sd.date AS date, cu.title AS courseTitle
from statistics_duration_cu as sd
LEFT JOIN course as cu on cu.course_id=sd.course_id
where sd.school_id="test" and sd.date BETWEEN '2014-9-28' AND '2014-10-10'
GROUP BY sd.course_id ORDER BY sd.date ASC;
*/
/*
//管理后台-详情
SELECT sd.school_id,sd.course_id AS courseId,sd.lecture_id AS lectureId, lecture_title AS lectureTitle,sum(sd.duration) AS totalDuration, sum(sd.count) AS number,sd.date AS date
from statistics_duration_cu as sd
where sd.school_id="test" and sd.course_id="hyjgz2np"
GROUP BY lectureId ORDER BY totalDuration DESC;
*/
/*
//查询该网校的所有课程的时长统计
SELECT sd.school_id,sd.course_id,sum(sd.duration) AS duration, sum(sd.count) AS number,sd.date AS date, cu.title AS courseTitle
from statistics_duration_cu as sd
LEFT JOIN course as cu on cu.course_id=sd.course_id
where sd.school_id="swiftv"
GROUP BY course_id ORDER BY sd.duration DESC;
*/
/*
//查询一段时间内的统计数据
SELECT sd.user_id AS userId,sd.school_id AS schoolId,sd.course_id AS courseId,sd.lecture_id As lectureId, section_title AS sectionTitle,lecture_title As lectureTitle,sum(sd.duration) AS totalDuration, sum(sd.count) AS number,sd.date
from statistics_duration_cu as sd
where sd.course_id="i0cpf4a8" and sd.school_id='swiftv' and sd.date BETWEEN '2014-9-1' AND '2014-10-5'
GROUP BY sd.date;
*/
/*
select DATE_FORMAT(date,'%Y-%m-%d') as day, school_id AS schoolId, course_id AS courseId, sum(duration) AS totalDuration, sum(count) AS number, date from statistics_duration_cu
where school_id='swiftv' and course_id='hwcuol4d'
and DATE_FORMAT(date,'%Y')=2014 and DATE_FORMAT(date,'%m')=9 group by day order by day;
*/
/*
SELECT sd.user_id,sd.school_id,sd.course_id,sd.lecture_id, section_title,lecture_title,sum(sd.duration) AS duration, sum(sd.count) AS number,sd.date
from statistics_duration_cu as sd
where sd.course_id="hwcuol4d" and sd.school_id="swiftv"
GROUP BY date;
*/
/*
select DATE_FORMAT(date,'%Y-%m-%d') as day, sum(duration) from statistics_duration_cu
where school_id='swiftv' and DATE_FORMAT(date,'%Y')=2014 and DATE_FORMAT(date,'%m')=10 group by day order by day;
*/
/*
SELECT sd.user_id,sd.school_id,sd.course_id,sd.lecture_id, section_title,lecture_title,sum(sd.duration) AS duration, sum(sd.count) AS number,sd.date
from statistics_duration_cu as sd
where sd.course_id="hwcuol4d" and sd.school_id="swiftv"
GROUP BY date ORDER BY duration DESC;
*/
/*
SELECT sd.user_id,sd.school_id,sd.course_id,sd.lecture_id, section_title,lecture_title,sum(sd.duration) AS duration, sum(sd.count) AS number,sd.date
from statistics_duration_cu as sd
where sd.course_id="hwcuol4d" and user_id="hz6tvah8" and sd.school_id="swiftv"
GROUP BY lecture_id ORDER BY sd.duration DESC;
*/
/*
SELECT sd.user_id,sd.school_id,sd.course_id,sd.lecture_id, section_title,lecture_title,sd.duration AS duration, sd.count AS number,sd.date
from statistics_duration_cu as sd
where sd.course_id="hwcuol4d" and user_id="hz6tvah8" and sd.school_id="swiftv"
ORDER BY sd.duration DESC;
*/
/*
SELECT sd.user_id,sd.school_id,sd.course_id,sd.lecture_id, sum(sd.duration) AS duration, sum(sd.count) AS number,sd.date,us.nickname,us.avatar
from statistics_duration_cu as sd
LEFT JOIN user as us on sd.user_id=us.user_id
where sd.course_id="hwlrt694" and sd.school_id="swiftv"
GROUP BY sd.user_id ORDER BY sd.duration DESC;
*/
/*
SELECT sd.user_id,sd.school_id,sd.course_id,sd.lecture_id, sum(sd.duration) AS duration, sum(sd.count) AS number,sd.date,us.nickname,us.avatar
from statistics_duration_cu as sd
LEFT JOIN user as us on sd.user_id=us.user_id
where sd.course_id="hwlrt694" and sd.school_id="swiftv"
GROUP BY sd.user_id;
*/
/*
select user_id,course_id,school_id,lecture_id,sum(duration),count(pid) from statistics_duration where user_id="hz1agerm" and course_id="hxbazkq4" and school_id="swiftv";
*/
/*
select * from statistics_duration where course_id="hxbazkq4" and school_id="swiftv";
*/
课时统计开发过程sql使用记录
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。