注释：查询该课程表下,所有的课程以及课程观看的人数统计
SELECT ml.`name` as lessonName,ml.category,ml.deleted,ml.teacherName, ,ml.liveId,ml.selected,mlc.channelId,p.viewNums,count(*) as totalNumfrom mlk_live_channel AS ml LEFT JOIN mlk_lesson_channel AS mlc ON mlc.lessonId = LEFT JOIN mlk_live_channel AS mcl ON = mlc.channelId LEFT JOIN (SELECT mpr.liveId,mpr.memberId,COUNT(mpr.liveId) as viewNums FROM mlk_payment_record AS mpr WHERE mpr.payType = 3 AND mpr.notify = 1 GROUP BY mpr.liveId) AS p ON p.liveId = mcl.liveIdWHERE ml.deleted = 0 AND ml.category = 1GROUP BY ORDER BY ml.create_time
$connection = $this->db; $sql = "SELECT as lessonName,ml.category,ml.deleted,ml.teacherName,ml.banner,
,ml.liveId,ml.selected,mlc.channelId,p.viewNums,count(mlc.lessonId) as totalLesson
from mlk_live_channel AS ml
LEFT JOIN mlk_lesson_channel AS mlc ON mlc.lessonId =
LEFT JOIN mlk_live_channel AS mcl ON = mlc.channelId
LEFT JOIN
(SELECT mpr.liveId,mpr.memberId,COUNT(mpr.liveId) as viewNums FROM mlk_payment_record AS mpr
WHERE mpr.payType = 3 AND mpr.notify = 1 GROUP BY mpr.liveId) AS p
ON p.liveId = mcl.liveId
WHERE ml.deleted = 0 AND ml.category = 1
GROUP BY
ORDER BY ml.create_time"; $totals = $connection->fetchAll($sql); $this->view->setVar('channles', $totals);SELECT ml.`name` as lessonName,ml.category,ml.deleted,ml.teacherName,mlc.channelId,ml.liveId,ml.banner, ,ml.selected,mlc.channelId,SUM(p.viewNums) as totalViews,COUNT(mlc.lessonId) AS totalLessonfrom mlk_live_channel AS ml LEFT JOIN mlk_lesson_channel AS mlc ON mlc.lessonId = LEFT JOIN (SELECT ,mlc.liveid,count(DISTINCT mpr.memberId) as viewNums FROM mlk_live_channel AS mlcLEFT JOIN mlk_payment_record AS mpr ON mpr.liveId = mlc.liveIdWHERE mlc.deleted = 0 and mlc.category = 2 AND mpr.payType = 4GROUP BY mlc.liveId ) AS p ON = mlc.channelIdWHERE ml.deleted = 0 AND ml.category = 1GROUP BY HAVING SUM(p.viewNums)>0
