create or REPLACE view my_subject_v as
|
SELECT
|
`s`.`COVER_PAGE_URL` AS `COVER_PAGE_URL`,
|
`p`.`PROGRESS_VALUE` AS `PROGRESS_VALUE`,
|
`stu`.`USER_ID` AS `USER_ID`,
|
`stu`.`NAME` AS `USER_NAME`,
|
`s`.`SUBJECT_ID` AS `SUBJECT_ID`,
|
`s`.`STATUS` AS `subject_status`,
|
`s`.`NAME` AS `SUBJECT_NAME`,
|
`s`.`ORIG_SUBJECT_ID` AS `ORIG_SUBJECT_ID`,
|
`cls`.`CLASS_ID` AS `CLASS_ID`,
|
`cls`.`NAME` AS `CLASS_NAME`,
|
`s`.`SCHOOL_YEAR` AS `SCHOOL_YEAR`,
|
`s`.`TERM` AS `TERM`,
|
`s`.`LECTURE_COUNT` AS `LECTURE_COUNT`,
|
`p`.`PROGRESS_PERCENT` AS `PERCENT`,
|
`s`.`TEACHER_ID` AS `TEACHER_ID`
|
FROM
|
(
|
(
|
(
|
(
|
`cls_class` `cls`
|
JOIN `cls_class_re_subject` `re` ON (
|
(
|
(
|
`cls`.`CLASS_ID` = `re`.`CLASS_ID`
|
)
|
AND (`re`.`DELETE_FLAG` = '0')
|
)
|
)
|
)
|
JOIN `stu_student` `stu` ON (
|
(
|
(
|
`cls`.`CLASS_ID` = `stu`.`CLASS_ID`
|
)
|
AND (`stu`.`STATUS` = 'ACTIVE')
|
AND (`stu`.`DELETE_FLAG` = '0')
|
)
|
)
|
)
|
JOIN `subject` `s` ON (
|
(
|
(`s`.`DELETE_FLAG` = '0')
|
AND (
|
`re`.`SUBJECT_ID` = `s`.`SUBJECT_ID`
|
)
|
)
|
)
|
)
|
LEFT JOIN `progress` `p` ON (
|
(
|
(
|
`s`.`SUBJECT_ID` = `p`.`TARGET_ID`
|
)
|
AND (
|
`stu`.`USER_ID` = `p`.`LEARNER_ID`
|
)
|
AND (`p`.`TYPE` = 'subject')
|
AND (`s`.`DELETE_FLAG` = 0)
|
)
|
)
|
)
|
WHERE
|
(`cls`.`DELETE_FLAG` = '0')
|
ORDER BY
|
`s`.`CREATE_TIME` DESC
|
