Не могу доработать запрос. , Не могу доработать запрос.

Здравствуйте! Столкнулся с проблемой, не могу доделать запрос.
Запрос:

SELECT peoples.Name,1,2 FROM marks,subjects_classes,peoples WHERE marks.SubjectID=subjects_classes.SubjectID
and peoples.ClassesID=subjects_classes.ClassID and subjects_classes.ClassID='1916c65b-8631-11e6-b7b6-000c29065d4c' and
subjects_classes.SubjectID='c8a296f6-8621-11e6-b7b6-000c29065d41' and marks.AddedDate between STR_TO_DATE('01.09.2016','%d.%m.%Y %H:%i:%s')
and STR_TO_DATE('30.09.2016','%d.%m.%Y %H:%i:%s')

Результат:
Прикреплённый файл____________.png (1,37 Кбайт, скачиваний: 425)
Должно быть:
Прикреплённый файлClassJornal.png (6,49 Кбайт, скачиваний: 415)
Сама база вид:
Прикреплённый файл________.png (161,92 Кбайт, скачиваний: 426)

Напиши человеческим языком - че нужно получить.

Добавлено 03.10.16, 07:50
Все ID и ссылки имеют тип GUID???

Нет все ID имеют тип string.

Добавлено 03.10.16, 08:58
ClassJournal.png тот результат что я должен получить.

Цитата Dmitriy78781 @ 03.10.16, 08:56

ClassJournal.png тот результат что я должен получить.

Ну тогда вопросы:

1) Не совсем понятно, а что есть 25 колонок? Если это дни месяца, вероятно их должно быть 28-31
2) А если это действительно дни месяца, то какой смысл указывать диапазон дат? Нужно указать месяц и год*
3) Что выводить, если ученик по предмету получит в день несколько оценок (например, контрольная работа и устный ответ)?

* или дату, относительно которой считается месяц+год

Сообщение отредактировано: JoeUser - 03.10.16, 10:54

Я когда делал скриншет, то нечайно обрезал часть вывода, 31 колонка. По заданию ученик получит только одну оценку в течении урока. Аналогия со школьным журналом.

Вариант (правда там ID числовые):

SELECT
  Q.Name,
  CASE WHEN Q.F1 = 0 THEN "-" ELSE Q.F1 END AS "1",
  CASE WHEN Q.F2 = 0 THEN "-" ELSE Q.F2 END AS "2",
  CASE WHEN Q.F3 = 0 THEN "-" ELSE Q.F3 END AS "3",
  CASE WHEN Q.F4 = 0 THEN "-" ELSE Q.F4 END AS "4",
  CASE WHEN Q.F5 = 0 THEN "-" ELSE Q.F5 END AS "5",
  CASE WHEN Q.F6 = 0 THEN "-" ELSE Q.F6 END AS "6",
  CASE WHEN Q.F7 = 0 THEN "-" ELSE Q.F7 END AS "7",
  CASE WHEN Q.F8 = 0 THEN "-" ELSE Q.F8 END AS "8",
  CASE WHEN Q.F9 = 0 THEN "-" ELSE Q.F9 END AS "9",
  CASE WHEN Q.F10 = 0 THEN "-" ELSE Q.F10 END AS "10",
  CASE WHEN Q.F11 = 0 THEN "-" ELSE Q.F11 END AS "11",
  CASE WHEN Q.F12 = 0 THEN "-" ELSE Q.F12 END AS "12",
  CASE WHEN Q.F13 = 0 THEN "-" ELSE Q.F13 END AS "13",
  CASE WHEN Q.F14 = 0 THEN "-" ELSE Q.F14 END AS "14",
  CASE WHEN Q.F15 = 0 THEN "-" ELSE Q.F15 END AS "15",
  CASE WHEN Q.F16 = 0 THEN "-" ELSE Q.F16 END AS "16",
  CASE WHEN Q.F17 = 0 THEN "-" ELSE Q.F17 END AS "17",
  CASE WHEN Q.F18 = 0 THEN "-" ELSE Q.F18 END AS "18",
  CASE WHEN Q.F19 = 0 THEN "-" ELSE Q.F19 END AS "19",
  CASE WHEN Q.F20 = 0 THEN "-" ELSE Q.F20 END AS "20",
  CASE WHEN Q.F21 = 0 THEN "-" ELSE Q.F21 END AS "21",
  CASE WHEN Q.F22 = 0 THEN "-" ELSE Q.F22 END AS "22",
  CASE WHEN Q.F23 = 0 THEN "-" ELSE Q.F23 END AS "23",
  CASE WHEN Q.F24 = 0 THEN "-" ELSE Q.F24 END AS "24",
  CASE WHEN Q.F25 = 0 THEN "-" ELSE Q.F25 END AS "25",
  CASE WHEN Q.F26 = 0 THEN "-" ELSE Q.F26 END AS "26",
  CASE WHEN Q.F27 = 0 THEN "-" ELSE Q.F27 END AS "27",
  CASE WHEN Q.F28 = 0 THEN "-" ELSE Q.F28 END AS "28",
  CASE WHEN Q.F29 = 0 THEN "-" ELSE Q.F29 END AS "29",
  CASE WHEN Q.F30 = 0 THEN "-" ELSE Q.F30 END AS "30",
  CASE WHEN Q.F31 = 0 THEN "-" ELSE Q.F31 END AS "31"
FROM (
  SELECT
    Peoples.Name,
    SUM(CASE WHEN DAY(Marks.AddedDate) = 1  THEN Marks.Mark ELSE 0 END) AS "F1",
    SUM(CASE WHEN DAY(Marks.AddedDate) = 2  THEN Marks.Mark ELSE 0 END) AS "F2",
    SUM(CASE WHEN DAY(Marks.AddedDate) = 3  THEN Marks.Mark ELSE 0 END) AS "F3",
    SUM(CASE WHEN DAY(Marks.AddedDate) = 4  THEN Marks.Mark ELSE 0 END) AS "F4",
    SUM(CASE WHEN DAY(Marks.AddedDate) = 5  THEN Marks.Mark ELSE 0 END) AS "F5",
    SUM(CASE WHEN DAY(Marks.AddedDate) = 6  THEN Marks.Mark ELSE 0 END) AS "F6",
    SUM(CASE WHEN DAY(Marks.AddedDate) = 7  THEN Marks.Mark ELSE 0 END) AS "F7",
    SUM(CASE WHEN DAY(Marks.AddedDate) = 8  THEN Marks.Mark ELSE 0 END) AS "F8",
    SUM(CASE WHEN DAY(Marks.AddedDate) = 9  THEN Marks.Mark ELSE 0 END) AS "F9",
    SUM(CASE WHEN DAY(Marks.AddedDate) = 10 THEN Marks.Mark ELSE 0 END) AS "F10",
    SUM(CASE WHEN DAY(Marks.AddedDate) = 11 THEN Marks.Mark ELSE 0 END) AS "F11",
    SUM(CASE WHEN DAY(Marks.AddedDate) = 12 THEN Marks.Mark ELSE 0 END) AS "F12",
    SUM(CASE WHEN DAY(Marks.AddedDate) = 13 THEN Marks.Mark ELSE 0 END) AS "F13",
    SUM(CASE WHEN DAY(Marks.AddedDate) = 14 THEN Marks.Mark ELSE 0 END) AS "F14",
    SUM(CASE WHEN DAY(Marks.AddedDate) = 15 THEN Marks.Mark ELSE 0 END) AS "F15",
    SUM(CASE WHEN DAY(Marks.AddedDate) = 16 THEN Marks.Mark ELSE 0 END) AS "F16",
    SUM(CASE WHEN DAY(Marks.AddedDate) = 17 THEN Marks.Mark ELSE 0 END) AS "F17",
    SUM(CASE WHEN DAY(Marks.AddedDate) = 18 THEN Marks.Mark ELSE 0 END) AS "F18",
    SUM(CASE WHEN DAY(Marks.AddedDate) = 19 THEN Marks.Mark ELSE 0 END) AS "F19",
    SUM(CASE WHEN DAY(Marks.AddedDate) = 20 THEN Marks.Mark ELSE 0 END) AS "F20",
    SUM(CASE WHEN DAY(Marks.AddedDate) = 21 THEN Marks.Mark ELSE 0 END) AS "F21",
    SUM(CASE WHEN DAY(Marks.AddedDate) = 22 THEN Marks.Mark ELSE 0 END) AS "F22",
    SUM(CASE WHEN DAY(Marks.AddedDate) = 23 THEN Marks.Mark ELSE 0 END) AS "F23",
    SUM(CASE WHEN DAY(Marks.AddedDate) = 24 THEN Marks.Mark ELSE 0 END) AS "F24",
    SUM(CASE WHEN DAY(Marks.AddedDate) = 25 THEN Marks.Mark ELSE 0 END) AS "F25",
    SUM(CASE WHEN DAY(Marks.AddedDate) = 26 THEN Marks.Mark ELSE 0 END) AS "F26",
    SUM(CASE WHEN DAY(Marks.AddedDate) = 27 THEN Marks.Mark ELSE 0 END) AS "F27",
    SUM(CASE WHEN DAY(Marks.AddedDate) = 28 THEN Marks.Mark ELSE 0 END) AS "F28",
    SUM(CASE WHEN DAY(Marks.AddedDate) = 29 THEN Marks.Mark ELSE 0 END) AS "F29",
    SUM(CASE WHEN DAY(Marks.AddedDate) = 30 THEN Marks.Mark ELSE 0 END) AS "F30",
    SUM(CASE WHEN DAY(Marks.AddedDate) = 31 THEN Marks.Mark ELSE 0 END) AS "F31"
  FROM
    Peoples
  LEFT JOIN
    Marks ON
      Marks.PeopleID = Peoples.ID AND
      Marks.SubjectID = 1 AND                -- какой предмет (тут подставить строку вместо числа)
      MONTH(Marks.AddedDate) = 10 AND        -- за какой месяц  
      YEAR(Marks.AddedDate) = 2016           -- за какой год
  WHERE
    Peoples.ClassesID = 1                    -- какой класс (тут подставить строку вместо числа)
  GROUP BY
    Peoples.Name
) AS Q
ORDER BY
  Q.Name

Все работает спасибо!

Расти большой

А что если не использовать фун-цию sum

 SUM(CASE WHEN DAY(Marks.AddedDate) = 1  THEN Marks.Mark ELSE 0 END)

я попробовал sum без работает.

Цитата Dmitriy78781 @ 04.10.16, 07:32

я попробовал sum без работает.

Смысл sum в данном запросе - выловить одну оценку за один день по предмету. Однако, если в БД будет несколько оценок за день конкретному ученику по конкретному предмету - запрос отработает не верно (вернет сумму оценок за день).

Хорошо, а если допустим в один день два урока одного предмета, то как быть?

Добавлено 05.10.16, 05:16
Как должен выглядеть запрос?

Цитата Dmitriy78781 @ 05.10.16, 05:16

Как должен выглядеть запрос?

Сперва нужно определить, а как будет выглядеть ответ.

Скажем так же само, но только числа могут повторятся, но идти по порядку. 9 сентября самостоятельная и рядом 9 сентября контрольная.

Цитата Dmitriy78781 @ 05.10.16, 10:34

Скажем так же само, но только числа могут повторятся, но идти по порядку. 9 сентября самостоятельная и рядом 9 сентября контрольная.

В таком случае содержимое ячеек нужно представлять строкой, иначе, если числами - количество столбцов будет плавать. Для "склейки" - агрегатную функцию SUM() заменить на GROUP_CONCAT(). Ну это так, теоретически, попробуй написать код сам.

А можете подсказать какую-нибудь хорошую книгу по таким вот запросам?

Цитата Dmitriy78781 @ 06.10.16, 05:48

хорошую книгу по таким вот запросам

Решаемая задача приводит к формированию нереляционного набора данных. В книгах по SQL такой хренью никто не занимается, обоснованно считая, что этим должна заниматься интерфейсная часть. И введение в некоторые диалекты PIVOT в этом смысле ничего не меняет.

Цитата Akina @ 06.10.16, 06:08

Решаемая задача приводит к формированию нереляционного набора данных.

Кстати о птичках. В MySQL/PostgeSQL есть тип JSON (в постгрессе еще и Array), чем собственно можно воспользоваться по вопросу выше. Будет ли такой набор реляционным?

Цитата JoeUser @ 06.10.16, 14:41

Будет ли такой набор реляционным?

Может будет, может, нет. Смотря что за набор. Но вот нормализованным он не будет стопудово.

Цитата Akina @ 06.10.16, 18:14

Но вот нормализованным он не будет стопудово.

Это с чевойто? Нормализация, сиречь - есть обеспечение неизбыточности данных. В "пожеланиях" ТС избыточности нет ваще напроч - (один день_один ученик_его оценки), единичное упоминание без какого-то рода отношений. Имхо, тут все ровно.

На счет реляционности. По заявлениям из вики - малеха туманно. Но, тем не менее ...

1) Структурная составляющая - действительно получаются обозначимые отношения (в разрезе записей)
2) Составляющая целостности - действительно отношения получаются однозначными (дублирования нет)
3) Составляющая обработки - действительно есть возможность выделения нужного путем запросов (любое поле можно выбрать запросом)

Получается и с реляционностью все не так плохо

Цитата JoeUser @ 07.10.16, 05:29

Нормализация, сиречь - есть обеспечение неизбыточности данных.

Ась? Нормализация - это приведение к нормальной форме. При чём тут избыточность данных - я фиг знает, хотя как правило отсутствие избыточности есть одно из следствий нормализации.

А данный запрос формирует несколько независимых атрибутов записи (оценка за определённый день), содержащих экземпляры одного атрибута (оценка) сущности ученик. Что ни одной из нормальных форм не соответствует. Денормализация налицо.

Цитата JoeUser @ 07.10.16, 05:29

2) Составляющая целостности - действительно отношения получаются однозначными (дублирования нет)

Ошибочное утверждение. В данном случае однозначность отношений обеспечивается внешним механизмом контроля исходных данных. Да и сам ТС соглашается, что в один день может быть несколько оценок. Более того, он начинает соглашаться, что исходные данные могут влиять на структуру результата - какая уж тут на фиг реляционность?

Цитата Akina @ 07.10.16, 09:49

Ась? Нормализация - это приведение к нормальной форме. При чём тут избыточность данных - я фиг знает

Цитата

Нормальная форма — свойство отношения в реляционной модели данных, характеризующее его с точки зрения избыточности, потенциально приводящей к логически ошибочным результатам выборки или изменения данных.

Цитата Akina @ 07.10.16, 09:49

одно из следствий нормализации

Наоборот все Уменьшение избыточности - это цель, это главное. А нормализация - это способ, это второстепенное, читай следствие.

Добавлено 10.10.16, 04:41

Цитата Akina @ 07.10.16, 09:49

В данном случае однозначность отношений обеспечивается внешним механизмом контроля исходных данных.

Тип "массив" или JSON ничем не отличается от той же "строки". Это единый элемент - это главное. Для "строки" есть функция получения подстроки, для "массива" есть функция получения i-го подэлемента. Никаких внешних механизмов не наблюдаю - все реализуется возможностями движка БД.