create or REPLACE view my_lecture_v as
|
SELECT
|
`p`.`PROGRESS_PERCENT` AS `PERCENT`,
|
`p`.`PROGRESS_VALUE` AS `PROGRESS_VALUE`,
|
`s`.`SUBJECT_ID` AS `SUBJECT_ID`,
|
`s`.`NAME` AS `SUBJECT_NAME`,
|
`l`.`SUBJECT_CHAPTER_ID` AS `CHAPTER_ID`,
|
`l`.`NAME` AS `LECTURE_NAME`,
|
`l`.`LECTURE_ID` AS `LECTURE_ID`,
|
`stu`.`NAME` AS `USER_NAME`,
|
`stu`.`USER_ID` AS `USER_ID`,
|
`l`.`LECTURE_TYPE` AS `LECTURE_TYPE`,
|
`l`.`UPDATE_TIME` AS `LECTURE_UPDATE_TIME`,
|
`l`.`STATUS`,
|
`stu`.`CLASS_ID` AS `CLASS_ID`,
|
`l`.`CREATE_TIME` AS `LECTURE_CREATE_TIME`
|
FROM
|
(
|
(
|
(
|
`subject` `s`
|
JOIN (
|
`cls_class_re_subject` `c`
|
JOIN `stu_student` `stu` ON (
|
(
|
(`stu`.`STATUS` = 'ACTIVE')
|
AND (`stu`.`DELETE_FLAG` = '0')
|
AND (
|
`c`.`CLASS_ID` = `stu`.`CLASS_ID`
|
)
|
)
|
)
|
)
|
)
|
LEFT JOIN `cls_subject_lecture` `l` ON (
|
(
|
(
|
`s`.`SUBJECT_ID` = `l`.`SUBJECT_ID`
|
)
|
AND (
|
`l`.`CLASS_ID` = `stu`.`CLASS_ID`
|
)
|
AND (`l`.`DELETE_FLAG` = 0)
|
)
|
)
|
)
|
LEFT JOIN `progress` `p` ON (
|
(
|
(
|
`l`.`LECTURE_ID` = `p`.`TARGET_ID`
|
)
|
AND (
|
`p`.`LEARNER_ID` = `stu`.`USER_ID`
|
)
|
AND (`p`.`DELETE_FLAG` = 0)
|
)
|
)
|
)
|
WHERE
|
(
|
(`s`.`DELETE_FLAG` = 0)
|
AND (
|
`c`.`SUBJECT_ID` = `s`.`SUBJECT_ID`
|
)
|
AND (`c`.`DELETE_FLAG` IS FALSE)
|
AND (`l`.`STATUS` = 'draft')
|
)
|
