Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- id | id_course | lesson_title
- 1 | 3 | Урок 1
- 2 | 3 | Урок 2
- 3 | 3 | Урок 3
- 4 | 4 | Урок 4
- 5 | 4 | Урок 5
- id | id_lesson | email | date
- 43 | 1 | 123@gmail.com | 2018-07-09 13:17:05
- 44 | 2 | 123@gmail.com | 2018-07-09 13:18:05
- 45 | 3 | 123@gmail.com | 2018-07-09 13:19:05
- function total_score($id){
- global $connection;
- $query = "SELECT l.id_course, h.email, MAX(h.date) FROM lesson l LEFT JOIN history h ON h.id_lesson = l.id WHERE l.id_course= '$id' GROUP BY l.id_course, h.email HAVING SUM(h.id_lesson IS NULL) = 0";
- $res = mysqli_query($connection, $query);
- $total_score = array();
- while($row = mysqli_fetch_assoc($res)){
- $total_score[$row['email']] = $row;
- }
- return $total_score;
- }
- SELECT l.id_course, h.email, MAX(h.date)
- FROM lesson l
- LEFT JOIN history h ON h.id_lesson = l.id
- GROUP BY l.id_course, h.email
- HAVING SUM(h.id_lesson IS NULL) = 0
- create table `lesson`
- (
- `id` int,
- `id_course` int,
- `lesson_title` text
- );
- create table history
- (
- `id` int,
- `id_lesson` int,
- `email` text,
- `date` date
- );
- create table history
- (
- `id` int,
- `id_lesson` int,
- `email` text,
- `date` date
- );
- Insert into `lesson` (`id`,`id_course`,`lesson_title`) values
- (1, 3, 'Урок 1'),
- (2, 3, 'Урок 2'),
- (3, 3, 'Урок 3'),
- (4, 4, 'Урок 4'),
- (5, 4, 'Урок 5')
- ;
- Insert into `lesson` (`id`,`id_course`,`lesson_title`) values
- (1, 3, 'Урок 1'),
- (2, 3, 'Урок 2'),
- (3, 3, 'Урок 3'),
- (4, 4, 'Урок 4'),
- (5, 4, 'Урок 5')
- ;
- Insert into `history` (`id`,`id_lesson`,`email`,`date`) values
- (43, 1, '100@gmail.com', now()),
- (44, 2, '100@gmail.com', now()),
- (45, 3, '100@gmail.com', now()),
- (46, 4, '110@gmail.com', now()),
- (47, 5, '110@gmail.com', now()),
- (48, 1, '200@gmail.com', now()),
- (49, 2, '200@gmail.com', now()),
- (50, 1, '300@gmail.com', now()),
- (51, 4, '300@gmail.com', now())
- ;
- Insert into `history` (`id`,`id_lesson`,`email`,`date`) values
- (43, 1, '100@gmail.com', now()),
- (44, 2, '100@gmail.com', now()),
- (45, 3, '100@gmail.com', now()),
- (46, 4, '110@gmail.com', now()),
- (47, 5, '110@gmail.com', now()),
- (48, 1, '200@gmail.com', now()),
- (49, 2, '200@gmail.com', now()),
- (50, 1, '300@gmail.com', now()),
- (51, 4, '300@gmail.com', now())
- ;
- select * from `lesson`;
- select * from `lesson`;
- select * from `history`;
- select * from `history`;
- select distinct
- h.`email`,
- l.`id_course`
- from `lesson` l
- join `history` h on l.`id` = h.`id_lesson`
- select distinct
- h.`email`,
- l.`id_course`
- from `lesson` l
- join `history` h on l.`id` = h.`id_lesson`
- select
- cr.`email`,
- cr.`id_course`,
- L0.`id` as id_lesson
- from
- (
- select distinct
- h.`email`,
- L.`id_course`
- from `lesson` L
- join `history` h on L.`id` = h.`id_lesson`
- ) as cr
- join `lesson` L0 on cr.`id_course` = L0.`id_course`
- order by
- cr.`email` asc,
- cr.`id_course` asc,
- L0.`id` asc
- select
- cr.`email`,
- cr.`id_course`,
- L0.`id` as id_lesson
- from
- (
- select distinct
- h.`email`,
- L.`id_course`
- from `lesson` L
- join `history` h on L.`id` = h.`id_lesson`
- ) as cr
- join `lesson` L0 on cr.`id_course` = L0.`id_course`
- order by
- cr.`email` asc,
- cr.`id_course` asc,
- L0.`id` asc
- -- полная таблица прохождения/непрохождения уроков
- select
- L1.`email`,
- L1.`id_course`,
- L1.`id_lesson`,
- H1.`email`
- from
- (
- select
- cr.`email`,
- cr.`id_course`,
- L0.`id` as id_lesson
- from
- (
- select distinct
- h.`email`,
- L.`id_course`
- from `lesson` L
- join `history` h on L.`id` = h.`id_lesson`
- ) as cr
- join `lesson` L0 on cr.`id_course` = L0.`id_course`
- ) L1
- left outer join `history` H1
- on
- H1.`email` = L1.`email`
- and H1.`id_lesson` = L1.`id_lesson`
- order BY
- L1.`email`,
- L1.`id_course`,
- L1.`id_lesson`
- -- полная таблица прохождения/непрохождения уроков
- select
- L1.`email`,
- L1.`id_course`,
- L1.`id_lesson`,
- H1.`email`
- from
- (
- select
- cr.`email`,
- cr.`id_course`,
- L0.`id` as id_lesson
- from
- (
- select distinct
- h.`email`,
- L.`id_course`
- from `lesson` L
- join `history` h on L.`id` = h.`id_lesson`
- ) as cr
- join `lesson` L0 on cr.`id_course` = L0.`id_course`
- ) L1
- left outer join `history` H1
- on
- H1.`email` = L1.`email`
- and H1.`id_lesson` = L1.`id_lesson`
- order BY
- L1.`email`,
- L1.`id_course`,
- L1.`id_lesson`
- -- ученик не прошел весь курс
- select distinct
- L1.`email`,
- L1.`id_course`
- from
- (
- select
- cr.`email`,
- cr.`id_course`,
- L0.`id` as id_lesson
- from
- (
- select distinct
- h.`email`,
- L.`id_course`
- from `lesson` L
- join `history` h on L.`id` = h.`id_lesson`
- ) as cr
- join `lesson` L0 on cr.`id_course` = L0.`id_course`
- ) L1
- left outer join `history` H1
- on
- H1.`email` = L1.`email`
- and H1.`id_lesson` = L1.`id_lesson`
- where
- H1.`email` is null
- -- ученик не прошел весь курс
- select distinct
- L1.`email`,
- L1.`id_course`
- from
- (
- select
- cr.`email`,
- cr.`id_course`,
- L0.`id` as id_lesson
- from
- (
- select distinct
- h.`email`,
- L.`id_course`
- from `lesson` L
- join `history` h on L.`id` = h.`id_lesson`
- ) as cr
- join `lesson` L0 on cr.`id_course` = L0.`id_course`
- ) L1
- left outer join `history` H1
- on
- H1.`email` = L1.`email`
- and H1.`id_lesson` = L1.`id_lesson`
- where
- H1.`email` is null
- -- РЕЗУЛЬТАТ: пользователи прошедшие курс
- select
- L3.`email`,
- L3.`id_course`
- from
- (
- select distinct
- h.`email`,
- l.`id_course`
- from `lesson` l
- join `history` h on l.`id` = h.`id_lesson`
- ) L3 left outer join
- (
- select distinct
- L1.`email`,
- L1.`id_course`
- from
- (
- select
- cr.`email`,
- cr.`id_course`,
- L0.`id` as id_lesson
- from
- (
- select distinct
- h.`email`,
- L.`id_course`
- from `lesson` L
- join `history` h on L.`id` = h.`id_lesson`
- ) as cr
- join `lesson` L0 on cr.`id_course` = L0.`id_course`
- ) L1
- left outer join `history` H1
- on
- H1.`email` = L1.`email`
- and H1.`id_lesson` = L1.`id_lesson`
- where
- H1.`email` is null
- ) L2 on
- L2.`email` = L3.`email`
- and L2.`id_course` = L3.`id_course`
- where
- L2.`email` is null
- -- РЕЗУЛЬТАТ: пользователи прошедшие курс
- select
- L3.`email`,
- L3.`id_course`
- from
- (
- select distinct
- h.`email`,
- l.`id_course`
- from `lesson` l
- join `history` h on l.`id` = h.`id_lesson`
- ) L3 left outer join
- (
- select distinct
- L1.`email`,
- L1.`id_course`
- from
- (
- select
- cr.`email`,
- cr.`id_course`,
- L0.`id` as id_lesson
- from
- (
- select distinct
- h.`email`,
- L.`id_course`
- from `lesson` L
- join `history` h on L.`id` = h.`id_lesson`
- ) as cr
- join `lesson` L0 on cr.`id_course` = L0.`id_course`
- ) L1
- left outer join `history` H1
- on
- H1.`email` = L1.`email`
- and H1.`id_lesson` = L1.`id_lesson`
- where
- H1.`email` is null
- ) L2 on
- L2.`email` = L3.`email`
- and L2.`id_course` = L3.`id_course`
- where
- L2.`email` is null
Add Comment
Please, Sign In to add comment