Заполнение NULLS из LEFT JOIN значениями

SELECT

    rates_Calendar.date,
    subQuery.name,
    COALESCE(subQuery.amount,0) as amount,
    subQuery.reference,
    subQuery.property


FROM
    rates_Calendar
    LEFT JOIN (
                SELECT
                    rates_Booking.date,
                    unit.unit,
                    unit.abbreviation as name,
                    rates_Booking.amount,
                    rates_Booking.bookingReference AS reference,
                    property.property


                FROM
                    rates_Booking

                    LEFT JOIN booking ON booking.reference = rates_Booking.bookingReference

                    LEFT JOIN unit ON booking.apartment = unit.unit

                    LEFT JOIN property ON property.property = unit.property

                    # unit to apartments
                    LEFT JOIN apartments ON (apartments.unit = unit.unit)
                    LEFT JOIN apartmentTypes ON (apartmentTypes.id = apartments.apartmentTypeId)

                WHERE
                    rates_Booking.date BETWEEN @startDate AND @endDate
                    AND unit.unit = 221



                GROUP BY
                    property.area,
                    property.property,
                    apartmentTypes.id,
                    unit.unit,
                    rates_Booking.date

                ) AS subQuery ON subQuery.date = rates_Calendar.date


 WHERE
     rates_Calendar.date BETWEEN @startDate AND @endDate
 GROUP BY 
subQuery.reference,
subQuery.unit,
subQuery.apartmentTypeId,
subQuery.property,
subQuery.area,
    rates_Calendar.date

Теперь очевидно, что этот запрос приведет к NULLS для дат, которые не совпадают. Есть ли способ обновить все NULLS значениями NON NULL?

2013-01-01  unitA 138 1      property1
2013-01-02  unitA 138 1      property1
2013-01-03  unitA 138 1      property1
2013-01-04  NULL  0   NULL   NULL
2013-01-05  NULL  0   NULL   NULL

Есть ли способ обновить NULL с NON NULLS в соответствующих столбцах?

Я пытаюсь это сделать, потому что скрытие групп строк с NULLS невозможно, как можно понять из ссылки: Скрыть группы строк NULL в JasperReports


mysql sql database group-by left-join
person Ravi    schedule 09.01.2013    source источник

Ответы (4)


arrow_upward
1
arrow_downward

Я думаю, что вы не хотите LEFT JOIN тогда с вашим subQuery. Попробуй это:

SELECT

    rates_Calendar.date,
    subQuery.name,
    COALESCE(subQuery.amount,0) as amount,
    subQuery.reference,
    subQuery.property


FROM
    rates_Calendar, (
                SELECT
                    rates_Booking.date,
                    unit.unit,
                    unit.abbreviation as name,
                    rates_Booking.amount,
                    rates_Booking.bookingReference AS reference,
                    property.property


                FROM
                    rates_Booking

                    LEFT JOIN booking ON booking.reference = rates_Booking.bookingReference

                    LEFT JOIN unit ON booking.apartment = unit.unit

                    LEFT JOIN property ON property.property = unit.property

                    # unit to apartments
                    LEFT JOIN apartments ON (apartments.unit = unit.unit)
                    LEFT JOIN apartmentTypes ON (apartmentTypes.id = apartments.apartmentTypeId)

                WHERE
                    rates_Booking.date BETWEEN @startDate AND @endDate
                    AND unit.unit = 221



                GROUP BY
                    property.area,
                    property.property,
                    apartmentTypes.id,
                    unit.unit,
                    rates_Booking.date

                ) AS subQuery


 WHERE
   rates_Calendar.date BETWEEN @startDate AND @endDate
person Itchy    schedule 09.01.2013
comment
IFNULL, вероятно, можно использовать, но как я могу обновить NULLS значениями NON NULL в приведенных выше строках? - person Ravi; 09.01.2013
comment
Что касается вашего другого комментария, я полностью переработал свой ответ. :-) - person Itchy; 09.01.2013
comment
Во-первых, запрос генерирует повторяющиеся строки. Я могу использовать Group By Rates_Calendar.date, чтобы устранить их. Но я хочу, чтобы сумма осталась такой же, как в моем исходном сообщении. Но ваше предложение обновляет все значения до 138. - person Ravi; 09.01.2013
comment
Верно. Вы можете использовать мой предложенный subQuery и назвать его subQuery2 и добавить его к одному подзапросу, который у вас был. Тогда в начале вы могли бы использовать subQuery2 для всех, кроме одного с 138/0. И не забудьте GROUP BY. - person Itchy; 09.01.2013
comment
Это работает на уровне единиц (я имею в виду unit.unit=221). Но когда вы меняете его на уровень собственности (property.area = 'London'), сумма остается одинаковой для всех них. У меня есть LEFT JOIN с подзапросом, который я использую для суммы, и второй подзапрос для остальных полей. - person Ravi; 09.01.2013
comment
Да, это правильно и имеет смысл с той стороны, которую я понимаю. В конце вы должны получить LEFT JOINS для всех групп результатов. (Извините, что не сказал об этом раньше, но это решило поставленный вами вопрос. ;-)) - person Itchy; 09.01.2013
comment
Что ты имеешь в виду ? Измените второй подзапрос на LEFT JOIN ? - person Ravi; 09.01.2013
comment
Да. Если это не сработает, посмотрите также ответ Гордона Линоффа. К настоящему времени я думаю, что его решение более элегантно. - person Itchy; 10.01.2013

arrow_upward
1
arrow_downward

Это то, что вы хотите?

SELECT rates_Calendar.date, coalesce(subQuery.name, 'unitA'),
        COALESCE(subQuery.amount,0) as amount,
        coalesce(subQuery.reference, 1),
        coalesce(subQuery.property, 'property1')
. . .

Или вы хотите прочитать значения из столбца, например:

select rates_Calendar.date,
       coalesce(subquery.Name, max(SubQuery.name) over ()) as name,
       COALESCE(subQuery.amount,0) as amount,
       coalesce(subQuery.reference, max(subquery.reference) over ()) as reference,
       coalesce(subQuery.property, max(subquery.property) over ()) as property

Это получает максимальное значение из столбца и использует его по умолчанию.

person Gordon Linoff    schedule 09.01.2013
comment
Привет, Гордон, я не это имел в виду. Надеюсь, вы видели приведенный выше разговор. - person Ravi; 10.01.2013
comment
@Рави. . . На самом деле мне кажется, что второй запрос действительно отвечает на ваш вопрос. Чем оно отличается от того, что вы хотите? - person Gordon Linoff; 10.01.2013
comment
Второй запрос ответит на мой вопрос, если он не получит NULL в качестве максимального значения. Это не работает. Он не выбирает максимальное значение столбца. SELECT Rates_Calendar.date, COALESCE (subQuery.id, max (subQuery.id)), COALESCE (subQuery.region, max (subQuery.region)), COALESCE (subQuery.building, max (subQuery.building)), COALESCE (subQuery .title,max(subQuery.title)), COALESCE(subQuery.name,max(subQuery.name)), COALESCE(subQuery.amount,max(subQuery.amount)) - person Ravi; 10.01.2013
comment
@Рави. . . Единственный способ получить NULL в качестве максимального значения - это когда все значения равны NULL. В этом случае вам нечего заполнять. - person Gordon Linoff; 10.01.2013
comment
Я не имею в виду, что все значения равны NULL. Вывод остается таким же, как в моем исходном сообщении, с использованием макс. - person Ravi; 10.01.2013
comment
@Рави. . . Да, это связано с группировкой. В этом случае синтаксис будет немного другим. - person Gordon Linoff; 10.01.2013
comment
Не подскажете, как мне его изменить? - person Ravi; 10.01.2013

arrow_upward
0
arrow_downward

Может быть, вам нужна функция IFNULL?

select ifnull(some_field, 'default_value') from mytable
person heximal    schedule 09.01.2013
comment
В приведенном выше случае нет ничего похожего на значение по умолчанию. Это должно быть unitA во всем втором столбце, 1 во всем 4-м столбце и property1 во всем 5-м столбце. - person Ravi; 09.01.2013

arrow_upward
0
arrow_downward

Мне удалось сделать это, используя таблицы CROSS JOIN ON Rates_Calendar и unit, а также объединив подзапрос к этим двум таблицам.

person Ravi    schedule 21.01.2013