Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / MySQL Новый топик    Ответить
 Помогите правильно сформировать запрос!  [new]
YuraVakulenko
Member

Откуда:
Сообщений: 2
Здравствуйте, друзья! Может ли мне кто-то помочь сформировать запрос, чтобы из БД в рамках одного запроса вытащить информацию о упражнениях, что принадлежат уроку и в то же выремя вывести информацию о результате выполнения пользователем этого упражнения.
17 дек 19, 14:23    [22042176]     Ответить | Цитировать Сообщить модератору
 Re: Помогите правильно сформировать запрос!  [new]
YuraVakulenko
Member

Откуда:
Сообщений: 2
не дописал пред сообщение.. вот весь текст

Здравствуйте, друзья! Может ли мне кто-то помочь сформировать запрос, чтобы из БД в рамках одного запроса вытащить информацию о упражнениях, что принадлежат уроку и в то же выремя вывести информацию о результате выполнения пользователем этого упражнения.

вот запрос, с которым мне нужна помощь. Обратите внимание, что в WHERE course_id, lesson_id, user_id ecl.course_id = 3 AND ecl.lesson_id = 33 AND ler.user_id = 1 являются внешними данными и приходят извне.
+
SELECT DISTINCT ecl.exercise_id          as exerciseId,
       ecl.ordering             as exerciseOrderNumber,
       ex.description_1         as description1,
       ex.description_2         as description2,
       ex.audio_files           as audioFiles,
       ex.max_score             as maxScore,
       ex.is_verbal             as isVerbal,
       ex.is_speaking           as isSpeaking,
       ex.cloud_icon            as cloudIcon,
       ex.is_writing            as isWriting,
       ex.add_editor_text_field as editorTextField,
       ler.is_finished as ler_isFinisedm,
       ler.no_mistakes as ler_noMistakes,
       ler.count_mistakes as ler_countMistakes,
       ler.score as ler_score,
       ler.max_score as ler_maxScore,
       ler.user_unswers as ler_userAnswers,
       ler.editor_text_field as ler_editorTextField,
       ler.teacher_corrections as ler_teacherCorrections,
       ler.finished_date as ler_finisgedDate,
       ler.remaining_attempts as ler_remainingAttempts
FROM `exercises_to_course_lessons` as ecl
         LEFT JOIN `exercises` as ex
                   ON ex.id = ecl.exercise_id
         LEFT JOIN `courses` as c
                   ON c.id = ecl.course_id
         LEFT JOIN `lessons` as l
                   ON l.id = ecl.lesson_id
        LEFT JOIN `lesson_exercises_results` as ler
            ON ler.course_id = ecl.course_id AND ler.lesson_id = ecl.lesson_id AND ler.exercise_id = ecl.exercise_id

WHERE ecl.course_id = 3
  AND ecl.lesson_id = 33
  AND ler.user_id = 1
  AND l.publish = 1
  AND c.publish = 1
  AND ex.published = 1
ORDER BY ecl.ordering;


Результат выполнения этого запроса: Все данные, что содержатся в колонках, название которых начинается на ler_ это необходимые мне данные извлеченные из lesson_exercises_results таблицы. В данном случае результат выполнения запроса не верен, потому-что в lesson_exercises_results нет данных об id_exercises, что принадлежат след списку 176, 178 и я бы хотел видеть эти строки в результате, что привел ниже, но в колонках название которых начинается на ler_ должно содержатся NULL . Как мне этого добиться?
+
"exerciseId""exerciseOrderNumber""description1""description2""audioFiles""maxScore""isVerbal""isSpeaking""cloudIcon""isWriting""editorTextField""ler_isFinisedm""ler_noMistakes""ler_countMistakes""ler_score""ler_maxScore""ler_userAnswers""ler_editorTextField""ler_teacherCorrections""ler_finisgedDate""ler_remainingAttempts"
1741Die Schule fängt wieder an.Der erste Elternabend.0000011077"{""all_valid_unswers"":{""174"":true}""right_unswers"":{""174"":{""1"":""right""""2"":""right""""3"":""right""""4"":""right""""5"":""right""""6"":""right""""7"":""right""}}""right_unswers_values"":{""174"":{""1"":""1""""2"":""2""""3"":""1""""4"":""2""""5"":""1""""6"":""2""""7"":""2""}}""wrong_unswers"":[]""wrong_unswers_values"":[]""count_right_unswers"":{""174"":7}}"2018-12-14 20:50:0114
1741Die Schule fängt wieder an.Der erste Elternabend.0000011077"{""all_valid_unswers"":{""174"":true}""right_unswers"":{""174"":{""1"":""right""""2"":""right""""3"":""right""""4"":""right""""5"":""right""""6"":""right""""7"":""right""}}""right_unswers_values"":{""174"":{""1"":""1""""2"":""2""""3"":""1""""4"":""2""""5"":""1""""6"":""2""""7"":""2""}}""wrong_unswers"":[]""wrong_unswers_values"":[]""count_right_unswers"":{""174"":7}}"2018-12-14 20:58:3714
1752Über Schule reden.Ergänzen Sie die Sätze. Dann kontrollieren Sie mit der CD.'25'0000011077"{""all_valid_unswers"":{""175"":true}""right_unswers"":{""175"":{""1"":""right""""2"":""right""""3"":""right""""4"":""right""""5"":""right""""6"":""right""""7"":""right""}}""right_unswers_values"":{""175"":{""1"":""uns""""2"":""ihren""""3"":""deiner""""4"":""dir""""5"":""eurer""""6"":""seiner""""7"":""Unserer""}}""wrong_unswers"":[]""wrong_unswers_values"":[]""count_right_unswers"":{""175"":7}}"2018-12-14 23:03:5911
1774Das Kursprogramm. Lesen Sie es laut.''0000011077"{""all_valid_unswers"":{""177"":true}""right_unswers"":{""177"":{""1"":""right""""2"":""right""""3"":""right""""4"":""right""""5"":""right""""6"":""right""""7"":""right""}}""right_unswers_values"":{""177"":{""1"":""2""""2"":""2""""3"":""3""""4"":""2""""5"":""1""""6"":""2""""7"":""2""}}""wrong_unswers"":[]""wrong_unswers_values"":[]""count_right_unswers"":{""177"":7}}"2018-12-15 07:27:1814
1774Das Kursprogramm. Lesen Sie es laut.''0000011077"{""all_valid_unswers"":{""177"":true}""right_unswers"":{""177"":{""1"":""right""""2"":""right""""3"":""right""""4"":""right""""5"":""right""""6"":""right""""7"":""right""}}""right_unswers_values"":{""177"":{""1"":""2""""2"":""2""""3"":""3""""4"":""2""""5"":""1""""6"":""2""""7"":""2""}}""wrong_unswers"":[]""wrong_unswers_values"":[]""count_right_unswers"":{""177"":7}}"2018-12-15 07:27:4414
1774Das Kursprogramm. Lesen Sie es laut.''0000011077"{""all_valid_unswers"":{""177"":true}""right_unswers"":{""177"":{""1"":""right""""2"":""right""""3"":""right""""4"":""right""""5"":""right""""6"":""right""""7"":""right""}}""right_unswers_values"":{""177"":{""1"":""2""""2"":""2""""3"":""3""""4"":""2""""5"":""1""""6"":""2""""7"":""2""}}""wrong_unswers"":[]""wrong_unswers_values"":[]""count_right_unswers"":{""177"":7}}"2018-12-15 07:33:3514
1774Das Kursprogramm. Lesen Sie es laut.''0000011077"{""all_valid_unswers"":{""177"":true}""right_unswers"":{""177"":{""1"":""right""""2"":""right""""3"":""right""""4"":""right""""5"":""right""""6"":""right""""7"":""right""}}""right_unswers_values"":{""177"":{""1"":""2""""2"":""2""""3"":""3""""4"":""2""""5"":""1""""6"":""2""""7"":""2""}}""wrong_unswers"":[]""wrong_unswers_values"":[]""count_right_unswers"":{""177"":7}}"2018-12-15 07:44:0714
1774Das Kursprogramm. Lesen Sie es laut.''0000011077"{""all_valid_unswers"":{""177"":true}""right_unswers"":{""177"":{""1"":""right""""2"":""right""""3"":""right""""4"":""right""""5"":""right""""6"":""right""""7"":""right""}}""right_unswers_values"":{""177"":{""1"":""2""""2"":""2""""3"":""3""""4"":""2""""5"":""1""""6"":""2""""7"":""2""}}""wrong_unswers"":[]""wrong_unswers_values"":[]""count_right_unswers"":{""177"":7}}"2018-12-15 07:50:4814
1774Das Kursprogramm. Lesen Sie es laut.''0000011077"{""all_valid_unswers"":{""177"":true}""right_unswers"":{""177"":{""1"":""right""""2"":""right""""3"":""right""""4"":""right""""5"":""right""""6"":""right""""7"":""right""}}""right_unswers_values"":{""177"":{""1"":""2""""2"":""2""""3"":""3""""4"":""2""""5"":""1""""6"":""2""""7"":""2""}}""wrong_unswers"":[]""wrong_unswers_values"":[]""count_right_unswers"":{""177"":7}}"2018-12-15 07:56:5514
1774Das Kursprogramm. Lesen Sie es laut.''0000011077"{""all_valid_unswers"":{""177"":true}""right_unswers"":{""177"":{""1"":""right""""2"":""right""""3"":""right""""4"":""right""""5"":""right""""6"":""right""""7"":""right""}}""right_unswers_values"":{""177"":{""1"":""2""""2"":""2""""3"":""3""""4"":""2""""5"":""1""""6"":""2""""7"":""2""}}""wrong_unswers"":[]""wrong_unswers_values"":[]""count_right_unswers"":{""177"":7}}"2018-12-15 08:00:5814
1796Das Elterngespräch. Hören Sie den Dialog und ordnen Sie ihn. Dann ergänzen Sie.'21'000000000814


Вот почему я утверждаю, что в результатах запроса, что привел выше не хватает данных об id_exercises, что принадлежат след списку 176, 178. В след запросе я получаю все данные об упражнениях из `exercises_to_course_lessons` сливая их с информацией об упражнении, что содержится в `exercises`, что принадлежат тому же уроку из того же курса. (course_id = 3, lesson_id = 33)
+
SELECT ecl.exercise_id          as exerciseId,
       ecl.ordering             as exerciseOrderNumber,
       ex.description_1         as description1,
       ex.description_2         as description2,
       ex.audio_files           as audioFiles,
       ex.max_score             as maxScore,
       ex.is_verbal             as isVerbal,
       ex.is_speaking           as isSpeaking,
       ex.cloud_icon            as cloudIcon,
       ex.is_writing            as isWriting,
       ex.add_editor_text_field as editorTextField
FROM `exercises_to_course_lessons` as ecl
         LEFT JOIN `exercises` as ex
                   ON ex.id = ecl.exercise_id
         LEFT JOIN `courses` as c
                   ON c.id = ecl.course_id
         LEFT JOIN `lessons` as l
                   ON l.id = ecl.lesson_id
WHERE ecl.course_id = 3
  AND ecl.lesson_id = 33
  AND l.publish = 1
  AND c.publish = 1
  AND ex.published = 1
ORDER BY ecl.ordering;

Вот результат выполнения данного запроса:
+
"exerciseId""exerciseOrderNumber""description1""description2""audioFiles""maxScore""isVerbal""isSpeaking""cloudIcon""isWriting""editorTextField"
1741Die Schule fängt wieder an.Der erste Elternabend.00000
1752Über Schule reden.Ergänzen Sie die Sätze. Dann kontrollieren Sie mit der CD.'25'00000
1763Das Kursprogramm. Lesen Sie es laut.''11000
1774Das Kursprogramm. Lesen Sie es laut.''00000
1785Die Einladung. Füllen Sie den „unteren Abschnitt“ aus.10000
1796Das Elterngespräch. Hören Sie den Dialog und ordnen Sie ihn. Dann ergänzen Sie.'21'00000


Вот моя структура таблиц БД.
Таблица `exercises_to_course_lessons`. В ней содержатся упражнения(ID упражнений), упорядоченных по ordering , что должны принадлежать конкретному уроку (lesson_id) курса (course_id). Id - это AI.
Получена вот так
select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='exercises_to_course_lessons' 

+

TABLE_CATALOGTABLE_SCHEMATABLE_NAMECOLUMN_NAMEORDINAL_POSITIONCOLUMN_DEFAULTIS_NULLABLEDATA_TYPECHARACTER_MAXIMUM_LENGTHCHARACTER_OCTET_LENGTHNUMERIC_PRECISIONNUMERIC_SCALEDATETIME_PRECISIONCHARACTER_SET_NAMECOLLATION_NAMECOLUMN_TYPECOLUMN_KEYEXTRAPRIVILEGESCOLUMN_COMMENTGENERATION_EXPRESSION
deflangguardexercises_to_course_lessonsid1NOint100int(11)PRIauto_increment"selectinsertupdatereferences"""""
deflangguardexercises_to_course_lessonscourse_id2NOint100int(11)"""""selectinsertupdatereferences"""""
deflangguardexercises_to_course_lessonslesson_id3NOint100int(11)"""""selectinsertupdatereferences"""""
deflangguardexercises_to_course_lessonsexercise_id4NOint100int(11)"""""selectinsertupdatereferences"""""
deflangguardexercises_to_course_lessonsordering50NOint100int(11)"""""selectinsertupdatereferences"""""



Это структура таблицы exercises В ней содержится информация о упражнении (его название, описание, опубликовано ли оно и нек другая мета инфо)
получено вот так
select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='exercises'

+

TABLE_CATALOGTABLE_SCHEMATABLE_NAMECOLUMN_NAMEORDINAL_POSITIONCOLUMN_DEFAULTIS_NULLABLEDATA_TYPECHARACTER_MAXIMUM_LENGTHCHARACTER_OCTET_LENGTHNUMERIC_PRECISIONNUMERIC_SCALEDATETIME_PRECISIONCHARACTER_SET_NAMECOLLATION_NAMECOLUMN_TYPECOLUMN_KEYEXTRAPRIVILEGESCOLUMN_COMMENTGENERATION_EXPRESSION
deflangguardexercisesid1NOint100int(8)PRIauto_increment"selectinsertupdatereferences"""""
deflangguardexercisesdescription_12YESvarchar7002100utf8utf8_general_civarchar(700)"""""selectinsertupdatereferences"""""
deflangguardexercisesdescription_23YEStext6553565535utf8utf8_general_citext"""""selectinsertupdatereferences"""""
deflangguardexercisesimage4YESvarchar255765utf8utf8_general_civarchar(255)"""""selectinsertupdatereferences"""""
deflangguardexercisesaudio_files5YESvarchar4001200utf8utf8_general_civarchar(400)"""""selectinsertupdatereferences"""""
deflangguardexercisespublished61NOint100int(1)"""""selectinsertupdatereferences"""""
deflangguardexercisesmax_score7YESint100int(5)"""""selectinsertupdatereferences"""""
deflangguardexercisesis_verbal80NOint100int(1)"""""selectinsertupdatereferences"""""
deflangguardexercisesis_speaking90NOint100int(1)"""""selectinsertupdatereferences"""""
deflangguardexercisescloud_icon100NOint100int(1)"""""selectinsertupdatereferences"""""
deflangguardexercisesis_writing110NOint100int(1)"""""selectinsertupdatereferences"""""
deflangguardexercisesadd_editor_text_field120NOint100int(1)"""""selectinsertupdatereferences"""""
deflangguardexercisesdate_created13CURRENT_TIMESTAMPNOtimestamp0timestamp"""""selectinsertupdatereferences"""""



Это структура таблицы courses В ней содержится информация о курсе (его название, описание, опубликовано ли он и нек другая мета инфо)
получено вот так
select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='courses'

+

TABLE_CATALOGTABLE_SCHEMATABLE_NAMECOLUMN_NAMEORDINAL_POSITIONCOLUMN_DEFAULTIS_NULLABLEDATA_TYPECHARACTER_MAXIMUM_LENGTHCHARACTER_OCTET_LENGTHNUMERIC_PRECISIONNUMERIC_SCALEDATETIME_PRECISIONCHARACTER_SET_NAMECOLLATION_NAMECOLUMN_TYPECOLUMN_KEYEXTRAPRIVILEGESCOLUMN_COMMENTGENERATION_EXPRESSION
deflangguardcoursesid1NOint100int(6)PRIauto_increment"selectinsertupdatereferences"""""
deflangguardcoursesname_ru_RU2YESvarchar300900utf8utf8_general_civarchar(300)"""""selectinsertupdatereferences"""""
deflangguardcoursesdescr_ru_RU3YEStext6553565535utf8utf8_general_citext"""""selectinsertupdatereferences"""""
deflangguardcoursesshort_descr_ru_RU4YEStext6553565535utf8utf8_general_citext"""""selectinsertupdatereferences"""""
deflangguardcoursesimage5YESchar255765utf8utf8_general_cichar(255)"""""selectinsertupdatereferences"""""
deflangguardcoursesduration6YESint100int(11)"""""selectinsertupdatereferences"""""
deflangguardcoursesskill_level7YESvarchar3090utf8utf8_general_civarchar(30)"""""selectinsertupdatereferences"""""
deflangguardcoursesis_free8NOint100int(1)"""""selectinsertupdatereferences"""""
deflangguardcoursesordering9NOint100int(11)"""""selectinsertupdatereferences"""""
deflangguardcoursespublish10NOint100int(1)"""""selectinsertupdatereferences"""""



Это структура таблицы lesson_exercises_results В ней содержится о результатах выполнения студентом конкретного упражнения урока из конкретного курса. Как вы понимаете, одно и то же упражнение (exercise_id) у меня может содержаться в разных уроках даже в рамках одного и того же курса. Конечно это утверждение справедливо и в случае, если имеются разные курсы (course_id)
получено вот так
select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='lesson_exercises_results'

+
TABLE_CATALOGTABLE_SCHEMATABLE_NAMECOLUMN_NAMEORDINAL_POSITIONCOLUMN_DEFAULTIS_NULLABLEDATA_TYPECHARACTER_MAXIMUM_LENGTHCHARACTER_OCTET_LENGTHNUMERIC_PRECISIONNUMERIC_SCALEDATETIME_PRECISIONCHARACTER_SET_NAMECOLLATION_NAMECOLUMN_TYPECOLUMN_KEYEXTRAPRIVILEGESCOLUMN_COMMENTGENERATION_EXPRESSION
deflangguardlesson_exercises_resultsid1NOint100int(9)PRIauto_increment"selectinsertupdatereferences"""""
deflangguardlesson_exercises_resultsuser_id2NOint100int(11)"""""selectinsertupdatereferences"""""
deflangguardlesson_exercises_resultscourse_id3NOint100int(11)"""""selectinsertupdatereferences"""""
deflangguardlesson_exercises_resultslesson_id4NOint100int(11)"""""selectinsertupdatereferences"""""
deflangguardlesson_exercises_resultsexercise_id5NOint100int(11)"""""selectinsertupdatereferences"""""
deflangguardlesson_exercises_resultsis_finished6NOint100int(1)"""""selectinsertupdatereferences"""""
deflangguardlesson_exercises_resultsno_mistakes7NOint100int(1)"""""selectinsertupdatereferences"""""
deflangguardlesson_exercises_resultscount_mistakes8YESint100int(6)"""""selectinsertupdatereferences"""""
deflangguardlesson_exercises_resultsscore9YESint100int(6)"""""selectinsertupdatereferences"""""
deflangguardlesson_exercises_resultsmax_score10YESint100int(6)"""""selectinsertupdatereferences"""""
deflangguardlesson_exercises_resultsuser_unswers11YEStext6553565535utf8utf8_general_citext"""""selectinsertupdatereferences"""""
deflangguardlesson_exercises_resultseditor_text_field12YEStext6553565535utf8utf8_general_citext"""""selectinsertupdatereferences"""""
deflangguardlesson_exercises_resultsteacher_corrections13YEStext6553565535utf8utf8_general_citext"""""selectinsertupdatereferences"""""
deflangguardlesson_exercises_resultsfinished_date14YEStimestamp0timestamp"""""selectinsertupdatereferences"""""
deflangguardlesson_exercises_resultsremaining_attempts15NOint100int(4)"""""selectinsertupdatereferences"""""
17 дек 19, 15:28    [22042253]     Ответить | Цитировать Сообщить модератору
 Re: Помогите правильно сформировать запрос!  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19981
YuraVakulenko
Это структура таблицы
Это фигня, а не структура таблицы. Выполняете
SHOW CREATE TABLE tablename
вырезаете код создания таблицы и размещаете сюда.

Также крайне желательно сделать скрипты какого-то минимального наполнения таблиц (INSERT INTO с данными), и дать желаемый результат именно для таких данных.

Для упрощения часть полей можно смело удалить, скажем, из 8 полей, попадающих в выходной набор из таблицы exercises, достаточно оставить одно поле (если остальные не используются для связывания, отбора и сортировки, конечно).

Ещё разумнее сразу создать работоспособный fiddle (скажем, на https://dbfiddle.uk/?rdbms=mysql_8.0) и дать на него ссылку.
17 дек 19, 15:54    [22042275]     Ответить | Цитировать Сообщить модератору
Все форумы / MySQL Ответить