Избавьтесь от нулевых значений в MySQL запросе

Спасибо за скрипку БД. Я в значительной степени изменил ваш запрос в соответствии со следующими рекомендациями:

• Подзапрос не кажется необходимым: вычисление может быть выполнено за один запрос GROUP BY
• Вам нужен декартово произведение для генерации списка всех дат для всех зон, отсюда FROM calendar CROSS JOIN zones; общие фильтры, основанные на параметрах, которые применяются к calendar и zones и могут быть помещены в пункт WHERE
• Каждое условие фильтрации в LEFT JOIN таблицах ed должно быть перемещено в [ 117] предложение соответствующего объединения
• более эффективно использовать функцию DATE, чем DATE_FORMAT для усечения даты-времени до даты

Новый запрос (см. the db fiddle ):

SELECT z.zoneID, calendar.dy, z.seasonmax, z.daymax, COUNT(DISTINCT l.ID) Asold
FROM 
    calendar
    CROSS JOIN zones z
    LEFT JOIN licensetypes lt ON z.zoneID IN(lt.ValidForZone)
    LEFT JOIN licenses l 
        ON  lt.ID = l.TypeID 
        AND calendar.dy BETWEEN DATE(l.From) AND DATE(l.To)
WHERE
    FIND_IN_SET( z.zoneID, ( SELECT lt2.ValidForZone FROM licensetypes lt2 WHERE lt2.ID = @licensetype ) )
    AND calendar.dy >= @fromdate
    AND calendar.dy <= @todate
GROUP BY z.zoneID, calendar.dy, z.seasonmax, z.daymax
ORDER BY dy

NB: этот запрос неправильно учитывает случай, когда несколько значений, разделенных запятыми, хранятся в licensetypes.ValidForZone: при совпадении нескольких типов лицензий первый считается (так же, как ваш исходный запрос). Было бы лучше нормализовать вашу базу данных и использовать таблицу мостов для хранения отношения 1-N между типами лицензий и зонами, вместо того, чтобы вставлять значения N в одно поле ...

Я думаю, что этот запрос должен дать вам желаемые результаты (которые, как представляется, 1 для 3/1, 2 для 4/1 и 5/1). Вы должны начать с CROSS JOIN дней и зон, затем LEFT JOIN - licensetypes и licenses для соответствующего типа лицензии и дней:

SELECT z.zoneID, DATE(c.dy) AS dy, z.seasonmax, z.daymax, COUNT(l.ID) AS sold
FROM calendar c
CROSS JOIN zones z
LEFT JOIN licensetypes t ON t.ID = @licensetype AND FIND_IN_SET(z.zoneID, t.ValidForZone)
LEFT JOIN licenses l ON l.TypeID = t.ID AND DATE(c.dy) >= DATE(l.From) AND DATE(c.dy) < DATE(l.To)
WHERE DATE(c.dy) BETWEEN @fromdate AND @todate
GROUP BY dy, z.zoneID

Демонстрация по SQLFiddle [ 115]

Если вам нужна строка для каждой зоны, даже для тех, которые не были проданы в тот день, вы можете взять таблицу zones из подзапроса. Вы хотите начать с CROSS JOIN между таблицами zones и calendar, чтобы получить все комбинации зон и дат. Затем LEFT JOIN, что с информацией о лицензии для получения лицензии считается.

Также, чтобы присоединиться к списку через запятую, вам нужно использовать FIND_IN_SET, а не IN(). См. Поиск с разделенным запятыми значением mysql .

SELECT z.zoneID, c.dy, z.seasonmax, z.daymax, IFNULL(COUNT(l.ID), 0) AS sold
FROM zones AS z
CROSS JOIN calendar AS c
JOIN licensetypes AS lt ON FIND_IN_SET(z.zoneID, lt.ValidForZone)
LEFT JOIN licenses AS l ON lt.ID = l.typeID AND c.dy >= DATE_FORMAT(l.From, '%Y-%m-%d') AND c.dy < DATE_FORMAT(l.To, '%Y-%m-%d')
WHERE c.dy BETWEEN @fromdate AND @todate
    AND lt.ID = @licensetype
GROUP BY z.zoneID, c.dy
ORDER BY dy