课时统计开发过程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";
*/