Форум MySQL

Здравствуйте! Имеется 2 таблицы
1-таблица лизинговых договоров. price(сумма товаров договора), discount(скидка), intress(интресс в процентах), period(к-во месяцев)

CREATE TABLE IF NOT EXISTS `contracts` (   `id` int(11) NOT NULL default '0',   `price` float NOT NULL default '0',   `discount` float NOT NULL default '0',   `intress` float NOT NULL default '0',   `period` int(11) NOT NULL default '0',   PRIMARY KEY  (`id`) )

2-таблица платежей по этим договорам связь идёт по contracts.id=payments.contract_id month_pay(месячный платёж), month_intress(интресс в месячном платеже не в процентах а в рублях), intress_free_pay(месячный платёж минус интресс в месячном платеже), sum_payed(сумма которую заплатил), pay_date(дата когда надо платить), payment_date(дата когда уже оплатили)

CREATE TABLE IF NOT EXISTS `payments` (   `id` int(11) NOT NULL default '0',   `contract_id` int(11) NOT NULL default '0',   `month_pay` float NOT NULL default '0',   `month_intress` float NOT NULL default '0',   `intress_free_pay` float NOT NULL default '0',   `sum_payed` float NOT NULL default '0',   `pay_date` date NOT NULL default '0000-00-00',  `payment_date` date NOT NULL default '0000-00-00',   PRIMARY KEY  (`id`) )

Необходимо пройтись по таблице договоров и вывести информацию о каждом договоре в строке + через привязку таблицы платежей в этой же строке найти значения - 1) общую сумму которую должны заплатить по договору - sum_to_pay 2) Сумму которую уже заплатили - sum_payed 3) Общую сумму интрессов в рублях - intress_value 4) Общую сумму долга по договору - debt_value 5) К-во просроченных дней у тех платежей которые вообще не оплачивались - failed_pays_count 6) К-во просроченных дней у тех платежей который были заплачены не вовремя - late_pays_count....+ чтобы сохранялась возможность sql сортировки и поиска по всем найденным полям


Мой запрос работает верно, с одним но....failed_p

SELECT c.*, COUNT(DISTINCT p.id) as failed_pays_count, COUNT(DISTINCT p2.id) as late_pays_count, SUM(p3.sum_payed)/count(DISTINCT p.id)/count(DISTINCT p2.id) AS sum_payed, SUM(p3.month_pay-p3.sum_payed)/count(DISTINCT p.id)/count(DISTINCT p2.id) as debt_value, SUM(p3.month_intress)/count(DISTINCT p.id)/count(DISTINCT p2.id) as intress_value, SUM(p3.month_pay)/count(DISTINCT p.id)/count(DISTINCT p2.id) AS sum_to_pay  FROM contracts AS c  LEFT JOIN payments AS p ON (c.id=p.contract_id AND p.month_pay>p.sum_payed AND TO_DAYS(NOW())-TO_DAYS(p.pay_date)>2) LEFT JOIN payments AS p2 ON (c.id=p2.contract_id AND TO_DAYS(p2.payment_date)-TO_DAYS(p2.pay_date)>2 AND p2.month_pay=p2.sum_payed)  LEFT JOIN payments AS p3 ON (c.id=p3.contract_id)  WHERE 1  GROUP BY c.id  HAVING 1 order by c.id DESC limit 0, 5

Но....всё работает верно если в каждом договоре имеются хотя бы один просроченный платёж (условие LEFT JOIN payments AS p2 ON (c.id=p2.contract_id AND TO_DAYS(p2.payment_date)-TO_DAYS(p2.pay_date)>2 AND p2.month_pay=p2.sum_payed)
или хотя бы один вообще не оплаченный (условие LEFT JOIN payments AS p ON (c.id=p.contract_id AND p.month_pay>p.sum_payed AND TO_DAYS(NOW())-TO_DAYS(p.pay_date)>2) )
Если же в договоре нету либо одного просроченного платежа или неоплаченного или обоих, то все вычисления сумм типа SUM(p3.sum_payed)/count(DISTINCT p.id)/count(DISTINCT p2.id) AS sum_payed преобретают значения NULL и все вычисления проходят мимо. Что не верного в данном запросе? И как его сделать правильным, оставив имеющуюся логику и структуру. Спасибо!

если я конечно всё правильно понял :)

получается у вас в формуле SUM(p3.sum_payed)/count(DISTINCT p.id)/count(DISTINCT p2.id) AS sum_payed участвуют значения из таблиц payments AS p (неоплаченные платежи) и payments AS p2 (просроченные платежи)

соответственно если нет просроченных или неоплаченных платежей то возвращается значение NULL (LEFT JOIN срабатывает), соответственно и результатом расчёта является NULL

а как вы хотели?

получается в формуле SUM(p3.sum_payed)/count(DISTINCT p.id)/count(DISTINCT p2.id) либо count(DISTINCT p.id) окажется NULL, либо count(DISTINCT p2.id) , вот и результат NULL

Но ведь как то можно привязать в данном случае что то вроде SUM(p3.sum_payed)/IFNULL(count(DISTINCT p.id), 1)/IFNULL(count(DISTINCT p2.id), 1) AS sum_payed
??? Только куда и как?

ну вот вы сами и ответили :)

Ну только IFNULL в данном случае возвращает тоже результат NULL, я озадачен, возможно что-то не понимаю....

я сейчас тоже с таким случаем столкнулся, при отборе необходимых значений через систему справочников, где в связи участвуют несколько таблиц

так вот, если в связанных таблицах откуда берутся необходимые числовые значения нет данных, то, даже при использовании функции IFNULL() всё равно возвращается NULL (если там NULL)

выходом здесь я думаю является использование вложенных запросов для вытаскивания нужных значений, а не использование связки LEFT JOIN, по крайней мере мне помогает

А сортировать и производить поиск по результатам подзапроса можно? без использования фунций сортировок массивов?

ну да, только не помню можно ли будет использовать для этого псевдонимы, если нет то будете в ORDER BY указывать полное выражение (подзапрос)